How to join with an or clause in Fluent LINQ

Question

I am trying to join two tables.

The TSQL would be:

SELECT * 
FROM User u INNER JOIN Hierarchy h ON u.OrganisationId = h.OrganisationId 
   OR u.OrganisationId = h.OwnerOrganisationId

I have searched and no one has an answer for this with fluent. The closest I can think of is this:

var join1 = context.User.Join(context.Hierarchy, u => u.OrganisationId, h => h.OrganisationId, uh => new {u, h});
var join2 = context.User.Join(context.Hierarchy, u => u.OrganisationId, h => h.OwnerOrganisationId, uh => new {u, h});
var desiredResult = join1.Union(join2);

This seems like it could be highly inefficient though.

I wonder what the execution plan would look like for a cross join and filter in the where clause. — SQLMason, Apr 04 '14 at 17:11

score 2 · Accepted Answer · answered Apr 04 '14 at 17:27

2

Fluent syntax

var orJoin = context.User.SelectMany(
                u => context.Hierarchy.Where(h => u.OrganisationId == h.OrganisationId || u.OrganisationId == h.OwnerOrganisationId),
                (u, h) => new { u, h }
             );

Query syntax

var orJoin = from u in context.User
             from h in context.Hierarchy
             where u.OrganisationId == h.OrganisationId || u.OrganisationId == h.OwnerOrganisationId
             select new { u, h };

answered Apr 04 '14 at 17:27

Aducci

26,101
8
63
67

Thanks, sorry for the late reply. This wasn't a direct solution but helped me work out how to solve the particular problem myself. – Anon343224user Apr 23 '14 at 13:43

How to join with an or clause in Fluent LINQ

1 Answers1

Linked