Stored procedure with multiple recordsets in Entity Framework 6

Question

ASP.NET Core 6 Web API, EF 6, C#, SQL Server 2019. I know how to execute a stored procedure that returns a recordset, take advantage of model mapping, and get a collection of populated, strongly typed objects that correspond to records. Life is good that way.

Is there a way to execute a stored procedure that returns multiple recordsets and receive a collection of strongly typed record object collections?

I know how to do that sans the Entity Framework (with, e. g. the the old school SqlDataAdapter / Dataset, or via a DataReader with hand parsing of records).

Maybe there is a way to somehow decouple the recordset parsing logic from the query execution logic in EF, do the latter by hand, then populate the record collections from a sequence of DataReaders?

Answer 1

If you only need to map query results to objects you don't need a full-featured ORM like EF Core 6. You can use a micro-ORM like Dapper to map query results directly to objects.

In the most common case you can map a query's results to objects and even use parameters with eg:

var sql="select * from Products where CategoryId=@category";
var products=connection.Query<Product>(sql,new {category=123});

You can also map multiple results :

var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
}