网络核心:实体框架然后包含在Projection Select中 [英] Net Core: Entity Framework ThenInclude with Projection Select

问题描述

我正在尝试在实体框架中进行包含选择"中的然后包含".

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.

由于某些原因，在投影选择中，我不能仅从Department中选择列. 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

注意:由于Bridge表具有多对多关系，因此ProductType可以返回多个Department.

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.

如果要查询具有特定产品类型的部门，并且该部门与桥接表之间存在多对多关系，则:

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上使用Select来获取部门.为了防万一，我提供了两种变体.尝试第一个，如果不起作用，请使用第二个.

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.

这篇关于网络核心:实体框架然后包含在Projection Select中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！