How can I create an Lambda Expression for Linq in the Where clause for two tables after the Join?

Question

I have this,

 Expression<Func<MyCourse, bool>> filter = mc => mc.Active == 1;
 Func<MyCourse, bool> funcWhere = filter.Compile();

and then this,

var myClasses = db.MyCourse.Join(db.People, mc => mc.PersonId, 
p => p.PersonId, (mc, p) => new { MyCourse= mc, Person = p }).Where(???)

The reason I need to do it this way is because if I put the filter on the MyCourse table first with,

db.MyCourse.Where(funcWhere).Join....

The SQL created brings back all of the People and all of the MyCourse and then uses the filter. If I do the where at the end,

(mc, p) => new { MyCourse= mc, Person = p }).Where(mc=>mc.MyCourse.Active == 1)

I get a good query with the Joins. Else the engine queries all the rows into memory first. Two separate queries with thousands of rows.

I have seen a tremendous amount of questions on SO and elsewhere about this. I cannot find one that tells me how to do the Expression when there is more than one table, from a Join, using a dynamic Where Expression<Func<T,TResult>>.

The goal is to make a dynamic query statement based upon Expressions (not Dynamic Linq, and no third party.) In fact, this question claims the Where at the end is slower, but in my program it does the correct query with Joins.

MyCourse has a PersonId and People has a PersonId. If I wrote this by hand it'd look like,

select mc.CourseName, p.LastName 
from MyCourse mc inner join Person p on mc.PersonId = p.PersonId
where mc.Active = 1;

(Those are just example columns for the question. They're not really what I want from the above query, except Active == 1.)

Where clause with Join in lambda expression

Update: FWIW, I was able to get it working this this,

    var param = Expression.Parameter(typeof(MyClass), "MyClassDebug");
    var exp = Expression.Lambda<Func<MyClass, bool>>(
        Expression.Equal(
            Expression.Property(param, dbParameter),
            Expression.Constant(dbValue)
        ),
        param
    );

I did not do navigation properties or anything else. And I was able to use it like this,

var MyQuery = (from recs in dbcontext.MyClass.Where(exp)
               ...three joins

The produced SQL looked good, and the Explain plan showed minimal row retrieval.

Answer 1

I suspect calling Compile() on your Expression is causing your trouble. Your full query includes the Join, but you've already compiled the Where clause, so it can't compile the entire query including the Join together. That might be why it's grabbing the entire table because it's executing the Where by itself first, then doing the Join later.

But you don't need to call Compile(). Just pass the Expression into Where():

Expression<Func<MyCourse, bool>> filter = mc => mc.Active == 1;
var myClasses = db.MyCourse
    .Where(filter)
    .Join(db.People, mc => mc.PersonId, 
p => p.PersonId, (mc, p) => new { MyCourse= mc, Person = p }).ToList();

Somewhat unrelated to your actual problem, but if you created the foreign keys, you can simplify this a bit. Update your model in your Visual Studio project if you haven't already. Your Person class will change to have a list of MyCourse and your MyCourse class will have a list of Person.

So you can do something like:

Expression<Func<MyCourse, bool>> filter = mc => mc.Active == 1;
var courses = db.MyCourse.Include("Person").Where(filter);
foreach (var course in courses) {
    var person = course.Person; //This is populated with the Person record
}

Linq handles the join, and each MyCourse returned will have a Person property.