CODEWITHSUNDEEP
c#.netentity-frameworklinq.net-core-2.2
user12425844
user12425844

Reputation:

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

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

Upvotes: 1

Views: 1435

Answers (4)

granadaCoder
granadaCoder

Reputation: 27904

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.

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

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

Upvotes: 1

Giorgi Anakidze
Giorgi Anakidze

Reputation: 216

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

Upvotes: 1

Bahtiyar &#214;zdere
Bahtiyar &#214;zdere

Reputation: 1918

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.

Upvotes: 0

Related Questions