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

async-await c# entity-framework-6 stored-procedures

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

1
2
9/13/2019 6:18:25 PM

Popular Answer

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.

1
9/17/2019 5:43:30 PM


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