Net Core:Entity Framework ThenInclude with Projection Select [英] Net Core: Entity Framework ThenInclude with Projection Select

问题描述

我正在尝试在实体框架中使用 Select Projection 执行 ThenInclude.

I am trying to conduct ThenInclude with Select Projection in Entity Framework.

我们有三个表，ProductType 连接到 ProductTypeDepartmentBridge，然后连接到 Departments 表.

We have three tables, ProductType joins to ProductTypeDepartmentBridge, which then joins to Departments table.

出于某种原因，在投影选择中，我不能只选择部门中的列.Intellisense 出现错误并且不会显示.但是，我至少可以看到Department 表.

For some reason, in projection select, I cannot select only the columns from Department. Intellisense is giving an error and it won't show up. However, I can at least see thenincude to Department table.

        var departmentsByProductType = unitOfWork.DBSet<Productype>()
            .Include(d => ProductTypeDepartmentBridge)
            .ThenInclude(d => d.Department)
            .Where(d => d.ProductTypeId == 5)
            .Select(d => new 
            {
                DepartmentId = d.DepartmentId,
                DepartmentName = d.DepartmentName
            });

错误:

                DepartmentId = d.DepartmentId,
                DepartmentName = d.DepartmentName

注意:一个 ProductType 可以返回多个 Departments，因为 Bridge 表具有多对多关系.

Note: A ProductType can return multiple Departments, since Bridge table has many-to-many relationship.

推荐答案

使用 Include/ThenInclude 不会更改 Linq 表达式的范围.因此，如果您从 ProductType 和 Include 桥接表和 Department 表构建查询，Select 的表达式仍然是 ProductType，它不会移动到 Department.

Using Include/ThenInclude does not change the scope of the Linq Expression. So if you are building a query from ProductType and Include the bridging table and then the Department table, the expression for the Select remains ProductType, it doesn't move to Department.

如果要查询具有特定 ProductType 的 Departments 并且它与桥接表是多对多关系，则:

If you want to query Departments that have a particular ProductType and it is a many-to-many relationship with the bridging table then:

如果部门包含桥接实体的集合以获取其产品类型:

If Departments contain a collection of the bridging entities to get to their product type:

var departmentsByProductType = unitOfWork.DbSet<Department>()
    .Where(d => d.ProductTypeDepartmentBridge.Any(b => b.ProductType.ProductTypeId == 5))
    .Select(d => new 
    {
        DepartmentId = d.DepartmentId,
        DepartmentName = d.DepartmentName
    });

如果桥接表将其 PK 映射为 ProductTypeId+DepartmentId 的复合键或以其他方式将 FK 映射到实体中，则可以稍微简化此操作...您可以简化 Where 子句到:

This can be simplified slightly if the bridging table either maps it's PK as a composite key of both ProductTypeId+DepartmentId or otherwise has the FKs mapped in the entity... You can simplify the Where clause to:

.Where(d => d.ProductTypeDepartmentBridge.Any(b => b.ProductTypeId == 5))

如果您没有从部门返回桥的链接:

If you don't have the link from department back to the bridge:

// This should work....
var departmentsByProductType = unitOfWork.DBSet<Productype>()
            .Where(p => p.ProductTypeId == 5)
            .SelectMany(p => p.ProductTypeDepartmentBridge.Department
                .Select( d => new 
                {
                    DepartmentId = d.DepartmentId,
                    DepartmentName = d.DepartmentName
                }));
// but if not, this will work...
var departmentsByProductType = unitOfWork.DBSet<Productype>()
            .Where(p => p.ProductTypeId == 5)
            .SelectMany(p => p.ProductTypeDepartmentBridge
                .Select( b => new 
                {
                    DepartmentId = b.Department.DepartmentId,
                    DepartmentName = b.Department.DepartmentName
                }));

在第一个示例中，我们遍历 Departments，并通过桥接表利用 Any 检查，仅获取与所需产品类型有链接的部门.返回的唯一部门是包含指向该产品类型的链接的部门.

In the first example we iterate over Departments and take only the ones that have a link to the desired product type by leveraging an Any check through the bridging table. The only departments returned are ones that contain a link to that product type.

在第二个示例中，我们转到产品类型，然后使用 SelectMany 表示我们要从集合中获取多个结果.这将通过桥接实体为每个相关部门提供一行.从那里我们使用 Select 来获取部门详细信息.有 2 种风格，因为我不是 100% 确定您可以通过 SelectMany 直接到达部门，您可能需要在桥接实体上SelectMany，然后 选择 .Department 以获取部门.我包括了这两种变体以防万一.试试第一个，如果不行就用第二个.

In the second example we go to the Product Type, but then use SelectMany to say we want to grab multiple results from a collection. This will give us a row for each related Department via the bridging entity. From there we use Selectto get the department details. There are 2 flavours since I'm not 100% sure you can get to the Department via the SelectMany directly, you might need to SelectMany on the bridging entity, then Select on the .Department to get the departments. I included both variants in case. Try the first one, then use the 2nd if it doesn't work.

使用 Select 的优点是您不需要使用 Include 除非您选择整个实体并希望包含相关实体作为返回实体的一部分图形.从实体或什至相关实体中选择字段时，您不需要包含它们.

The advantage of using Select is that you don't need to use Include unless you are selecting whole entities and want to include related entities as part of that returned entity graph. When selecting fields from an entity, or even related entities, you don't need to include them.

这篇关于Net Core:Entity Framework ThenInclude with Projection Select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！