LINQ 帮助:混合联接和指定默认值
我试图弄清楚如何在 LINQ 中进行混合联接,并具有对 2 个 LINQ 对象的特定访问权限。下面是实际 TSQL 查询的示例:
SELECT
*
FROM
[User] AS [a]
INNER JOIN
[GroupUser] AS [b]
ON
[a].[UserID] = [b].[UserID]
INNER JOIN
[Group] AS [c]
ON
[b].[GroupID] = [c].[GroupID]
LEFT JOIN
[GroupEntries] AS [d]
ON
[a].[GroupID] = [d].[GroupID]
WHERE [a].[UserID] = @UserID
最后,基本上我想要的是一个充满 GroupEntry 对象的可枚举对象。我感兴趣的是此查询中的最后两个表/对象。我将把组显示为组标题,并将所有条目显示在组标题下。如果某个组没有条目,我仍然希望将该组视为没有任何条目的标题。到目前为止,这是我所拥有的:
因此,我想创建一个函数:
public void DisplayEntriesByUser(int user_id)
{
MyDataContext db = new MyDataContext();
IEnumberable<GroupEntries> entries =
(
from user in db.Users
where user.UserID == user_id
join group_user in db.GroupUsers
on user.UserID = group_user.UserID
into a
from join1 in a
join group in db.Groups
on join1.GroupID equals group.GroupID
into b
from join2 in b
join entry in db.Entries.DefaultIfEmpty()
on join2.GroupID equals entry.GroupID
select entry
);
Group last_group_id = 0;
foreach(GroupEntry entry in entries)
{
if (last_group_id == 0 || entry.GroupID != last_group_id)
{
last_group_id = entry.GroupID;
System.Console.WriteLine("---{0}---", entry.Group.GroupName.ToString().ToUpper());
}
if (entry.EntryID)
{
System.Console.WriteLine(" {0}: {1}", entry.Title, entry.Text);
}
}
}
上面的示例并没有按预期工作。有两个问题我无法解决:
我似乎仍然在最后一次连接中得到 INNER JOIN 而不是 LEFT JOIN。我没有得到任何空结果,因此没有条目的组不会出现。
我需要找到一种方法,以便可以填写空白条目集的默认值。也就是说,如果有一个没有条目的组,我希望返回一个大部分空白的条目,除了我希望 EntryID 为 null 或 0,GroupID 是它所代表的空组的 ID ,并且我需要entry.Group对象的句柄(即它是父级的空Group对象)。
对此的任何帮助将不胜感激。
注意:表名称和现实世界的表示纯粹是为了这个示例而派生的,但它们的关系简化了我想要做的事情。
I am trying to figure out how to do a mixed-join in LINQ with specific access to 2 LINQ objects. Here is an example of how the actual TSQL query might look:
SELECT
*
FROM
[User] AS [a]
INNER JOIN
[GroupUser] AS [b]
ON
[a].[UserID] = [b].[UserID]
INNER JOIN
[Group] AS [c]
ON
[b].[GroupID] = [c].[GroupID]
LEFT JOIN
[GroupEntries] AS [d]
ON
[a].[GroupID] = [d].[GroupID]
WHERE [a].[UserID] = @UserID
At the end, basically what I would like is an enumerable object full of GroupEntry objects. What am interested is the last two tables/objects in this query. I will be displaying Groups as a group header, and all of the Entries underneath their group heading. If there are no entries for a group, I still want to see that group as a header without any entries. Here's what I have so far:
So from that I'd like to make a function:
public void DisplayEntriesByUser(int user_id)
{
MyDataContext db = new MyDataContext();
IEnumberable<GroupEntries> entries =
(
from user in db.Users
where user.UserID == user_id
join group_user in db.GroupUsers
on user.UserID = group_user.UserID
into a
from join1 in a
join group in db.Groups
on join1.GroupID equals group.GroupID
into b
from join2 in b
join entry in db.Entries.DefaultIfEmpty()
on join2.GroupID equals entry.GroupID
select entry
);
Group last_group_id = 0;
foreach(GroupEntry entry in entries)
{
if (last_group_id == 0 || entry.GroupID != last_group_id)
{
last_group_id = entry.GroupID;
System.Console.WriteLine("---{0}---", entry.Group.GroupName.ToString().ToUpper());
}
if (entry.EntryID)
{
System.Console.WriteLine(" {0}: {1}", entry.Title, entry.Text);
}
}
}
The example above does not work quite as expected. There are 2 problems that I have not been able to solve:
I still seem to be getting an INNER JOIN instead of a LEFT JOIN on the last join. I am not getting any empty results, so groups without entries do not appear.
I need to figure out a way so that I can fill in the default values for blank sets of entries. That is, if there is a group without an entry, I would like to have a mostly blank entry returned, except that I'd want the EntryID to be null or 0, the GroupID to be that of of the empty group that it represents, and I'd need a handle on the entry.Group object (i.e. it's parent, empty Group object).
Any help on this would be greatly appreciated.
Note: Table names and real-world representation were derived purely for this example, but their relations simplify what I'm trying to do.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这尚未经过测试,但我认为它非常接近:
我认为您不需要使用
into
除非您打算进行一些进一步的处理,例如DefaultIfEmpty()
。请注意,DefaultIfEmpty()
的第二个重载允许您输入自定义默认值。因此,您可以创建一个新的GroupEntry
对象,并为每个属性分配所需的值(或将属性留空)。This is untested, but I think it's pretty close:
I don't think you need to use
into
unless you're planning to do some further processing, such asDefaultIfEmpty()
. And notice that the second overload ofDefaultIfEmpty()
allows you to enter a custom default value. So, you can create a newGroupEntry
object and assign the values you want for each property (or leave properties blank).