C# - Using Linq get data if null value exist in query

Question

I have two datatables,

var userList1 = from myRow in dt.AsEnumerable()
                where myRow.Field<bool?>("IsActive1") == null 
                      ? true 
                      : myRow.Field<bool?>("IsActive1") == true
                select myRow;

var userList2 = from myRow in dt1.AsEnumerable()
                select myRow;

dt1 table shows like this,

Using this Linq query,

var objUserSetUp1 = (from A in userList1
                     join B in userList2 
                     on new 
                     { 
                         UserId = A.Field<Int64?>("Id") == null 
                                      ? 0 
                                      : A.Field<Int64>("Id") 
                     } 
                     equals new 
                     { 
                         UserId = B.Field<Int64?>("UserId") == null 
                                      ? 0 
                                      : B.Field<Int64>("UserId") 
                     }
                     select new
                     {
                         UserId = A.Field<Int64>("Id"),
                         FirstName = A.Field<string>("FirstName"),
                         SurName = A.Field<string>("SurName"),
                         Computer_Name = A.Field<string>("Computer_Name"),
                         IP_Address = A.Field<string>("IP_Address"),
                         LogInTime = A.Field<string>("LogInTime") == null 
                                          ? "UnKnown" 
                                          : A.Field<string>("LogInTime"),
                         UserName = A.Field<string>("UserName"),
                         Password = A.Field<string>("Password"),
                         login_Id = A.Field<Int64?>("login_Id") == null 
                                        ? 0 : 
                                        A.Field<Int64?>("login_Id"),
                         docCount = B.Field<Int64>("docCount")
                     }).ToList();

How can I get if UserId is null also want to take docCout field value too. How can I do this inside the query?

Answer 1

I think you need a Left Outer Join, where the default value for the outer join (ie when no matching record exists) is the userList2 entry where Field("UserId") is null.

See below (untested, but you get the idea!):

    var objUserSetUp1 = (from A in userList1
                        join B in userList2 
                            on A.Field<Int64?>("Id") equals B.Field<Int64?>("UserId")
                            into BGroup
                        from C in BGroup.DefaultIfEmpty(userList2.Single(u => u.Field<Int64?>("UserId") == null))
                        select new
                        {
                            UserId = A.Field<Int64>("Id"),
                            FirstName = A.Field<string>("FirstName"),
                            SurName = A.Field<string>("SurName"),
                            Computer_Name = A.Field<string>("Computer_Name"),
                            IP_Address = A.Field<string>("IP_Address"),
                            LogInTime = A.Field<string>("LogInTime") == null
                                            ? "UnKnown"
                                            : A.Field<string>("LogInTime"),
                            UserName = A.Field<string>("UserName"),
                            Password = A.Field<string>("Password"),
                            login_Id = A.Field<Int64?>("login_Id") == null
                                        ? 0 :
                                        A.Field<Int64?>("login_Id"),
                            docCount = C.Field<Int64>("docCount")
                        }).ToList();