Perform LINQ join with two conditions

Question

Have looked over some other answers, but couldn't apply to this situation. In vb.net, here's what I'm trying to achieve:

Dim query = From o In dbDS.gi_organisation
            Join g In dbDS.gi_game On o.ID Equals g.DeveloperID Or o.ID Equals g.PublisherID

It doesn't compile.

There's lots of solutions along these lines (c#):

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

But I cannot for the life of me apply it to my scenario. Can anyone help?

try using `where`, remove `on-equals` --> `where o.ID Equals == g.DeveloperID || o.ID == g.PublisherID` — T.S., Jun 21 '21 at 19:51
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. Basically, you must replace left join with outer join (`from o`...`from g`...`where`). — NetMage, Jun 21 '21 at 20:42

score 0 · Answer 1 · answered Jun 21 '21 at 20:47

The answer was to drop JOIN entirely and use a mixture of linq and lambda:

Dim query = From o In dbDS.gi_organisation
            From g In dbDS.gi_game.Where(Function(x) x.DeveloperID = o.ID Or x.PublisherID = o.ID)

And to control for any nulls in your foreign keys:

Dim query = From o In dbDS.gi_organisation
            From g In dbDS.gi_game.Where(Function(x) (Not x.IsDeveloperIDNull AndAlso (x.DeveloperID = o.ID)) Or
                                                     (Not x.IsPublisherIDNull AndAlso (x.PublisherID = o.ID)))

Perform LINQ join with two conditions

1 Answers1