Processing multiple result-sets returned from a stored procedure in Entity Framework

Question

I have a stored procedure like this :

CREATE STORED PROCEDURE Test1
AS
BEGIN
SELECT * FROM Table1
SELECT * FROM Table2
END

Now I want to use this procedure in EF.How?! can I use both two SELECT requests returned from procedure in EF ?!

Note : I know how can I use this stored procedure if it returns just on result

Thanks

Answer 1

1: EF5/EF6 support Stored Procedures with Multiple Result Sets.

2: EF-Core has an open issue to implement this feature.

3: There is a workaround with ADO.NET.

Example for EF5/EF6:

using (var db = new DataContext())
{
    db.Database.Initialize(force: false);

    var cmd = db.Database.Connection.CreateCommand();

    cmd.CommandText = "[dbo].[Test1]";

    try
    {
        db.Database.Connection.Open();
        
        var reader = cmd.ExecuteReader();

        var itemsFromTable1 = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Table1>(reader, "Table1", MergeOption.AppendOnly);   


        foreach (var item in itemsFromTable1)
        {
            Console.WriteLine(item);
        }        

        reader.NextResult();

        var itemsFromTable2 = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Post>(reader, "Table2", MergeOption.AppendOnly);


        foreach (var items in itemsFromTable2)
        {
            Console.WriteLine(items);
        }
    }
    finally
    {
        db.Database.Connection.Close();
    }
}

Solution with ADO.Net:

First code your classes:

public class Table1 
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Table2
{
    public int Id { get; set; }
    public int Age { get; set; }
}

public class StoredProcedureResult
{
    public List<Table1> Table1Items { get; set; }
    public List<Table2> Table2Items { get; set; }
}

Afterwards code the following function into your CustomDbContext class:

public async Task<StoredProcedureResult> GetStoredProcedureResult(int id, string name)
{
    var connection = Database.GetDbConnection();
    await connection.OpenAsync();

    var command = connection.CreateCommand();
    command.CommandText = "Test1";
    command.CommandType = CommandType.StoredProcedure;

    var reader = await command.ExecuteReaderAsync();
    var table1Items = new List<Table1>();
    var table2Items = new List<Table2>();

    while (await reader.ReadAsync())
    {
        table1Items.Add(new Table1
        {
            Id = reader.GetInt32("id"),
            Name = reader.GetString("name"),
        });
    }

    await reader.NextResultAsync();

    while (await reader.ReadAsync())
    {
        table2Items.Add(new Table2
        {
            Id = reader.GetInt32("id"),
            Age = reader.GetInt32("Age"),
        });
    }

    var storedProcedureResult = new StoredProcedureResult();

    storedProcedureResult.Table1Items = table1Items;
    storedProcedureResult.Table2Items = table2Items;

    await reader.CloseAsync();

    return storedProcedureResult;
}

Answer 2

Here is the answer your question

     using (var db = new EF_DEMOEntities())
    {
       var cmd = db.Database.Connection.CreateCommand();
       cmd.CommandText = "[dbo].[proc_getmorethanonetable]";

        try
        {
            db.Database.Connection.Open();
            using (var reader = cmd.ExecuteReader())
          {
                var orders = ((IObjectContextAdapter)db).ObjectContext.Translate<Order>(reader);
               GridView1.DataSource = orders.ToList();
                GridView1.DataBind();
                   reader.NextResult();
                 var items =  
         ((IObjectContextAdapter)db).ObjectContext.Translate<Item>(reader);
                 GridView2.DataSource = items.ToList();
                GridView2.DataBind();
                reader.NextResult();
                 var collect =  ((IObjectContextAdapter)db).ObjectContext.Translate<object>(reader);
         GridView3.DataSource = collect.ToList();
         GridView3.DataBind();

      }
     }
         finally
        {
           db.Database.Connection.Close();
     }
}