选择具有多个和嵌套级别的实体，而无需使用“包含" [英] Select entities with multiple and nested levels without using Include

问题描述

我有以下实体:

public class Item 
{
    public int Id { get; set; }

    public int? ParentId { get; set; }
    public Item Parent { get; set; }
    public List<Item> Children { get; set; }

    public double PropertyA { get; set; }
    public double PropertyB { get; set; }
    ...
}

现在，我想查询数据库并检索所有嵌套子代的数据. 我可以通过在Include()中使用Eager Loading:

Now I want to query the database and retrieve data of all the nested children. I could achieve this by using Eager Loading with Include():

var allItems = dbContext.Items
                    .Include(x => Children)
                    .ToList();

但我不想进行急切加载，而是进行以下投影:

But instead of Eager Loading, I want to do the following projection:

public class Projection 
{
    public int Id { get; set; }
    public List<Projection> Children { get; set; }
    public double PropertyA { get; set; }
}

是否可以通过一次选择仅检索所需的数据? 我们正在使用Entity Framework 6.1.3.

Is it possible to retrieve only the desired data with a single select? We are using Entity Framework 6.1.3.

修改: 到目前为止，这是我尝试过的. 我真的不知道该如何告诉EF与他们的父母以相同的方式来映射所有孩子Projection.

This is what I have tried so far. I really don't know how to tell EF to map all child Projection the same way than their parents.

在EntityFramework.SqlServer.dll中发生了'System.NotSupportedException'类型的未处理异常

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

其他信息:类型投影"出现在单个LINQ to Entities查询中的两个结构不兼容的初始化中.可以在同一查询的两个地方初始化一个类型，但前提是两个地方都设置了相同的属性，并且这些属性以相同的顺序设置.

Additional information: The type 'Projection' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

var allItems = dbContext.Items
    .Select(x => new Projection
    {
        Id = x.Id,
        PropertyA = x.PropertyA,
        Children = x.Children.Select(c => new Projection()
        {
            Id = c.Id,
            PropertyA = c.PropertyA,
            Children = ???
        })
    })
    .ToList();

推荐答案

通常来说，您无法在单个SQL查询中加载未知无限深度的递归结构，除非您批量加载所有可能相关的数据，无论它们是否属于请求的结构.

Generally speaking, you can't load a recursive structure of unknown unlimited depth in a single SQL query, unless you bulk-load all potentially relevant data irregardless whether they belong to the requested structure.

因此，如果您只想限制已加载的列(不包括PropertyB)，但可以加载所有行，则结果可能类似于以下内容:

So if you just want to limit the loaded columns (exclude PropertyB) but its ok to load all rows, the result could look something like the following:

var parentGroups = dbContext.Items.ToLookup(x => x.ParentId, x => new Projection
{
    Id = x.Id,
    PropertyA = x.PropertyA
});

// fix up children
foreach (var item in parentGroups.SelectMany(x => x))
{
    item.Children = parentGroups[item.Id].ToList();
}

如果要限制已加载的行数，则必须接受多个数据库查询才能加载子项.例如，加载单个子集合可能看起来像这样

If you want to limit the number of loaded rows, you have to accept multiple db queries in order to load child entries. Loading a single child collection could look like this for example

entry.Children = dbContext.Items
    .Where(x => x.ParentId == entry.Id)
    .Select(... /* projection*/)
    .ToList()

这篇关于选择具有多个和嵌套级别的实体，而无需使用“包含"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！