LINQ - Nested Query
17,182
Solution 1
You can just nest the Linq to Entities query as well:
var results = from x in context.MyEntities
select new Customer()
{
CustomerID = x.CustomerID,
FirstName = x.FirstName,
LastName = x.LastName,
Gender = x.Gender,
BirthMonth = x.BirthMonth,
TotalPurchases = context.PurchaseOrders
.Where(po=>po.CustomerId == x.CustomerId)
.Count()
};
Solution 2
Just do a count off of the navigation property of "PurchaseOrders" that I assume is on the Customer entity.
TotalPurchases = x.PurchaseOrders.Count()
Author by
user208662
Updated on June 25, 2022Comments
-
user208662 almost 2 years
I have a SQL Statement that I am trying to convert to a LINQ query. I need to do this because I can't edit my database :(. Regardless, I have a SQL Statement that looks like the following:
SELECT CustomerID, FirstName, LastName, Gender, BirthMonth, (SELECT COUNT(ID) FROM PurchaseOrder WHERE [CustomerID]=CustomerID) as TotalPurchases FROM MyEntities
I know how to do everything in LINQ excepted for the nested query part. Currently, I have the following:
var results = from x in context.MyEntities select new Customer() { CustomerID = x.CustomerID, FirstName = x.FirstName, LastName = x.LastName, Gender = x.Gender, BirthMonth = x.BirthMonth, TotalPurchases = ? };
How do I execute a nested query in LINQ to get the value for TotalPurchases?
Thank you so much!