C#LINQ两次加入同一表

本文是小编为大家收集整理的关于C#LINQ两次加入同一表的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

使用以下LINQ语句显示结果列表，我将其连接两个名为AssetTransterterems和会员资格的表.会员表包含一个用户列表，其中每个用户被存储为GUID并包含其用户名.

我现在想添加另一列名为userreceived的列，并且像用户助手一样显示用户名而不是GUID. USERRECEIDER = TXBOXITEMS.USERRECEIVER当前显示GUID，我正在尝试获取用户名.

我不确定如何修改LINQ语句以获取从会员表中获取用户名的用户名.我添加了第二个加入:

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

但这没有显示结果.

    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，但未显示USERRECEEVERVER的所有其他结果.

谢谢

推荐答案

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> orderby> orderby条款. ):

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
}

本文地址：https://www.itbaoku.cn/post/1557187.html

百度翻译此文有道翻译此文

问题描述

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.

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.

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,
                                    });

EDIT: 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.

Thank you

推荐答案

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

Should be:

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

You should then be able to use

UserReceived = userReceived.Username

instead of

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

Edit

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
}

上一篇：Sql不在Linq中的问题

下一篇：.Distinct()条款不工作 c# MVC