实体框架_返回多个结果集的实体框架和存储过程

实体框架

问题 (The problem)

从第一个版本开始，实体框架（EF）允许我们导入存储过程（SP），但是仍然缺少一些功能。

The specific problem here is when you have a SP that returns more than one resultset; for instance, returning two (or more) SELECT statements.

这里的特定问题是当您有一个返回多个结果集的SP时。 例如，返回两个（或更多）SELECT语句。

Take the following SP as an example:

以以下SP为例：

CREATE PROCEDURE GetAllData
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ID, Name, Age FROM dbo.Clients;
    SELECT ID, Name, Quantity FROM dbo.Products;
END

ID          Name               Age
----------- ------------------ ----------
1           Client 1           20
2           Client 2           22
3           Client 3           34
4           Client 4           12
5           Client 5           55



ID          Name               Quantity
----------- ------------------ -----------
1           Product 1          12.33
2           Product 2          1.00
3           Product 3          45.70
4           Product 4          100.56
5           Product 5          87.00

()

Pre-Conditions

As said above, EF will fail to properly handle your SP so you can leave it out of the Entity Model. Both solutions will rely on the following Plain Old CLR Objects (POCOs):

前提条件

如上所述，EF将无法正确处理您的SP，因此您可以将其排除在实体模型之外。 两种解决方案都将依赖于以下普通旧CLR对象（PO​​CO）：

public class AllData
{
    public List<Client> AllClients { get; set; }
    public List<Product> AllProducts { get; set; }
}

public class Client
{
    public int ID { get; set; }
    public String Name { get; set; }
    public int Age { get; set; }
}

public class Product
{
    public int ID { get; set; }
    public String Name { get; set; }
    public decimal Quantity { get; set; }
}

解决方案1：EF6的“官方”方式 (Solution 1: The EF6 "official" way)

As in this example on MSDN, we can do something like: 就像在MSDN上的此示例中一样 ，我们可以执行以下操作：

public AllData GetAllData()
{
    AllData result = new AllData()
    {
        AllClients = new List<Model.Client>(),
        AllProducts = new List<Model.Product>()
    };

    using (var db = new EFMultipleResultSetEntities())
    {
        // Create a SQL command to execute the sproc 
        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "[dbo].[GetAllData]";

        try
        {
            db.Database.Connection.Open();

            // Run the sproc  
            var reader = cmd.ExecuteReader();
            // Read Blogs from the first result set 
            var customers = ((IObjectContextAdapter)db)
                .ObjectContext
                .Translate<Client>(reader, "Clients", MergeOption.AppendOnly);


            foreach (var item in customers)
            {
                result.AllClients.Add(
                    new Model.Client()
                    {
                        ID = item.ID,
                        Name = item.Name,
                        Age = item.Age
                    });
            }

            // Move to second result set and read Posts 
            reader.NextResult();
            var products = ((IObjectContextAdapter)db)
                .ObjectContext
                .Translate<Product>(reader, "Products", MergeOption.AppendOnly);


            foreach (var item in products)
            {
                result.AllProducts.Add(
                    new Model.Product()
                    {
                        ID = item.ID,
                        Name = item.Name,
                        Quantity = item.Quantity
                    });
            }
        }
        finally
        {
            db.Database.Connection.Close();
        }
    }

    return result;
}

解决方案2：自己动手 (Solution 2: Do it yourself)

就个人而言，我更喜欢回到ADO.net的基础知识，并进行我自己的POCO的水合作用（ the act of filling and object with data) of my own POCO. The main reason is often linked to the fact that, most of the time, these POCOs are meant to be sent by a WCF SOAP service; doing it with the EF entities will give you some serialization problems, to say the least (more about this on a future article).

Other good reasons can be:

其他好的原因可能是：

• Your application model is different from your database model
  您的应用程序模型与数据库模型不同
• The result of the SP is not representable by any EF entity
  SP的结果无法由任何EF实体代表
• You simply want to avoid useless intermediate objects hydration 
  您只想避免无用的中间对象水合作用

public AllData GetAllData()
{
    AllData result = new AllData()
    {
        AllClients = new List<Model.Client>(),
        AllProducts = new List<Model.Product>()
    };

    using (var db = new EFMultipleResultSetEntities())
    {
        // Create a SQL command to execute the sproc 
        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "[dbo].[GetAllData]";

        try
        {
            db.Database.Connection.Open();
                    
            // Run the sproc  
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                result.AllClients.Add(
                    new Model.Client() {
                        ID = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Age = reader.GetInt32(2)
                    });
            }

            reader.NextResult();
            while (reader.Read())
            {
                result.AllProducts.Add(
                    new Model.Product()
                    {
                        ID = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Quantity = reader.GetDecimal(2)
                    });
            }

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

    return result;
}

()

Think as if there was no box

This is one of my rules; don't limit yourself by thinking outside of the box, think as if there was no box at all.

好像没有盒子一样

这是我的规则之一； 不要在框框之外思考自己，好像在没有框框一样。

Although you might be using EF as the interface with your database, that doesn't mean that you must be stuck with what it supports. Most of the time, picking a more "old school" approach will prove to be much more efficient.

尽管您可能将EF用作数据库的接口，但这并不意味着您必须坚持使用它所支持的功能。 在大多数情况下，选择更“古老的”方法将被证明效率更高。

翻译自: https://www.experts-exchange.com/articles/24099/Entity-Framework-and-Store-Procedures-that-return-multiple-resultsets.html

实体框架