CODEWITHSUNDEEP
sqlentity-frameworkmany-to-manyouter-joinfull-outer-join
John
John

Reputation: 3677

FULL OUTER JOIN on a Many-to-Many with LINQ Entity Framework

I have a many-to-many relationship of products (p) and materials (m) and the products2materials table (p2m) as the many-to-many link.

I need to get 

- all products that have materials assigned,
- all products with no materials assigned,
- and all materials with no products assigned.

Basically a union of what is there. However, since this will be a data filter, I need to filter out products and/or materials that do not match the search criteria (e.g. all products that start with "A", etc.).

How do I do this in LINQ-to-EF 4.1?

Many thanks!

Upvotes: 0

Views: 2792

Answers (2)

yoel halb
yoel halb

Reputation: 12711

The following should do the job:

from m in context.Materials //m has to be the first
from p in context.Products    
where !p.Select(p1 => p1.Material).Contains(m) || p.Material == null || p.Material == m

For performance it would probably be better the following:

var a = from p in context.Products select p.Material;

var b = from m in context.Materials //m has to be the first
        from p in context.Products    
        where a.Contains(m) || p.Material == null || p.Material == m

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

Linq doesn't offer full outer join operation directly so your best choice is to try separate left and right join L2E queries and union them to single result set.

I would try something like (not tested):

var query = (from p in context.Products
             from m in p.Materials
             select new { p, m })
            .Union(
             from m in context.Materials
             from p in m.Products
             select new { p, m })
            ...

Perhaps you will have to use DefaultIfEmpty to enforce outer joins.

Upvotes: 0

Related Questions