NpgSql - How to call a stored procedure (void function) from EF core - asynchronously

c# entity-framework-core npgsql postgresql

Question

I think there are a couple of questions embedded in here.

First, all documentation on calling a function from EF core seems to assume I'm waiting for a result set. E.g.:

_context.Set<MyEntity>().FromSql<MyEntity>("select myDbFunction({0})", myParam);

However, I have a background job I want to run after inserting or updating certain entities (it catalogs some json for faster searching).

This is the approach I was planning to take, or something like it:

    private void AsyncQueryCallback(Task QueryTask)
    {
        if (QueryTask.Exception != null)
        {
            System.Diagnostics.Debug.WriteLine(QueryTask.Exception.ToString());
        }
    }

    public void UpdateAccessionAttributes(int AccessionId)
    {
        var cancelToken = new System.Threading.CancellationToken();
        var idParam = new Npgsql.NpgsqlParameter("accessionId", NpgsqlTypes.NpgsqlDbType.Integer);
        idParam.Value = AccessionId;

        _context.Database.ExecuteSqlCommandAsync("generate_accession_attributes @accessionId", cancelToken, new[] { idParam }).ContinueWith(AsyncQueryCallback);

    }

But, of course, Postgres doesn't support named parameters, etc.

I'm also fumbling a bit on error handling. I don't want/need to await - just need a callback function to log any errors...

Help appreciated, as always!

1
1
5/18/2017 4:28:10 PM

Accepted Answer

The easiest way to accomplish what you're trying to do is probably the following:

_context.Database.ExecuteSqlCommand("SELECT generate_accession_attributes({0})", AccessionId);

If you don't want to use async, then call ExecuteSqlCommand (the sync version) instead of ExecuteSqlCommandAsync. You can place the logging line right after that (instead of a callback), and any issue will generate an exception as usual.

1
5/21/2017 7:59:16 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow