如何处理存储过程返回一对多关系

Question

我有一个存储过程获取表 类别和生产这是我的实体

public class Category
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public List<Product> Products { get; set; }
    }
    public class Product
    {
        public long Id { get; set; }
        public long CategoryId { get; set; }
        public string Name { get; set; }
    }

这是我的商店程序

CREATE PROCEDURE GetCategoryAndProductByCategoryId
    @Id int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * from Category as c
    inner join Product on c.Id = p.CategoryId
    where c.Id = @Id
END
GO

public virtual ObjectResult<Category_Result> GetCategoryAndProductByCategoryId(Nullable<int> Id)
        {
            var IdParameter = Id.HasValue ?
                new ObjectParameter("Id", Id) :
                new ObjectParameter("Id", typeof(System.Int32));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Category_Result>("GetCategoryAndProductByCategoryId", IdParameter);
        }

如何映射到一个类别并列出产品 因为商店产品总是返回列表对象，我的意思是类别是列表对象。 如何将其绑定到类别

Answer 1

首先像这样编写存储过程，

CREATE PROCEDURE [dbo].[GetCategoryAndProductByCategoryId]
    @Id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
                C.[Id],
                C.[Name]
        FROM
                [dbo].[Category] C
        WHERE
                C.[Id] = @Id;
    SELECT
                P.[Id],
                P.[Name],
                P.[CategoryId]
        FROM
                [dbo].[Product] P
        WHERE
                C.[CategoryId] = @Id;
END

然后，在 API/业务逻辑中，可以序列化这两个结果集并组合关系。像这样的东西，代码改编自这里。

using (var db = new BloggingContext())
{
    var cmd = db.Database.Connection.CreateCommand();
    cmd.CommandText = "[dbo].[GetCategoryAndProductByCategoryId]";
    try
    {
        db.Database.Connection.Open();
        // Run the sproc 
        var reader = cmd.ExecuteReader();
        // Read the category from the first result set
        var category = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Category>(
                reader,
                "Category",
                MergeOption.AppendOnly)
            .Single();      
        // Move to second result set and read Products
        reader.NextResult();
        var products = ((IObjectContextAdapter)db)
            .ObjectContext
            .Translate<Product>(
                reader,
                "Product",
                MergeOption.AppendOnly);
        category.Products = products;
        return category;
    }
    finally
    {
        db.Database.Connection.Close();
    }
}

Answer 2

你可以通过这样的XML来获取列表子项

SELECT  c.Id, c.Name, 
      (  SELECT p.Id, p.Name, p.CategoryId FROM Product p 
         WHERE p.CategoryId = c.Id
         FOR XML PATH ('')
      ) AS XmlProducts
from Category as c
where c.Id = @Id

在 C# 中，映射Category数据表时，使用 XML deserialize 按函数获取列表产品对象

public  T DeserialXmlToObject<T>(string xmlString) where T : new()
    {
        T result;
        try
        {
            var serializer = new XmlSerializer(typeof(T));
            var rdr = new StringReader(xmlString);
            result = (T)serializer.Deserialize(rdr);
        }
        catch (Exception ex)
        {                
            return default(T);
        }
        return result;
    }

参考链接：反序列化 xml

对于 XML SQL