Query with a join or use LINQ magic?

Question

Is is better to do a joined query like this:

var employer = (from person in db.People
                  join employer in db.Employers
                  on person.EmployerID equals employer.EmployerID 
                  where person.PersonID == idPerson
                  select employer).FirstOrDefault();

Or is it just as good to do the easy thing and do this (with null checks):

var employer = (from person in db.People
                  where person.PersonID == idPerson
                  select person).FirstOrDefault().Employer;

Obviously, in this one I would actually have to do it in 2 statements to get in the null check.

Is there any sort of best practice here for either readability or performance issues?

Answer 1

The second one could evaluate to null which would result in an error.

I like the first one better because if it is null then you can deal with it without an exception being thrown.

Answer 2

I find the first one better to read, but I am not sure of the differences in implementation.

I strongly recommend using LinqPad to see the Generated SQL that will help you figure out differences.

Answer 3

I'd use this:

var employer = (from person in db.People
                where person.PersonID == idPerson
                select person.Employer).FirstOrDefault();

It's got the simplicity of the first version but still only fetches the data for the employer (rather than the person and the employer).