1

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

Improve this question
3
  • 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. Commented Jun 20, 2020 at 9:02
  • hi @granadaCoder I only need customer Commented Jun 20, 2020 at 9:03
  • 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 Commented Jun 20, 2020 at 9:07

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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) dotnet.microsoft.com/platform/support/policy/dotnet-core
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.
My own example of 2.1 ".Any" code : github.com/granadacoder/oracle-ef-issues-demo/blob/master/src/…
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: github.com/granadacoder/oracle-ef-issues-demo/blob/master/src/…
It's a different question. But try to place an .Include after Customer and before . Where
|
1

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();
Improve this answer

Comments

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();
Improve this answer

3 Comments

hi David, apologize I actually need CustomerName, an attribute of CustomerTable, also Transactions does not contain Product data, it has to go through Purcahses
well transactions joins through purchases in our database still
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.
0 
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.

Improve this answer

1 Comment

I need the filter though on Product table attribute column, so this will not work