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 });

Answer 2

Thanks Jon! Your are right on ... I forgot I had LINQPad, which is a great tool! I was able to enter the query and it showed me the lambda expression which was exactly what you had!

 .Join (
  _CommentSources, 
  c => c.CommentId, 
  s => s.CommentId, 
  (c, s) => 
     new  
     {
        c = c, 
        s = s
     }
)

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 3

Figured out my additional where clause ... it would look something like:

.Where (temp0 => (temp0.s.SourceId == new Guid ("7c49c349-f902-4049-bf87-41a77b709677")))

Answer 4

Here is my final code:

            var query = ctx.DataContext.Comments.Join(ctx.DataContext.CommentSources,
                  c => c.CommentId, 
                  s => s.CommentId,
                  (c, s) => new {Comment = c, CommentSource = s});

            if (criteria.SourceId != null && criteria.SourceId != Guid.Empty)
                query = query.Where(row => row.CommentSource.SourceId == criteria.SourceId);

            if (criteria.IsDeleted == DeletedFilter.Deleted)
                query = query.Where(row => row.Comment.DeletedBy != Guid.Empty);
            else if (criteria.IsDeleted == DeletedFilter.NotDeleted)
                query = query.Where(row => row.Comment.DeletedBy == Guid.Empty);

            var data = query.Select(row => CommentInfo.FetchCommentInfo(row.Comment));