Equivalent of SQL ISNULL in LINQ

Question

In SQL you can run a ISNULL null     how would you do this in a linq query   I have a join in this query   var hht   from x in db HandheldAssets         join a in db HandheldDevInfos on x AssetID equals a DevName into DevInfo         from aa in DevInfo DefaultIfEmpty           select new                   AssetID   x AssetID          Status   xx Online              but I have a column that has a bit type that is non nullable  xx online  how can I set this to false if it is null

User · Answer

I often have this problem with sequences (as opposed to discrete values). If I have a sequence of ints, and I want to SUM them, when the list is empty I'll receive the error "InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.".

I find I can solve this by casting the sequence to a nullable type. SUM and the other aggregate operators don't throw this error if a sequence of nullable types is empty.

So for example something like this

MySum = MyTable.Where(x => x.SomeCondtion).Sum(x => x.AnIntegerValue);

becomes

MySum = MyTable.Where(x => x.SomeCondtion).Sum(x => (int?) x.AnIntegerValue);

The second one will return 0 when no rows match the where clause. (the first one throws an exception when no rows match).

User · Answer

Since aa is the set object that might be null  can you check aa    null     aa   xx might be interchangeable  a typo in the question   the original question talks about xx but only defines aa   i e   select new       AssetID   x AssetID      Status   aa    null    bool  null   aa Online     a Nullable lt bool gt      or if you want the default to be false  not null    select new       AssetID   x AssetID      Status   aa    null   false   aa Online        Update  in response to the downvote  I ve investigated more    the fact is  this is the right approach  Here s an example on Northwind           using var ctx   new DataClasses1DataContext                          ctx Log   Console Out              var qry   from boss in ctx Employees                       join grunt in ctx Employees                           on boss EmployeeID equals grunt ReportsTo into tree                       from tmp in tree DefaultIfEmpty                         select new                                                                 ID   boss EmployeeID                                   Name   tmp    null        tmp FirstName                                        foreach var row in qry                                Console WriteLine   0    1    row ID  row Name                             And here s the TSQL - pretty much what we want  it isn t ISNULL  but it is close enough    SELECT  t0   EmployeeID  AS  ID        CASE         WHEN  t2   test  IS NULL THEN CONVERT NVarChar 10   p0          ELSE  t2   FirstName       END  AS  Name  FROM  dbo   Employees  AS  t0  LEFT OUTER JOIN       SELECT 1 AS  test    t1   FirstName    t1   ReportsTo      FROM  dbo   Employees  AS  t1        AS  t2  ON   t0   EmployeeID      t2   ReportsTo  --  p0  Input NVarChar  Size   0  Prec   0  Scale   0     -- Context  SqlProvider Sql2008  Model  AttributedMetaModel Build  3 5 30729 1   QED

User · Answer

Since aa is the set object that might be null  can you check aa    null     aa   xx might be interchangeable  a typo in the question   the original question talks about xx but only defines aa   i e   select new       AssetID   x AssetID      Status   aa    null    bool  null   aa Online     a Nullable lt bool gt      or if you want the default to be false  not null    select new       AssetID   x AssetID      Status   aa    null   false   aa Online        Update  in response to the downvote  I ve investigated more    the fact is  this is the right approach  Here s an example on Northwind           using var ctx   new DataClasses1DataContext                          ctx Log   Console Out              var qry   from boss in ctx Employees                       join grunt in ctx Employees                           on boss EmployeeID equals grunt ReportsTo into tree                       from tmp in tree DefaultIfEmpty                         select new                                                                 ID   boss EmployeeID                                   Name   tmp    null        tmp FirstName                                        foreach var row in qry                                Console WriteLine   0    1    row ID  row Name                             And here s the TSQL - pretty much what we want  it isn t ISNULL  but it is close enough    SELECT  t0   EmployeeID  AS  ID        CASE         WHEN  t2   test  IS NULL THEN CONVERT NVarChar 10   p0          ELSE  t2   FirstName       END  AS  Name  FROM  dbo   Employees  AS  t0  LEFT OUTER JOIN       SELECT 1 AS  test    t1   FirstName    t1   ReportsTo      FROM  dbo   Employees  AS  t1        AS  t2  ON   t0   EmployeeID      t2   ReportsTo  --  p0  Input NVarChar  Size   0  Prec   0  Scale   0     -- Context  SqlProvider Sql2008  Model  AttributedMetaModel Build  3 5 30729 1   QED

User · Answer

Looks like the type is boolean and therefore can never be null and should be false by default

User · Answer

Looks like the type is boolean and therefore can never be null and should be false by default

User · Answer

You can use the    operator to set the default value but first you must set the Nullable property to true in your dbml file in the required field  xx Online   var hht   from x in db HandheldAssets         join a in db HandheldDevInfos on x AssetID equals a DevName into DevInfo         from aa in DevInfo DefaultIfEmpty           select new                   AssetID   x AssetID          Status   xx Online    false

User · Answer

You can use the    operator to set the default value but first you must set the Nullable property to true in your dbml file in the required field  xx Online   var hht   from x in db HandheldAssets         join a in db HandheldDevInfos on x AssetID equals a DevName into DevInfo         from aa in DevInfo DefaultIfEmpty           select new                   AssetID   x AssetID          Status   xx Online    false

