How to get Multiple Result Set in Entity Framework using Linq with C#?

Question

In my stored procedure, I have two select statement based on two result set i would like to get the details using linq with c# code.

Below is my Stored Procedure :

Create Proc SP_GetResult
As
Begin
    Select Id,Name from EmployeeTable;
    Select ContactNo,DateOfBirth from Customer;
End

I tried below code to call Stored Procedure using Linq :

Public void SelectValues()
{
    using (Entities1 entity = new Entities1())
    {
       var list = entity.SP_GetResult

       foreach (var test in list)
       {
          var test12123 = test;
       }
    }
}

I can get only EmployeeTable details. But, I can't get Customer table details.

How to do it ?

Any idea ?

Servy · Accepted Answer · 2017-04-18 20:14:05Z

2

It's achievable, there is a great explanation in MSDN . Check this article, it shows two ways to achieve it:

https://msdn.microsoft.com/en-us/data/jj691402.aspx

For Your requirements use approach 2 (Multiple Result Sets with Configured in EDMX).

Once You do this You can use those results with Linq without a problem.For example

Public void SelectValues()
{
    using (Entities1 entity = new Entities1())
    {
       var Employees = entity.SP_GetResult;
       var Customers = Employees.GetNextResult<Customer>();
       // do your stuff
    }
}

To 'join' those 2 collection You can use a Tuple Collection

Tuple<EmployeeTable, Customer>

Since the above approach works only for .NET 4.5 and higher, you can use the first approach from the article. It also suits you. You can use linq there. Look at my example:

public List<EmployeeTable> GetEmployees()
{
using(var ctx = new myEntities())
{
    var cmd = ctx.Database.Connection.CreateCommand();
    cmd.CommandText = "[dbo].[SP_GetResult]";

    var reader = cmd.ExecuteReader(); 
    //reader.NextResult(); <- uncomment this to get second result(Customer)
    var employees = ((IObjectContextAdapter)db) 
        .ObjectContext 
        .Translate<EmployeeTable>(reader, "EmployeeTable", MergeOption.AppendOnly); 
    return employees;
}

Now You can use linq like:

var boss = GetEmployees().FirstOrDefault(x => x.Name == "BossName");

Alternative:

Actually to do such simple queries You don't need to have it in one sp. You don't even need store procedures for this. Having only EF You can get it like this:

using (var ctx = new myEntities())
{
    var employyes = from x in ctx.EmployeeTable select new
    {
        id = x.Id,
        name = x.Name
    }
}

edited Apr 18, 2017 at 20:14

Servy

204k27 gold badges347 silver badges470 bronze badges

answered Jul 2, 2015 at 10:09

MajkeloDev

1,66115 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Dhamo Over a year ago

I saw that link, But i am looking for using linq to achieve this task. How can do it. In that link they are using BloggingContext() to achieve the problem. If there is any way then please help me to do it.

MajkeloDev Over a year ago

Ok, but first - I assume You use database first, not model first right ?

Dhamo Over a year ago

Yes exactly. I would like to use database first based on my above example code.

Dhamo Over a year ago

can you please help to solve this issue. I refered some articles they said I need to upgrade my Entity Version. Because I am using Entity version 4.0.0.0.

Dhamo Over a year ago

I tried below your posted code but I am getting null values in Customers. var Customers = Employees.GetNextResult<Customer>();

|

Collectives™ on Stack Overflow

How to get Multiple Result Set in Entity Framework using Linq with C#?

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related