C#实体框架:对GrandChildren进行Linq筛选，并对父项进行选择 [英] C# Entity Framework: Linq Filter on GrandChildren and Conduct a Select on the Parent

问题描述

我们公司当前正在将Entity Framework Net Core 2.2与Sql Server一起使用

Our company is currently using Entity Framework Net Core 2.2 with Sql Server

试图找到购买特定产品输入参数的所有不同客户. 尝试进行最终选择时，它将b lambda显示为Product.我们需要不同的客户最后出现.

Trying to find all Distinct customers who purchased a certain Product Input Parameter. When trying to do final select, it shows b lambda as Product. We need the Distinct Customers showing up last.

如何编写EF Linq查询以针对不同的客户获得此查询?

How the EF Linq query be written to get this for distinct Customers?

var taxAgencyDistinctList = db.Customer
        .SelectMany(b => b.Transactions)
        .SelectMany(b => b.Purchases)
        .Select(b => b.Product)
        .Where(b => b.BKProduct == ProductInput) 
        .Select(b => b.).Distinct();

等效的SQL很简单:

select distinct c.customerName
from dbo.customer customer
inner join dbo.Transactions transaction
    on transaction.customerid = customer.customerid
inner join dbo.Purchases purchases
    on purchases.PurchaseId = transaction.PurchaseId
inner join dbo.Product product 
    on transaction.ProductId = product.ProductId
where tra.BKProduct = @ProductInput

公司更喜欢使用方法，如果可能的话，我们不使用Linq代替Sql

Company prefers method, where we Don't use Linq to Sql, if possible

资源:

对EF Core中包含的内容进行过滤

向下过滤，然后向下三个嵌套层

Net Core:实体框架，然后包含Projection Select

推荐答案

虽然您可能会与其他一些人获得所需的数据，但您可能会因为过度消耗(这意味着您过多地访问了数据库)在.

While you may be getting the data you want with some other others, you are probably overhydrating (which means you're hitting the db too much) for what you are after.

".Any"是EF编写"WHERE EXISTS"的方式条款.

".Any" is the EF way of writing "WHERE EXISTS" clauses.

这是尝试进行EF查询的方法:

Here is an attempt at the EF query:

IEnumerable<Customer> justCustomersHydrated = db.Customer
                      .Where(p => p.Transactions.SelectMany(c => c.Purchases).Select(gc => gc.Product.Where(gc => gc.BKProduct == ProductInput).Any());

我正在使用"p"作为父母，"c"代表孩子"，"gc"作为孙子.您当然可以替换它们，但是我试图在代码中显示意图.

I'm using "p" as Parent, "c" as Child, and "gc" as GrandChild. You can replace those of course, but I'm trying to show intention in the code.

您正在尝试使用看起来更像这样的(生成的)SQL.

You're trying to get to (generated) SQL that looks more like this.

select c.customerId /* and c.AllOtherColumns */
from dbo.customer customer
WHERE EXISTS
(
    SELECT 1 FROM dbo.Transactions transaction
inner join dbo.Purchases purchases
    on purchases.PurchaseId = transaction.PurchaseId
inner join dbo.Product product 
    on transaction.ProductId = product.ProductId
where tra.BKProduct = @ProductInput

AND /* relationship to outer query */
 transaction.customerid = customer.customerid
 )

您也可以只选择customerid(尽管通常从父表中选择所有列并不太可怕，除非该表在某处有很多/很多列或一个大数据(image/varbinary(max))列

You can also just select the customerid (although usually selecting all columns from the parent table isn't too horrible, unless that table has many/many columns or a big data (image/varbinary(max)) column in there somewhere.

查看此答案:

实体框架-检查是否有孙记录

该答案具有"new {"的位置进行较不积极的SELECT.

Where that answer has "new {" for a less aggressive SELECT.

这篇关于C#实体框架:对GrandChildren进行Linq筛选，并对父项进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！