User · Answer

Since aa is the set object that might be null  can you check aa    null     aa   xx might be interchangeable  a typo in the question   the original question talks about xx but only defines aa   i e   select new       AssetID   x AssetID      Status   aa    null    bool  null   aa Online     a Nullable lt bool gt      or if you want the default to be false  not null    select new       AssetID   x AssetID      Status   aa    null   false   aa Online        Update  in response to the downvote  I ve investigated more    the fact is  this is the right approach  Here s an example on Northwind           using var ctx   new DataClasses1DataContext                          ctx Log   Console Out              var qry   from boss in ctx Employees                       join grunt in ctx Employees                           on boss EmployeeID equals grunt ReportsTo into tree                       from tmp in tree DefaultIfEmpty                         select new                                                                 ID   boss EmployeeID                                   Name   tmp    null        tmp FirstName                                        foreach var row in qry                                Console WriteLine   0    1    row ID  row Name                             And here s the TSQL - pretty much what we want  it isn t ISNULL  but it is close enough    SELECT  t0   EmployeeID  AS  ID        CASE         WHEN  t2   test  IS NULL THEN CONVERT NVarChar 10   p0          ELSE  t2   FirstName       END  AS  Name  FROM  dbo   Employees  AS  t0  LEFT OUTER JOIN       SELECT 1 AS  test    t1   FirstName    t1   ReportsTo      FROM  dbo   Employees  AS  t1        AS  t2  ON   t0   EmployeeID      t2   ReportsTo  --  p0  Input NVarChar  Size   0  Prec   0  Scale   0     -- Context  SqlProvider Sql2008  Model  AttributedMetaModel Build  3 5 30729 1   QED

User · Answer

I often have this problem with sequences (as opposed to discrete values). If I have a sequence of ints, and I want to SUM them, when the list is empty I'll receive the error "InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.".

I find I can solve this by casting the sequence to a nullable type. SUM and the other aggregate operators don't throw this error if a sequence of nullable types is empty.

So for example something like this

MySum = MyTable.Where(x => x.SomeCondtion).Sum(x => x.AnIntegerValue);

becomes

MySum = MyTable.Where(x => x.SomeCondtion).Sum(x => (int?) x.AnIntegerValue);

The second one will return 0 when no rows match the where clause. (the first one throws an exception when no rows match).

User · Answer

You can use the    operator to set the default value but first you must set the Nullable property to true in your dbml file in the required field  xx Online   var hht   from x in db HandheldAssets         join a in db HandheldDevInfos on x AssetID equals a DevName into DevInfo         from aa in DevInfo DefaultIfEmpty           select new                   AssetID   x AssetID          Status   xx Online    false

User · Answer

Since aa is the set object that might be null  can you check aa    null     aa   xx might be interchangeable  a typo in the question   the original question talks about xx but only defines aa   i e   select new       AssetID   x AssetID      Status   aa    null    bool  null   aa Online     a Nullable lt bool gt      or if you want the default to be false  not null    select new       AssetID   x AssetID      Status   aa    null   false   aa Online        Update  in response to the downvote  I ve investigated more    the fact is  this is the right approach  Here s an example on Northwind           using var ctx   new DataClasses1DataContext                          ctx Log   Console Out              var qry   from boss in ctx Employees                       join grunt in ctx Employees                           on boss EmployeeID equals grunt ReportsTo into tree                       from tmp in tree DefaultIfEmpty                         select new                                                                 ID   boss EmployeeID                                   Name   tmp    null        tmp FirstName                                        foreach var row in qry                                Console WriteLine   0    1    row ID  row Name                             And here s the TSQL - pretty much what we want  it isn t ISNULL  but it is close enough    SELECT  t0   EmployeeID  AS  ID        CASE         WHEN  t2   test  IS NULL THEN CONVERT NVarChar 10   p0          ELSE  t2   FirstName       END  AS  Name  FROM  dbo   Employees  AS  t0  LEFT OUTER JOIN       SELECT 1 AS  test    t1   FirstName    t1   ReportsTo      FROM  dbo   Employees  AS  t1        AS  t2  ON   t0   EmployeeID      t2   ReportsTo  --  p0  Input NVarChar  Size   0  Prec   0  Scale   0     -- Context  SqlProvider Sql2008  Model  AttributedMetaModel Build  3 5 30729 1   QED

User · Answer

You can use the    operator to set the default value but first you must set the Nullable property to true in your dbml file in the required field  xx Online   var hht   from x in db HandheldAssets         join a in db HandheldDevInfos on x AssetID equals a DevName into DevInfo         from aa in DevInfo DefaultIfEmpty           select new                   AssetID   x AssetID          Status   xx Online    false

User · Answer

Looks like the type is boolean and therefore can never be null and should be false by default

User · Answer

Looks like the type is boolean and therefore can never be null and should be false by default

[c#] Equivalent of SQL ISNULL in LINQ?

Examples related to c#

Examples related to sql

Examples related to linq

Examples related to isnull