GrandaS GrandaS - 2 months ago 6
SQL Question

linq SQL statements for joining two id with single list

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" };

Answer

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();
Comments