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!

All My Pluralsight Courses

OdeToCode by K. Scott Allen