c# Linq Join same table twice

Question

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

Why do you want a third join if you just want to do something with 'userReceived.Username`? Why can't you use 'userReceived.Username` just like you use `user.Username`? — oerkelens, Mar 02 '18 at 14:03
there was a typo, sorry, added a join too many times which is removed.. — Belliez, Mar 02 '18 at 14:11
So what doesn't work about using `userReceived.Username` exactly like you used `user.Username`? — oerkelens, Mar 02 '18 at 14:16
I tried UserReceived.Username but the results would only show if UserReceived was not null. — Belliez, Mar 02 '18 at 14:22

Rafalon · Accepted Answer · 2018-03-02T14:30:30.710

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
}

I tried this earlier, however it never returned any results, only results that had a "ReceivedUser" that is not null. — Belliez, Mar 02 '18 at 14:19
@Belliez I edited my answer to add the *left join* behaviour you seem to need — Rafalon, Mar 02 '18 at 14:31
this is perfect, thank you. I have a few other statements like this I can re-use your example, thanks again — Belliez, Mar 02 '18 at 14:45

c# Linq Join same table twice

1 Answers1

Linked