How to do Joins in Linq using lambdas and the expression tree?

Question

I'm trying to do a JOIN in Linq using lambda expressions ... and running into some problems.

I have two entities, Comments and CommentSources. CommentSources are associated to Comments. I have the following code, which does work:

01 IQueryable<Data.Comment> query = ctx.DataContext.Comments;
02
03
04 if (criteria.IsDeleted == DeletedFilter.Deleted)
05    query = query.Where(row => row.DeletedBy != Guid.Empty);
06 else if (criteria.IsDeleted == DeletedFilter.NotDeleted)
07    query = query.Where(row => row.DeletedBy == Guid.Empty);
08
09 var data = query.Select(row => CommentInfo.FetchCommentInfo(row));

I need to join CommentSources on Comments on the field, and I would like to use, if possible, something like:

01 query = query.Join(join code goes here)

How can I do this using lambdas in the expression tree?

One more thing ... how do I add a Where to the Join statement?

Instead of asking another question ... how would I do a Where clause on that Join? For example, I have a field called SourceId on the CommentSource that I would like to filter by.

Answer 1

You need to specify five things (at least):

• The "outer" sequence (Comments) (this is the implicit first parameter)
• The "inner" sequence (CommentSource)
• How to get from a CommentSource to a key
• How to get from a Comment to a key
• What you want the result to be for a CommentSource/Comment pair

For example:

query = query.Join(ctx.DataContext.CommentSource,
                   comment => comment.CommentSourceId,
                   commentSource => commentSource.Id,
                   (comment, commentSource) 
                      => new { Comment=comment, CommentSource=commentSource });