How to get multiple result set in Entity Framework using Linq from C #?
In my stored procedure, I have two select statements based on two result sets that I would like to get 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 only get the EmployeeTable data. But I can't get the details of the clients table.
How to do it?
Any idea?
source to share
It's achievable, MSDN has a great explanation. Check out 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 customization in EDMX).
After that, you can use those results with Linq without any 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" this collection 2 you can use the Tuple collection
Tuple<EmployeeTable, Customer>
Since the above approach only works for .NET 4.5 and higher, you can use the first approach from the article. It also suits you. You can use linq there. Take a 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 make such simple queries, you don't need to have it in one sp. You don't even need storage routines for this. With only EF, you can do it like this:
using (var ctx = new myEntities())
{
var employyes = from x in ctx.EmployeeTable select new
{
id = x.Id,
name = x.Name
}
}
source to share