Intersect 需要 LINQ 查询帮助

发布于 2024-08-25 05:28:36 字数 896 浏览 7 评论 0原文

LINQ 专家，我正在寻求编写查询的帮助...

我有一个包含 Person 记录的表，它有一个可为空的 ParentID 列，因此它是一种自引用，其中每个记录可能有一个 Parent。

我正在寻找其父行已处理的未处理行。这个 SQL 工作正常：

SELECT *
  FROM Person
  where IsProcessed = 0 and
  ParentId in
  (
  select Id from Person 
  where IsProcessed = 1
  )

我尝试了许多 LINQ 查询，但都失败了。现在，我正在尝试：

    var qParent = 
                from parent in db.Person
                where 
                parent.IsProcessed == true
                select parent.ID;

    var qChildren = from child in db.Person
                    where
                    child.IsProcessed == false
                    && child.ParentId.HasValue
                    select child.ParentId.Value;

    var q2 = qChildren.Intersect(qParent);

出于某种原因，这会产生带有 DISTINCT 子句的 SQL，并且我很困惑为什么会生成 DISTINCT。

我的主要问题是如何为上面的SQL语句编写LINQ？

提前致谢。

原文

LINQ gurus, I am looking for help to write a query...

I have a table with Person records, and it has a nullable ParentID column, so it is kind of self-referencing, where each record might have a Parent.

I am looking for unprocessed rows whose parent rows were processed.
This SQL works fine:

SELECT *
  FROM Person
  where IsProcessed = 0 and
  ParentId in
  (
  select Id from Person 
  where IsProcessed = 1
  )

I tried a number of LINQ queries, but they failed. Now, I'm trying:

    var qParent = 
                from parent in db.Person
                where 
                parent.IsProcessed == true
                select parent.ID;

    var qChildren = from child in db.Person
                    where
                    child.IsProcessed == false
                    && child.ParentId.HasValue
                    select child.ParentId.Value;

    var q2 = qChildren.Intersect(qParent);

This yields SQL with a DISTINCT clause, for some reason, and I am baffled why DISTINCT is generated.

My main question is how to write LINQ for the SQL statement above?

Thanks in advance.

分享到QQ

分享到微博

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

发布评论

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

妳是的陽光 2024-09-01 05:28:36

Intersect 是一个集合操作 - 它旨在从交集返回一组不同的元素。在我看来，在 SQL 中使用 DISTINCT 是合理的。可能有多个子级具有相同的父级，例如 - Intersect 应该只返回该 ID 一次。

您有什么理由不想在这里使用联接吗？

var query = from parent in db.Person
            where parent.IsProcessed
            join child in db.Person.Where(child => !child.IsProcessed)
               on parent.ID equals child.ParentId.Value
            select child;

Intersect is a set operation - it is meant to return a set of distinct elements from the intersection. It seems reasonable to me that it would use DISTINCT in the SQL. There could be multiple children with the same parent, for example - Intersect should only return that ID once.

Is there any reason you don't want to use a join here?

var query = from parent in db.Person
            where parent.IsProcessed
            join child in db.Person.Where(child => !child.IsProcessed)
               on parent.ID equals child.ParentId.Value
            select child;

回复收藏 0 原文

夏见 2024-09-01 05:28:36

该查询可以字面翻译为：

var parentIds = db.Person.Where(x => x.IsProcessed)
                      .Select(x => x.Id)
                      .ToList();
var result = db.Person.Where(x => !x.IsProcessed && parentIds.Contains(x => x.Id))
                   .ToList();

The query can be translated literally into :

var parentIds = db.Person.Where(x => x.IsProcessed)
                      .Select(x => x.Id)
                      .ToList();
var result = db.Person.Where(x => !x.IsProcessed && parentIds.Contains(x => x.Id))
                   .ToList();

回复收藏 0 原文

~没有更多了~