Reputation:
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
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
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
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
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