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?

I edited your post, in order to make it more readable. Pay attention on this, since it is very important for the readers of your post. A not well formed question is difficult to be read. — Christos
– Christos, Commented Nov 12, 2016 at 8:57
@Rob , docCount is the field name of dt1 datatable. Actually I want docCount value too if UserId is DBNull. How can I get? — lashja
– lashja, Commented Nov 12, 2016 at 9:01
@AbhilashJA I'm not sure what you are asking exactly. I think you're asking about the join criteria? If A.id has a value that does not exist in B.UserId, do you want to use the B row that has UserID == null? — Rob
– Rob, Commented Nov 12, 2016 at 9:42

Rob · Accepted Answer · 2016-11-12 13:19:23Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related