OdeToCode IC Logo

Nested Selects in LINQ to SQL

Monday, September 24, 2007

Consider the following diagram to track the votes in a contest. Each voter can register only one vote (a score from 1 to 4) for each contest entry.

For any given voter, you'll want to present a screen showing every entry in the competition, along with the score assigned to the entry by the voter (if a score exists). To fetch the data with SQL, I'd write something like:

SELECT E.EntryID, E.EntryName, E.EntryDescription, @VoterID AS VoterID,
       (SELECT V.Score
        FROM   Votes AS V
        WHERE (V.VoterID = @VoterID) AND (V.EntryID = E.EntryID)) AS Score
FROM Entries AS E

The query should yield a resultset like the following:

EntryID EntryName VoterID Score 13 EntryA 1 4 14 EntryB 1 2 15 EntryC 1 NULL ...

I thought the query would be difficult to express in LINQ, but I stumbled into a solution just by keeping a "SQL" mindset:

var q = from e in Entries
select new
               EntryID = e.EntryID,
               EntryName = e.EntryName,
               EntryDescription = e.EntryDescription,
               VoterID = voterID,
               Score = (
int?)(from v in e.Votes
where v.VoterID == voterID
select v.Score).FirstOrDefault()

The LINQ generated SQL looks amazingly similar to the hand generated SQL, which is a comforting sight!