网络核心:实体框架,然后选择投影选择[英] Net Core: Entity Framework ThenInclude with Projection Select

本文是小编为大家收集整理的关于网络核心:实体框架,然后选择投影选择的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我试图在实体框架中使用某些投影进行操作.

我们有三张桌子,ProductType加入了ProductTypedePartmentBridge,然后加入了部门表.

由于某种原因,在投影选择中,我不能仅从部门中选择列. IntelliSense会出现错误,并且不会出现.但是,我至少可以看到当时的部门表.

        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 Table具有多一关系,因此产品类型可以返回多个部门.

推荐答案

使用include/thenClude不会更改LINQ表达式的范围.因此,如果您要从producttype和Include桥接表,然后是部门表,Select的表达式仍然是producttype,它不会移至部门.

如果您想查询具有特定producttype的部门,并且与桥接表是多一的关系:

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

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+emplection的复合键,则可以稍微简化这一点. >

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

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

// 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
                }));

在第一个示例中,我们迭代了部门,并仅通过利用Any通过桥接表进行检查,从而将其链接到所需的产品类型.返回的唯一部门是包含该产品类型的链接的部门.

在第二个示例中,我们转到产品类型,但然后使用SelectMany说我们想从集合中获取多个结果.这将为我们通过桥接实体为每个相关部门提供一行.从那里我们使用Select获取部门的详细信息.有2种口味,因为我不能100%确定您可以直接通过SelectMany到达部门,因此您可能需要在桥接实体上SelectMany,然后在.部门上Select才能获得部门.我包括两个变体,以防万一.尝试第一个,然后使用第二个如果不起作用.

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

本文地址:https://www.itbaoku.cn/post/1557086.html

问题描述

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

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

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
            });

Error:

                DepartmentId = d.DepartmentId,
                DepartmentName = d.DepartmentName

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

推荐答案

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
    });

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
                }));

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.

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.

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.