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!

Comments
Rick Strahl Tuesday, September 25, 2007
I'm really digging the way you can work with LINQ queries and the above is something I was just doing last night as well.

I think the part that I like more than anything is that I can create somewhat generic business logic for my queries in a business layer, pass out the query and then further manipulate the data and winnie it down to EXACTLY the data that I need for stuff like databinding or assignments. The end result is that I can create precise SQL that returns only what I need without having to have many overloads of business object methods.

Takes some time getting used to the syntax though and maybe more importantly trusting LINQ to SQL to do the right thing.
Noah Coad Thursday, October 18, 2007
Nice! I use plenty of embedded SQL statements, so it's nice to see LINQ supports it so well. The .FirstOrDefault() method looks quite useful. (btw, your images are not coming through in the RSS feed)
Brad Sickles Thursday, January 27, 2011
I was looking at your code and I noticed something that I fell victim to once before. "Score = (int?)..."
FirstOrDefault() will return "int" and not "int?" always. default(int) will return 0. Ignore this comment if you already considered this.
Comments are now closed.
Follow Me On Twitter
RSS Subscribe
Contact
Search Archives
by K. Scott Allen
K.Scott Allen