使用 LINQ 实体框架进行多对多的完全外部联接

发布于 2024-12-28 11:23:15 字数 368 浏览 4 评论 0原文

我有产品（p）和材料（m）的多对多关系，以及产品2材料表（p2m）作为多对多链接。

我需要

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

基本上获得现有内容的联合。但是，由于这将是一个数据过滤器，因此我需要过滤掉与搜索条件不匹配的产品和/或材料（例如所有以“A”开头的产品等）。

如何在 LINQ-to-EF 4.1 中执行此操作？

非常感谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

栖竹 2025-01-04 11:23:15

以下内容应该可以完成这项工作：

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

对于性能来说，以下内容可能会更好：

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

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

回复收藏 0 原文

习惯那些不曾习惯的习惯 2025-01-04 11:23:15

Linq 不直接提供完整的外连接操作，因此您最好的选择是尝试单独的左连接和右连接 L2E 查询并将它们联合到单个结果集。

我会尝试类似的方法（未测试）：

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 })
            ...

也许您必须使用 DefaultIfEmpty 来强制执行外部联接。

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.

回复收藏 0 原文

~没有更多了~