how to read multiple result of procedure in EF 6 as async

Question

Utpal Vishwas · Answer

In Entity Framework 6 (EF6) on .NET Framework, reading
multiple result sets from a stored procedure asynchronously is not directly supported out of the box. EF6 supports
MultipleResults only via synchronous APIs. However, you can do it asynchronously by dropping down to
DbContext.Database.SqlQuery and DbDataReader, using
ExecuteReaderAsync.
Goal
Call a stored procedure that returns multiple result sets, e.g.:
CREATE PROCEDURE GetData
AS
BEGIN
    SELECT * FROM Products;
    SELECT * FROM Categories;
END
Solution: Use DbCommand + ExecuteReaderAsync
Here’s a full example using ADO.NET with EF6 DbContext to read multiple result sets asynchronously.
Step-by-Step Example
public async Taskandlt;(Listandlt;Productandgt; Products, Listandlt;Categoryandgt; Categories)andgt; GetMultipleResultsAsync()
{
    using (var context = new YourDbContext())
    {
        var conn = context.Database.Connection;
        if (conn.State != ConnectionState.Open)
            await ((DbConnection)conn).OpenAsync();
        using (var command = conn.CreateCommand())
        {
            command.CommandText = "GetProductAndCategory";
            command.CommandType = CommandType.StoredProcedure;
            using (var reader = await ((DbCommand)command).ExecuteReaderAsync())
            {
                var products = ((IObjectContextAdapter)context)
                    .ObjectContext
                    .Translateandlt;Productandgt;(reader)
                    .ToList();
                await reader.NextResultAsync();
                var categories = ((IObjectContextAdapter)context)
                    .ObjectContext
                    .Translateandlt;Categoryandgt;(reader)
                    .ToList();
                return (products, categories);
            }
        }
    }
}
Calling the Method
In your controller or service:
public async Taskandlt;ActionResultandgt; Index()
{
    var result = await GetMultipleResultsAsync();
    var products = result.Products;
    var categories = result.Categories;
    return View(new MyViewModel
    {
        Products = products,
        Categories = categories
    });
}
Explanation
Part
Purpose
DbContext.Database.Connection
Gets the underlying ADO.NET connection.
ExecuteReaderAsync()
Executes the command and returns a DbDataReader.
Translateandlt;Tandgt;()
Maps raw data to your EF entity.
NextResultAsync()
Advances to the next result set.
Notes
Translateandlt;Tandgt;() is only available in EF6 through ObjectContext.This pattern requires manual mapping, but it’s the only async-compatible way in EF6 to handle multiple result sets.

forum

how to read multiple result of procedure in EF 6 as async

ICSM Computer

Can you answer this question?

1 Answers

Goal

Solution: Use `DbCommand` + `ExecuteReaderAsync`

Step-by-Step Example

Calling the Method

Explanation

Notes

Liked By

Part	Purpose
`DbContext.Database.Connection`	Gets the underlying ADO.NET connection.
`ExecuteReaderAsync()`	Executes the command and returns a `DbDataReader`.
`Translate<T>()`	Maps raw data to your EF entity.
`NextResultAsync()`	Advances to the next result set.