如何使用带有C#的Linq在实体框架中获取多个结果集? [英] How to get Multiple Result Set in Entity Framework using Linq with C#?

问题描述

在存储过程中，我有两个基于两个结果集的select语句，我想使用带有C#代码的linq来获取详细信息.

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.

以下是我的存储过程:

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

我尝试使用下面的代码使用Linq调用存储过程:

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;
       }
    }
}

我只能获取EmployeeTable详细信息.但是，我无法获得客户表的详细信息.

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

该怎么做?

有什么主意吗?

推荐答案

这是可以实现的，在MSDN中有一个很好的解释.查看本文，它显示了两种实现方法:

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

根据您的要求，使用方法2(在EDMX中配置了多个结果集).

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

一旦执行此操作，就可以将这些结果与Linq一起使用而没有问题.例如

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>

由于上述方法仅适用于.NET 4.5和更高版本，因此可以使用本文中的第一种方法.它也适合您.您可以在那里使用linq.看我的例子:

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;
}

现在，您可以像这样使用linq:

Now You can use linq like:

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

替代:

实际上，您可以执行这样的简单查询，而无需一次执行.您甚至不需要此存储过程.只有EF，您可以这样获得它:

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
    }
}

这篇关于如何使用带有C#的Linq在实体框架中获取多个结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！