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

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. When trying to do final select, it shows b lambda as Product. We need the Distinct Customers showing up last.

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

Equivalent SQL is easy:

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

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

Resources:

Filtering on Include in EF Core

Filtering On ThenInclude Three Nested Levels down

Net Core: Entity Framework ThenInclude with Projection Select

Do you only want the Customer(s) objects hydrated? Or do you want the Customer and Transactions and Purchases hydrated? The SQL has only the Customer. — granadaCoder, Jun 20 '20 at 09:02
So you really want this SQL. Which is accomplished by the ".Any" EF syntax. I'm trying to find an EF example. select c.customerId 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 ) PS all/most answers U R getting are over hydrating — granadaCoder, Jun 20 '20 at 09:07

granadaCoder · Accepted Answer · 2020-07-31T15:42:19.010

2

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

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

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

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.

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
 )

This will hydrate the Customer object (all scalars and no navigation properties of the Customer object).

Optionally, you can select (fewer scalar properties of Customer).... 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.

See this answer:

Entity Framework - check whether has grandchild records

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

edited Jul 31 '20 at 15:42

answered Jun 20 '20 at 09:14

granadaCoder

26,328
10
113
146

1

I have personally done .Any (WHERE EXISTS) with 2.1. By the way, an off topic issue is that 2.1 is LTS (long term support) and 3.1 is LTS, but 2.2 is NOT LTS. and 2.2 LTS has ended. You should move to 3.1, but if you cannot, you should move back to 2.1 (as counter intuitive as that sounds) https://dotnet.microsoft.com/platform/support/policy/dotnet-core – granadaCoder Jun 20 '20 at 09:21
But I promise, you should keep at this (figuring out the syntax) ... from a WHERE EXISTS/.Any angle. If you learn how to do this now, it will pay off in the future. "Over hydration" is a common issue with a lot of code I have seen. Doing a "Select customer.*, purchases.*, transactions.*, product.* FROM (JOINS on all 4 tables) is way over-kill for figuring out the customer list. – granadaCoder Jun 20 '20 at 09:25
My own example of 2.1 ".Any" code : https://github.com/granadacoder/oracle-ef-issues-demo/blob/master/src/DataLayer.EntityFramework/MyParentEntityFrameworkDomainDataLayer.cs#L146 – granadaCoder Jun 20 '20 at 09:25
And other off topic, but on topic. You should learn how to attach a Logger to the db context, and this way you can "see" the SQL being generated. See magic line here: https://github.com/granadacoder/oracle-ef-issues-demo/blob/master/src/DataLayer.EntityFramework/Contexts/EfPlaygroundDbContext.cs#L66 – granadaCoder Jun 20 '20 at 09:27
It's a different question. But try to place an .Include after Customer and before . Where – granadaCoder Jun 20 '20 at 10:12
Etiquette then would be to mark this as the answer. And maybe delete unnecessary deprecated comments to keep everything "clean" for future readers. I would also put your final code at the end of your original question. You (we) are trying to help future readers as well. – granadaCoder Jun 20 '20 at 11:14
also left another quick question here thanks, https://stackoverflow.com/questions/62505149/c-sharp-entity-framework-linq-filter-on-grandchildren-and-conduct-a-select-on-t – Jun 21 '20 at 22:31

score 1 · Answer 2 · answered Jun 20 '20 at 08:55

If you go with inner joins, then this should work fine.


    var taxAgencyDistinctList = db.Customer
        .Join(db.Transactions, customer => customer.customerId, transaction => transaction.customerid, (customer, transaction) => new 
        {
        Customer = customer,
        Transaction = transaction
        })
        .Join(db.Purchases, comb => comb.Transaction.PurchaseId, purchase => purchase.PurchaseId, (comb, purchase) => new
        {
        OldCombinedObject = comb,
        Purchase = purchase
        })
        .Join(db.Product, comb => comb.OldCombinedObject.Transaction.ProductId, product => product.ProductId, (comb, product) => new
        {
        LastCombinedObject = comb,
        Product = product
        })
        .Where(comb => comb.LastCombinedObject.OldCombinedObject.Transaction.BKProduct == ProductInput) 
        .Select(comb => comb.LastCombinedObject.OldCombinedObject.Customer).Distinct();

David Browne - Microsoft · Answer 3 · 2020-06-20T18:20:33.023

1

Do you really need to join the Purchases. IE are there some Transactions without Purchases and you want to exclude those with the inner join? If not, then

select distinct c.customerId 
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

is simply

 var cids = db.Transactions
              .Where( t => t.Purchase.BKProduct = productImput )
              .Select(t => new
                     {
                       t.Purchase.CustomerId,
                       t.Purchase.Customer.CustomerName
                     })
              .Distinct();

edited Jun 20 '20 at 18:20

answered Jun 20 '20 at 12:57

David Browne - Microsoft

80,331
6
39
67

hi David, apologize I actually need CustomerName, an attribute of CustomerTable, also Transactions does not contain Product data, it has to go through Purcahses – Jun 20 '20 at 17:37
well transactions joins through purchases in our database still – Jun 20 '20 at 18:19
Not in what you posted, but whatever. See additional update. In any case the pattern is to start a the "bottom" or near it and traverse the Navigation Properties as necessary. – David Browne - Microsoft Jun 20 '20 at 18:20

score 0 · Answer 4 · answered Jun 20 '20 at 08:53

var taxAgencyDistinctList = db.Purchases
        .Include(p => p.Transaction).ThenInclude(t => t.Customer)
        .Where(p => p.ProductId == ProductInput.ProductId)
        .Select(b => b.Transaction.Customer).Distinct();

You can go from other side. When you do a select, linq continues from that selected type. In your case that is product.

A second approach would be start with Customer and go including. Then in where close check customer purcheses.any(m => m.ProductId == input.ProductId) or something like that.

I need the filter though on Product table attribute column, so this will not work — , Jun 20 '20 at 08:58

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

4 Answers4

Linked