How To: Subqueries In LINQ to SQL

Often we write queries that require the use of subqueries.  And, typically, those subqueries filter on columns from the main query.  If you are wondering how you can translate that to LINQ to SQL, it isn’t all that complicated.

Subqueries are generated in LINQ to SQL when you write another query within the shaped results from your main query.  And, just like in SQL, you can refer back to columns from the main query to perform filtering.

Let’s say you need to show a report of all of the contacts, their credit cards, and the total charged to each of those cards. That can be accomplished as follows (notice that TotalPurchases is selected within the shaped results, and filters on the CreditCards.CreditCardID for that result row):

var cardUsage = (from c in Contacts
		join ccc in ContactCreditCards on c.ContactID equals ccc.ContactID
		join cc in CreditCards on ccc.CreditCardID equals cc.CreditCardID

		select new
			TotalPurchases = (from soh in SalesOrderHeaders
					where soh.CreditCardID == cc.CreditCardID
					select soh.TotalDue).Sum()

It generates this SQL, with subquery as expected:

SELECT [t0].[FirstName], [t0].[LastName], [t0].[EmailAddress], [t2].[CardType], [t2].[CardNumber],
		SELECT SUM([t3].[TotalDue])
		FROM [Sales].[SalesOrderHeader] AS [t3]
		WHERE [t3].[CreditCardID] = ([t2].[CreditCardID])
	AS [TotalPurchases]
FROM [Person].[Contact] AS [t0]
INNER JOIN [Sales].[ContactCreditCard] AS [t1] ON [t0].[ContactID] = [t1].[ContactID]
INNER JOIN [Sales].[CreditCard] AS [t2] ON [t1].[CreditCardID] = [t2].[CreditCardID]

As always, verify in LINQPad.

Page 2 of 512345
Rss Feed