CODEWITHSUNDEEP
.netsqllinq.net-3.5lambda
Blessy Antony
Blessy Antony

Reputation:

linq query to join two tables and get the count from one table values from the other

I have two tables Customers, Orders

Customers CustomerID FName LName

Orders OrderId CustomerID OrderDate

I want to make a linq statement that can join these two tables and get
FName, LName, Count of orders for each customer

Upvotes: 7

Views: 5111

Answers (2)

Amy B
Amy B

Reputation: 110111

from c in db.Customers
let theCount = c.Orders.Count()
select new {c.FName, c.LName, theCount}

http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic11

These access operations translate to more complicated joins or correlated sub-queries in the equivalent SQL, allowing you to walk through your object graph during a query.

Upvotes: 2

Winston Smith
Winston Smith

Reputation: 21882

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID into g
select new { c.FName, c.LName, Count=g.Count() }

Upvotes: 6

Related Questions