C# Entity Framework: Linq Filter on GrandChildren and Conduct a Select on the Parent with Attributes

Question

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

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.

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.

Alternative 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());

Alternative 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)

The correct terminology is "Property". In C#/.NET "Attributes" are something else — pinkfloydx33, Jun 21 '20 at 23:32
I edited the question to use the phrase "(navigation) properties)" instead of attributes, as per this observation. (Leaving this so future readers know you were not making stuff up @pinkfloyd) — granadaCoder, Jun 22 '20 at 21:04

granadaCoder · Accepted Answer · 2020-07-01T14:43:28.897

In this specific case, it probably does not matter.

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).

Does the order of LINQ functions matter?

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

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.

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

hi, can you answer this question? https://stackoverflow.com/questions/64340622/c-sharp-entity-framework-linq-filter-out-certain-grandchild-elements-in-parent thanks — , Oct 14 '20 at 17:51

C# Entity Framework: Linq Filter on GrandChildren and Conduct a Select on the Parent with Attributes

1 Answers1

Linked