linq SQL statements for joining two id with single list

Question

I want to join list of users with my records list, but records list has two columns where I should use value of user list, also one of those two columns is nullable. How to join it properly? I was trying to do something like this:

var results = (from r in records
               join u in users on r.RegisteredBy equals u.Id
               join u in users on r.ModifiedBy equals u.Id
               select new CustomResult()
               {
                   Id = r.Id,
                   Name = r.Name,
                   RegisteredByName = u.Name,
                   ModifiedByName = u.Name
               }).ToList();

It didn't work as I expected, I remember that I have to set it to use default value if null.

For example I have list of users

var user1 = new User() { Id = 1, Name = "John" };
var user2 = new User() { Id = 2, Name = "Matt" }
var user3 = new User() { Id = 3, Name = "George" };

List<User> users = new List<User>(){ user1, user2, user3 };

And I have another List of my records, i.e

var record1 = new Record() { Id = 1, Name = "Record1", RegisteredBy = 1, ModifiedBy = 3};
var record2 = new Record() { Id = 2, Name = "Record2", RegisteredBy = 3, ModifiedBy = null };
var record3 = new Record() { Id = 3, Name = "Record3", RegisteredBy = 2, ModifiedBy = 1 };

List<Record> records = new List<Record>(){ record1, record2, record3 };

As a result of this join I want to make another list of class, having information that I need, i.e

var result1 = new CustomResult(){ Id = 1, Name = "Record1", RegisteredByName = "John", ModifiedByName = "George" };
var result2 = new CustomResult(){ Id = 2, Name = "Record2", RegisteredByName = "George", ModifiedByName = null };
var result3 = new CustomResult(){ Id = 1, Name = "Record3", RegisteredByName = "Matt", ModifiedByName = "John" };

In your real case, are those really lists or database tables? i.e. `IQueryable`s? If yes, what ORM framework are you using? — Ivan Stoev, May 13 '16 at 17:37
this is just example of what I want, I use ef 6 with DDD pattern, so my database contexts are different, like two different databases — K V, May 13 '16 at 17:41
Sounds like you just need an outer join? If so, see this post: http://stackoverflow.com/questions/3404975/left-outer-join-in-linq — sgeddes, May 13 '16 at 17:41

score 1 · Accepted Answer · answered May 13 '16 at 17:50

Well, you need to perform left outer join for the optional (nullable) field (and then of course chech for null when accessing related object properties):

var results = (from r in records
               join ru in users on r.RegisteredBy equals ru.Id
               join mu in users on r.ModifiedBy equals mu.Id into modifiedBy
               from mu in modifiedBy.DefaultIfEmpty()
               select new CustomResult()
               {
                   Id = r.Id,
                   Name = r.Name,
                   RegisteredByName = ru.Name,
                   ModifiedByName = mu != null ? mu.Name : string.Empty
               }).ToList();

@fqhv Not really. You probably mean C#6 "elvis" operator: `= mu?.Name ?? string.Empty` — Ivan Stoev, May 13 '16 at 17:57

linq SQL statements for joining two id with single list

1 Answers1