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
– oerkelens, Commented Mar 2, 2018 at 14:03
there was a typo, sorry, added a join too many times which is removed.. — Belliez
– Belliez, Commented Mar 2, 2018 at 14:11
So what doesn't work about using userReceived.Username exactly like you used user.Username? — oerkelens
– oerkelens, Commented Mar 2, 2018 at 14:16
I tried UserReceived.Username but the results would only show if UserReceived was not null. — Belliez
– Belliez, Commented Mar 2, 2018 at 14:22

Rafalon · Accepted Answer · 2018-03-02 14:30:30Z

1

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
}

edited Mar 2, 2018 at 14:30

answered Mar 2, 2018 at 14:14

Rafalon

4,5702 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Belliez Over a year ago

I tried this earlier, however it never returned any results, only results that had a "ReceivedUser" that is not null.

Rafalon Over a year ago

@Belliez I edited my answer to add the left join behaviour you seem to need

Belliez Over a year ago

this is perfect, thank you. I have a few other statements like this I can re-use your example, thanks again

Collectives™ on Stack Overflow

c# Linq Join same table twice

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related