LINQ to SQL-自连接子级到父级(同一表) [英] LINQ to SQL - Self Join Child to Parent (Same Table)
问题描述
出于某种原因，我很难确定如何做到这一点.
For some reason I'm having a hard time wrapping my mind around how to do this.
我有一张留言表.(它是一个非常大的现有遗留系统的一部分，并且我无法更改表的定义方式.)只有1个帖子表.它是一个两级层次结构.每个帖子都是父母"或孩子".孩子们是回复父母的职位.(没有其他级别.)
I have a table of message posts. (It is part of a very large existing legacy system, and I can't change the way the tables are defined.) There is only 1 table of posts. It is a two-level hierarchy. Each post is either a "parent" or a "child". Children are posts replying to parents. (There are no other levels.)
例如，非常简单地，可能会有一个Posts表，如下所示:
So for example, very simply there might a Posts table that looks like this:
ID,ParentID,Content,UserID
===========================
1,0,"Hello",3
2,1,"Reply to Hello",7
3,1,"Another Reply to Hello",4
4,0,"New Post",2
...etc...
因此，假设定义了一个非常简单的Posts表，如下所示:
So imagine that a very simple Posts table is defined like this:
INT ID [identity value for the post],
INT ParentID [0 == no parent, otherwise the value is the parent identity value]
TEXT Content [just imagine it is text]
INT UserID [the author of the post]
这是问题所在.我想找到对特定用户的所有回复.
Here's the problem. I would like to find all replies made to a particular User.
因此，例如，如果我想查找对上面的UserID#3的所有答复，我应该提出以下建议:
So, for example, if I wanted to find all replies made to UserID #3 above, I should come up with this:
ID,ParentID,Content,UserID
===========================
2,1,"Reply to Hello",7
3,1,"Another Reply to Hello",4
这是因为UserID#3发布了帖子ID#1，而这两个是答复.
That's because UserID #3 posted post ID #1, and those two were replies.
通过UserID#3查找所有父帖子很简单.我会这样做:
It is simple to find all parent posts by UserID #3. I would just do this:
var posts = (from db.Posts
where p.UserID == 3
&& p.ParentID == 0 // this is a parent
select p).ToList();
同样，要查找不是UserID#3的所有子帖子(回复)，我可以这样做:
Likewise, to find all child (reply) posts not made by UserID #3, I would just do this:
var posts = (from db.Posts
where p.UserID != 3
&& p.ParentID != 0 // this is a child
select p).ToList();
但是我如何找到仅对UserID#3的所有回复?
But how do I find all replies made ONLY to UserID #3???
想象一下，过去10年中，Posts表有1,000,000行，并且可能只有3行是答复，所以我不能完全将所有表都蛮行地放入某个List中，然后进行排序.我需要执行1 LINQ to SQL查询，仅返回所需的3行.
Imagine the Posts table has 1,000,000 rows from the last 10 years, and there may only be 3 rows that are replies, so I can't exactly just brute all of it into some List and then sort through. I need to do 1 LINQ to SQL query that only returns the needed 3 rows.
如果我可以这样做，它将起作用:
If I could do this, it would work:
int userId = 3;
var posts = (from p in db.Posts
where p.UserID != 3
&& p.ParentID != 0 // this is a child
&& DID_USER_CREATE_POST(userId,p.ID) // can't do this -- since this imaginary C# function won't work here
select p).ToList();
我认为我需要做某种自联接(???)，因为父级和子级都在同一个表中，以得出这3个需要的行..但是我还没有弄清楚如何使用现有的表格结构来做到这一点.
I think I need to do some sort of self-join (???), since the parent and child are in the same table, to come up with those 3 needed rows.. but I have yet to figure out how to do this with the existing table structure.
没有人知道我如何使用LINQ to SQL来完成此任务.我正在使用C#，代码将在ASP.NET MVC控制器(发出RSS)中.
Does anyone have any idea how I can accomplish this using LINQ to SQL. I'm using C# and the code will be in an ASP.NET MVC controller (that emits RSS).
感谢您的帮助！
推荐答案
如果您具有正确的外键设置，则LINQ应该会看到引用，因此您的查询将是这样.
If you have proper Foreign Key setup, the LINQ should see the reference, so your query would be something like this.
var posts = from p in db.Posts
where p.Parent != null //Looking for children only
&& p.Parent.UserId == 3 //UserId of the parent post
引用对象的调用方式可能有所不同.如果您看不到任何此类对象，则也可以通过join实现相同的目的.
The reference object might be called differently. If you don't see any of this kind of object, you can achieve the same thing by join as well.
from post in db.Posts
join parent in db.Posts on post.ParentId equals parent.ID
where parent.UserId == 3
select post
我省略了其他更简单的 where 子句.
I omitted the other simpler where clauses.
这篇关于LINQ to SQL-自连接子级到父级(同一表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
