CODEWITHSUNDEEP
entity-frameworkstored-procedures
user9969
user9969

Reputation: 16040

Entity Framework stored procedure with multiple resultsets?

I am new to EF4 .

I am using a stored procedure that returns 2 resultsets? I understand that this is not possible and not supported.Pity!

What is the workaround? any code examples?

Thanks a lot

Upvotes: 21

Views: 18374

Answers (3)

Matthias Müller
Matthias Müller

Reputation: 572

In EF5/EF6 there is a feature to receive multiple result sets. Earlier EF-Versions (e.g. EF4) do not have this feature. For the current EF-Core Version there is still an open issue to implement this feature.

As a workaround, according to this blog post there is a solution with ADO.Net:

First code your classes:

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

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

public class TableC
{
    public string Test { get; set; }
    public string Param { get; set; }
}

public class StoredProcedureResult
{
    public List<TableA> TableAEntries { get; set; }
    public List<TableB> TableBEntries { get; set; }
    public List<TableC> TableCEntries { 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 = "GET_ID_NAME @ID, @NAME";
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter("@ID", id));
    command.Parameters.Add(new Microsoft.Data.SqlClient.SqlParameter("@NAME", name));

    var reader = await command.ExecuteReaderAsync();
    var tableAEntries = new List<TableA>();
    var tableBEntries = new List<TableB>();
    var tableCEntries = new List<TableC>();

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

    await reader.NextResultAsync();

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

    await reader.NextResultAsync();

    while (await reader.ReadAsync())
    {
        tableCEntries.Add(new TableC
        {
            Test = reader.GetString("Test"),
            Param = reader.GetString("Param"),
        });
    }

    var storedProcedureResult = new StoredProcedureResult();

    storedProcedureResult.TableAEntries = tableAEntries;
    storedProcedureResult.TableBEntries = tableBEntries;
    storedProcedureResult.TableCEntries = tableCEntries;

    await reader.CloseAsync();

    return storedProcedureResult;
}

Upvotes: 0

Christo
Christo

Reputation: 2370

EF5 now supports this:

Upvotes: 16

Kalle
Kalle

Reputation: 2293

A blog post on the subject: http://blogs.infosupport.com/blogs/willemm/archive/2010/07/16/ado-net-entity-framework-advanced-scenarios-working-with-stored-procedures-that-return-multiple-resultsets.aspx

Some possibly useful extensions: http://code.msdn.microsoft.com/EFExtensions

Upvotes: 9

Related Questions