CODEWITHSUNDEEP
c#stored-proceduresasync-awaitentity-framework-6
Robin
Robin

Reputation: 33

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

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

Upvotes: 2

Views: 3226

Answers (1)

Jasen
Jasen

Reputation: 14250

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.

Upvotes: 4

Related Questions