Abhilash JA Abhilash JA - 23 days ago 4
C# Question

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

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,

enter image description here

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?

Rob Rob
Answer

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