C#Linq两次联接同一表 [英] c# Linq Join same table twice

问题描述

使用下面的linq语句，我显示一个结果列表，该列表将两个表称为AssetTransferItems和Memberships联接在一起.成员资格表包含一个用户列表，其中每个用户均以GUID的形式存储，并包含其用户名.

Using the below linq statement I show a list of results which I join two tables called AssetTransferItems and Memberships. Memberships table contains a list of users where each user is stored as a GUID and contains their username.

我现在想添加另一个名为UserReceived的列，就像UserAdded一样显示用户名而不是GUID. UserReceived = txboxItems.UserReceived当前显示GUID，我正在尝试获取用户名.

I now want to add another column called UserReceived and like the UserAdded show the Username instead of GUID. UserReceived = txboxItems.UserReceived currently displays the GUID and I am trying to get the username.

我不确定如何修改Linq语句以从成员资格表中获取UserReceived的用户名.我添加了第二个连接:

I am not sure how I can modify the Linq statement to grab the username from the membership table for UserReceived. I added a 2nd join:

join userReceived in Memberships on txboxItems.UserReceived equals userReceived.UserId

但是这没有显示结果.

But this did not display the results.

    var query = (from txboxItems in AssetTransferItems
                                        join user in Memberships on txboxItems.UserAdded equals user.UserId
                                        join userReceived in Memberships on txboxItems.UserReceived equals userReceived.UserId

                                     where txboxItems.TransferBoxID == BoxId && txboxItems.Deleted == false
                                    orderby txboxItems.TicketID descending
                                     select new
                                    {
                                        Description = txboxItems.Description.ToString(),
                                        DateAdded = (DateTime?) txboxItems.DateAdded.Value,
                                        UserAdded = user.Username,
                                        DateReceived = (DateTime?) txboxItems.DateReceived.Value,
                                        UserReceived = userReceived.Username,
                                    });

我更新了linq语句以反映我现在所拥有的.它显示userReceived.Username，但不显示UserReceived为null的所有其他结果.

I updated the linq statement to reflect what I have now. It shows the userReceived.Username but all other results where UserReceived is null are not shown.

谢谢

推荐答案

join user in Memberships on txboxItems.UserAdded equals user.UserId
join userReceived in Memberships on txboxItems.UserAdded equals userReceived.UserId

应该是:

join user in Memberships on txboxItems.UserAdded equals user.UserId
join userReceived in Memberships on txboxItems.UserReceived equals userReceived.UserId
// ------------------------------------------------^^^^^^^^

您应该可以使用

UserReceived = userReceived.Username

代替

UserReceived = txboxItems.UserReceived
// ------------^^^^^^^^^^^^^^^^^^^^^^^

---

修改

从此答案中，以下内容应该可以正常工作(出于示例的可读性，我删除了where和orderby子句):

From this answer, the following should work (I removed where and orderby clauses for the example's readability):

from txboxItems in AssetTransferItems
from user in Memberships
    .Where(u => u.UserId == txboxItems.UserAdded).DefaultIfEmpty()
from userReceived in Memberships
    .Where(u => u.UserId == txboxItems.UserReceived).DefaultIfEmpty()
select new
{
     Description = txboxItems.Description.ToString(),
     DateAdded = (DateTime?) txboxItems.DateAdded.Value,
     UserAdded = user?.Username,
     DateReceived = (DateTime?) txboxItems.DateReceived.Value,
     UserReceived = userReceived?.Username
}

这篇关于C#Linq两次联接同一表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！