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
		{
		     	c.FirstName,
			c.LastName,
			c.EmailAddress,
			cc.CardType,
			cc.CardNumber,
			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.



2 Responses to “How To: Subqueries In LINQ to SQL”

  1. I try to extract the sub query to its own method to make it easy to read. Do you see any issues with that?
    As always I find this blog very informative.

  2. Run it through LINQPad…

    This won’t work as you’d hope.

    First, it’ll select all of the columns from the tables in your primary query (rather than just the columns you need to shape your result object). Then, it’ll fire the subquery (which now resides inside its own function) once for each result from the primary query. Decidely not the desirable result.

Rss Feed