LINQ join on two or more conditions

Question

I'm trying to create a linq join statement which joins an object from a table based on two conditions

MESSAGES
======
ID (int)
UserID (Guid)

MESSAGEPART
======
MessageID (int) 
IsPlaintext (bool)
MessageContent (nvarchar(max))

Here's the query I want to write, essentially:

var messages = from m in db.Message 
               join p in db.MessagePart on m.ID equals p.MessageID 
                                        and p.IsPlaintext equals false

Unfortunately, this doesn't work. This is the best I can do.

var messages = from m in db.Message 
               join p in
               (from x in db.MessagePart where x.IsPlaintext == false select x) 
               on m.ID equals p.MessageID

This seems a bit longwinded. Is there a more elegant way of achieving it?

Can you just make the second `join` condition a `where` instead? — Daniel Kelley, Jun 13 '14 at 12:04
What exactly is long winded about it? Is there a specific part of the code you don't like? — Mike Perrenoud, Jun 13 '14 at 12:06

Ishtiaq · Accepted Answer · 2014-06-13T12:55:17.090

4

An elegant solution.

  var messages = from m in db.Message 
                   join p in db.MessagePart on m.ID equals p.MessageID 
                   where p.IsPlaintext == false

edited Jun 13 '14 at 12:55

answered Jun 13 '14 at 12:08

Ishtiaq

980
2
6
21

+1 this because it is as OP asked "elegant" and will also generate nice SQL – Michael Coxon Jun 13 '14 at 12:21

Christos · Answer 2 · 2014-06-13T12:11:05.007

0

You could try this one:

var messages = from m in db.Message 
               join p in db.MessagePart 
               on new { m.ID, false } equals { p.MessageID, p.IsPlaintext }

or you could try this one:

var messages = db.Message.Join(db.MessagePart.Where(x=>x.IsPlainText==false),
                               x=>x.ID,
                               y=>y.Id,
                               (x,y)=>new {});

Inside the new { } you will declare the properties of the anonymous type you select.

ps. If you update your post and show there which fields you want to select, I could update also mine.

edited Jun 13 '14 at 12:11

answered Jun 13 '14 at 12:05

Christos

53,228
8
76
108

I like like your first answer because it solves a problem I was having the other day, but I think the SQL it would generate would be nasty as.. – Michael Coxon Jun 13 '14 at 12:24

LINQ join on two or more conditions

2 Answers2