LINQ query to select subjects of interest

Here's the query:

using (var db = CreateContext())
        {
            // performing the check for HasBeenAdded inline here, is this only one db call?
            return db.Subjects.Select(s => new SubjectDto(s){HasBeenAdded = db.Interests.Any(x => x.SubjectId == s.SubjectId)}).ToList();
        }

Basically I create a DTO from a subject and then populate a property of that DTO (HasBeenAdded) based on whether or not that entry's foreign key exists in another table. Is this the right way to go?

In terms of "SQL" what you need is a "Left Outer Join". So I would suggest this:

using (var db = CreateContext())
{
    var subjectDtos = from subject in Subjects
                      join interest in Interests on subject.SubjectId equals interest.SubjectId into si
                      from interest in si.DefaultIfEmpty()
                      select new SubjectDto { Subject = subject, HasBeenAdded = interest != null };
    return subjectDtos.Distinct().ToList();
}

And here is why:

• I think that LINQ query is more readable in 'SQL like' format.

• With the join clause you retrieve only the "subjects" that have an "interest". But with potential duplications if more than one "interest" has the ID of the same "subject" (For this reason I introduced the "Distinct" in the query, you can remove if the duplicate results interest you).

• As a matter of consistency I suggest you rather than use the constructor, initialize the property directly.

• For "DefaultIfEmpty" steatment docs go here. The Left Outer Join is there.

UPDATE My first code does not work. Fixed and tested now :)

StackExchange Code Review Q#31710, answer score: 7