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

Question

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!

Answer 1

From yours description it looks like you actually need:

1. All Products
2. All unused Materials

Do you need it as a single IQueryable (if yes which fields do you need) or as 2 IQueryables?

Answer 2

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.

Answer 3

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