Call stored procedure from EF asynchronously using C# inside async method

Question

We are moving to Entity Framework, however we have a large number of stored procedures. If our Web API uses an async/await pattern all the way to the stored procedure call, how do I handle the call? Can I/should I make it async as well?

I have a db context and I can call my stored procedure Foo synchronously. This works however the calling code is all wrapped with async/await keywords. The stored procedure in question is also a Select and not an Insert/Update/Delete. I cannot use ToListAsync() on the context.Foo call, if I try

context.Database.ExecutSQLCommandAsync()

I only get a Task<int> back and not my object. Is there a best practice here?

public async Task<List<FooData>> GetFoo(string param)
{
    List<FooData> dataList = new List<FooData>();

    using(dbEntities context = new dbEntities(connectionstring))
    {
        //  this runs as expected
        var result =  context.Foo(param).ToList();

        if(result != null)
        {
            result.ForEach(delegate(Foo_Result item)
                               {
                                   //  load object
                               });
        }
    }

    return dataList;
}

I suppose I could do this

var result = await Task.Run(()=> context.Foo(param).ToList());

but this is an Entity Framework db call and not an CPU intensive process. Is there a best practice to follow here? Thanks

It doesn't seem to work with a directly invoked stored proc. — Robin
– Robin, Commented Sep 16, 2019 at 18:38

Jasen · Accepted Answer · 2019-09-17 17:43:30Z

4

If you want a result set, then you'll need Database.SqlQuery.

public class MyDbContext : DbContext
{
    public async Task<IList<Foo>> GetFooAsync(int n)
    {
       var query = base.Database.SqlQuery<Foo>("exec fooStoredProc @n",
           new SqlParameter("@n", n));
       return await query.ToListAsync();
    }
}

With usage

public async Task Exec()
{
    MyDbContext db = new MyDbContext();
    var foos = await db.GetFooAsync(1);
}

This assumes your stored proc returns fields that match up to your Foo class.

edited Sep 17, 2019 at 17:43

answered Sep 17, 2019 at 4:28

Jasen

14.3k3 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call stored procedure from EF asynchronously using C# inside async method

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related