.NET Core Entity Framework stored procedures

Question

I'm trying to port a ASP.NET 4.5 application to .NET Core and I have one real issue I can't seem to figure out.

My existing application executes stored procs which return a dataset with multiple datatables. The Entity Framework can automatically map the returned fields to my entity properties but only works with the first datatable in the dataset (naturally).

So I'm just trying to figure out if its at all possible to somehow intercept the model building process and use custom code to process the dataset and look into the other datatables to set entity fields.

I know I can use the normal way of executing the stored procedure directly using the SqlConnection but I'm wondering if the Entity Framework has some way to do this already.

Answer 1

At the moment, the way to execute stored procedures that return data is to use the DbSet.FromSql method.

using (var context = new SampleContext())
{
    var data= context.MyEntity
        .FromSql("EXEC GetData")
        .ToList();
}

This has certain limitations:

• It must be called on a DbSet
• The returned data must map to all properties on the DbSet type
• It does not support ad hoc objects.

Or you can fall back to plain ADO.NET:

using (var context = new SampleContext())
using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "GetData";
    command.CommandType = CommandType.StoredProcedure;
    context.Database.OpenConnection();
    using (var result = command.ExecuteReader())
    {
        // do something with result
    }
}

There are plans to introduce support for returning ad hoc types from SQL queries at some stage.