Chain SelectMany instead of using a JOIN statement

Question

Let say I have:

I have to find all the classes that belong to a specific school (IdSchool=2) and that are from science for example.

Anyways because I find LINQ very eassy to use I do:

using(var context = new MyEntities())
{
    var classesOfInterest = context.Schools
                        .SelectMany(x => x.Teachers)
                        .SelectMany(x => x.Classes)
                        .Where(x => /* custom criteria */ )
                        .ToList();
}

So my question is: Is using this approach instad of using a join statement bad practice? Should I use a JOIN statement instead? There are cases where I chain 5 "SelectMany" statements. Right now it works great because database is small. If I where to be working with a big database is this something I should try to avoid?

Answer 1

Is using this approach instad of using a join statement bad practice?

Not at all. In fact this is the recommended (or preferred) practice when working with EF. The so called navigation properties are one of the beauty of the EF. When you have them (and you do), you'd never need to use "manual" joins. You just "navigate" if they are objects/collections and EF generates the joins for you if like you wrote them, but without the need to remember which field from the one table joins to which field of the other.

For instance, let take this simplified version of your query:

var queryA = context.Schools.SelectMany(x => x.Teachers);
var sqlA = queryA.ToString();

and the equivalent using joins:

var queryB = from s in context.Schools
             join t in context.Teachers on s.Id equals t.IdSchoool
             select t;
var sqlB = queryB.ToString(); 

you will see that sqlA and sqlB are one and the same.