How to create a join in an expression tree for LINQ?

Question

I have a Task object that has a collection of Label objects ... in the database the tables are called Task and Label.

There are a variety of ways to search for a Task, so using LINQ, I construct my LINQ query in an expression tree ... similar to the below code sample:

IQueryable<Data.Task> query = ctx.DataContext.Tasks;    

if (criteria.Number > 0)    
    query = query.Where(row => row.Number == criteria.Number);  

if (criteria.CustomerId != Guid.Empty)
    query = query.Where(row => row.Project.CustomerId == criteria.CustomerId);

if (criteria.ProjectId != Guid.Empty)
    query = query.Where(row => row.ProjectId == criteria.ProjectId);

var data = query.Select(row => TaskInfo.FetchTaskInfo(row));

this.AddRange(data);

This works great ... but now I would like to search for Tasks with a specific Label, for example, accounting or feature-request.

I am able to do this in LINQPad from a complete query:

from t in Tasks
join l in Labels on t.TaskId equals l.SourceId 
where l.Name == "accounting"
select t

Is there anyway to do this using an expression tree? I'm stuck! Any help would be greatly appreciated!

Answer 1

I believe this should do it:

Tasks.Join(Labels.Where(l => l.Name == "accounting"), t => t.TaskId, l => l.SourceId, (t, l) => t)

Answer 2

A "join" (not a "join ... into") clause in a query expression translates into a Join call. The tricky bit is transparent identifiers - only one sequence comes out of the join, and it's got to have both t and l in it (in your example) so the compiler does some magic.

I don't have much time to go into the details here, but it's probably best to just show you how your query expression is translated.

This:

from t in Tasks
join l in Labels on t.TaskId equals l.SourceId 
where l.Name == "accounting"
select t

Is translated into:

Tasks.Join(Labels, t => t.TaskId, l => l.SourceId, (t, l) => new { t, l })
     .Where(z => z.l.Name == "accounting")
     .Select(z => z.t)

Note the introduction of "z" here - basically that contains both t and l from the original expression.

EDIT: David Morton's answer gives a more efficient way of doing it. His line is equivalent to:

from t in Tasks
join l in (from x in Labels where x.Name == "accounting") 
       on t.TaskId equals l.SourceId 
select t

If you only have a select clause after the join, the compiler is able to skip the transparent identifier and put the projection directly as the last parameter to the Join call.

Answer 3

If you click the little lambda button in LINQPad it will show you the expression tree that you're trying to create, it's in the list of three buttons above the results box and below the expression box.