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

问题描述

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

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

尝试查找购买了特定产品输入参数的所有不同客户. 编写以下EF Linq查询以获取不同的客户.

Trying to find all Distinct customers who purchased a certain Product Input Parameter . The following EF Linq query was written to get the distinct Customers.

又出现了另一个问题，我们如何获得更多(导航)客户属性?应包括在何处"之前还是何处"之后?有关系吗?运行SQL事件探查器时，它注意到查询没有区别.我只是想确定在某些情况下包括此处"的位置是否重要?

Later another question came up, how do we get more (navigation) properties of customer? Should Include be placed Before the Where or After the Where? Does it matter? When running the SQL Profiler, it noted no difference in the queries. I just wanted to be sure in some cases does the location of Include here matter?

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

原始解决方案:

Original Solution: C# Entity Framework: Linq Filter on GrandChildren and Conduct a Select on the Parent

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

另一个问题出现了，我们需要获取更多的(导航)客户属性.

Another question came up, we need to get more (navigation) properties of Customer.

替代1:

var customerData = db.Customer
                      .Include(c=>c.CustomerType)
                      .Include(c=>c.CustomerAddress)
                      .Where(p => p.Transactions.SelectMany(c => c.Purchases).Select(gc => gc.Product.Where(gc => gc.BKProduct == ProductInput).Any());

替代2:

var customerData = db.Customer
                      .Where(p => p.Transactions.SelectMany(c => c.Purchases).Select(gc => gc.Product.Where(gc => gc.BKProduct == ProductInput).Any())
                      .Include(c=>c.CustomerType)
                      .Include(c=>c.CustomerAddress)

推荐答案

在这种特定情况下，可能没有关系.

In this specific case, it probably does not matter.

由于c#包含"；关于生成的sql的SELECT和JOIN.

Since c# "Include" is about the SELECT and the JOIN of the generated sql.

但是，您不想使用无所谓"作为总括声明.

However, you do not want to use the "it does not matter" as a blanket statement.

请参阅以下答案(以及总体问题和其他答案).

See here answer below (and overall question and other answers).

LINQ函数的顺序重要吗?

当您开始放置Where和OrderBy之类的内容时，操作顺序可能很重要.

When you start putting in things like Where and OrderBy, the order-of-the-operations can matter.

始终查看生成的sql并问自己它看起来合理吗?"? (您已经从您的问题中做到了这一点:) ..我主要是为以后的读者提到的)

Always look at the generated sql and ask yourself "does it look reasonable"? (Which you already did from your question :) ..I mention primarily this for future readers)

因此，在这种特定情况下，这是首选.我通常将. 最后的.因此，您的第一个示例将符合我的个人喜好.

So in this specific case, it is a preference. I typically put .Where last. So your first example would match my personal preference.

对于某些进一步调查"，请检查以下内容: https://weblogs.asp.net/dixin/introducing-linq-3-waht-is-functional-programming

And for some "further investigation", check out something like : https://weblogs.asp.net/dixin/introducing-linq-3-waht-is-functional-programming

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