CODEWITHSUNDEEP
.netlinq-to-sql
mattruma
mattruma

Reputation: 16677

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

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.

Upvotes: 9

Views: 17208

Answers (2)

mattruma
mattruma

Reputation: 16677

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

Upvotes: 5

Jon Skeet
Jon Skeet

Reputation: 1499770

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

Upvotes: 15

Related Questions