How to get two different username’s from joining two tables in EF Core?

I need to display data on table where createdBy and updatedBy properties display two different usernames taken from the Users table.

This is what I got so far:

IQueryable<Services> result;

result = (from u in _appContext.Users
          join s in _appContext.Services
                 on u.Id equals s.UpdatedBy
          select new Services
                     {
                         Id = s.Id,
                         CreatedBy = u.UserName, // this should display: admin
                         CreatedDate = s.CreatedDate,
                         UpdatedBy = u.UserName, // this should display: user
                         IsActive = s.IsActive,
                         UpdatedDate = s.UpdatedDate  
                     }).OrderByDescending(s => s.Id);

By this I get displayed the same (incorrect) username on both createdBy & updatedBy. In the database, these two properties have the correct userId stored.

How do I fix this? Sorry if I’m not clear with my question!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You could reverse your from/join then add a second join:

var result = from s in _appContext.Services
  join cu in _appContext.Users on cu.Id equals s.CreatedBy
  join uu in _appContext.Users on uu.Id equals s.UpdatedBy
  
select new Services 
{
  Id = s.Id,
  CreatedBy = cu.UserName,
  CreatedDate = s.CreatedDate,
  UpdatedBy = uu.UserName,
  IsActive = s.IsActive,
  UpdatedDate = s.UpdatedDate  
});

Note: I’ve not tested this with real data.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0