.NET Core Entity Framework Stored Procedures

Question

I am trying to port an ASP.NET 4.5 application to .NET Core, and I have one real problem that I cannot understand about.

My existing application runs stored procs that return a dataset with multiple datatables. Entity Framework can automatically match returned fields with my objects, but it only works with the first datatable in a dataset (of course).

So, I'm just trying to figure out whether it is possible to somehow somehow intercept the process of building the model and use my own code to process the data set and look at other data to set entity fields.

I know that I can use the usual way to execute a stored procedure directly using SqlConnection , but I am wondering if the Entity Framework has any way to do this already.

Answer 1

Currently, the way to execute stored procedures that returns 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 DbSet
• The returned data should display all properties of type DbSet
• It does not support special objects.

Or you can return to simple 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 special types from SQL queries at a certain stage.

Answer 2

To answer the @DKhanaf problem with multiple datasets, you can use the SqlDataAdapter to populate the DataSet with all of your result sets. However, the SqlDataAdapter requires the full .NET Framework, so you have to run the .NETCore project when you configure .NET 462 or something similar.

  using (var context = new SampleContext()) using (var command = context.Database.GetDbConnection().CreateCommand()) { command.CommandText = "GetData"; command.CommandType = CommandType.StoredProcedure; context.Database.OpenConnection(); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { var ds = new DataSet(); adapter.Fill(ds); return ds; } } }