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!