Comparing null values in LINQ expressions

Question

I have a table with a “t_cftc_ir_swaps” with following fields

T_CFTC_IR_SWAP_ID       Integer (Autonumber, PK)
PRODUCT                 Varchar
TRADEVOLUME_TYPE        Varchar
TRADEVOLUME_BUCKET_1    Varchar
TRADEVOLUME_BUCKET_2    Varchar
TRADEVOLUME_GROUP       Varchar
TRADEVOLUME             Integer
RELEASE_DATE            Date
TRADE_DATE              Date

I am using Entity Framework at the code level. I am trying to insert a record into this table. But before doing that I am checking if the record already exists in the table. For that purpose I have a “RecordAlreadyExists” function code as follows

Record already exists function

Now in some of the records in the data table we have null values for “TRADEVOLUME_BUCKET_2” but while inserting a new record if I try to compare the record being added (with a null value for TRADEVOLUME_BUCKET_2) against the underlying table records it returns “0” records even though I can query the database and see there are matching records in the table.

At the code break this is the record I am trying to insert. As you can see it has null value for “TRADEVOLUME_BUCKET_2”

Code at runtime

The count of records matching with the records which we are trying to add is “0”

enter image description here

When I run a query at the database level I do get a matching record already in the underlying table

data query query result

I have even tries using DBNull at code as follows

use of db null

But get the following exception when I try to do that at runtime “Unable to create a null constant value of type ‘System.Object’. Only entity types, enumeration types or primitive types are supported in this context”

db null exception

How should I compare null values using LINQ query?

@RobH - - Undelrying query is a select query on all records and doesn't show the where clause. SELECT "Extent1"."T_CFTC_IR_SWAPS_ID" AS "T_CFTC_IR_SWAPS_ID", "Extent1"."PRODUCT" AS "PRODUCT", "Extent1"."TRADEVOLUME_TYPE" AS "TRADEVOLUME_TYPE", "Extent1"."TRADEVOLUME_BUCKET_1" AS "TRADEVOLUME_BUCKET_1", "Extent1"."TRADEVOLUME_BUCKET_2" AS "TRADEVOLUME_BUCKET_2", "Extent1"."TRADEVOLUME_GROUP" AS "TRADEVOLUME_GROUP", "Extent1"."TRADEVOLUME" AS "TRADEVOLUME", "Extent1"."RELEASE_DATE" AS "RELEASE_DATE", "Extent1"."TRADE_DATE" AS "TRADE_DATE" FROM "CLOUDDBA"."T_CFTC_IR_SWAPS" "Extent1" — Asif, Oct 29 '14 at 11:32
@GertArnold - Thats the query I can see in the Autos section in visual studio when I put a break on the code line intExistingCount = CftcContext.T_CFTC_IR_SWAPS.Count( rec => (object)rec.TRADEVOLUME_BUCKET_2 == ((object)_record.TRADEVOLUME_BUCKET_2 ?? DBNull.Value) ); So it does show the entire record set on which the query is going to be applied on but doesn't show the where clause conditions which I am adding on top of it — Asif, Oct 29 '14 at 11:43
That's only the ToString of the DbSet object. Now please monitor the query as it is sent to the database. It should be different. — Gert Arnold, Oct 29 '14 at 11:47

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

Try giving a default value, to your property. I think you are facing a problem of NULL not being compared in Linq ... but default values such as an empty string is.

Check out these links

edited May 23 '17 at 12:15

Community

1
1

answered Oct 29 '14 at 11:14

Mez

4,666
4
29
57

RobH · Accepted Answer · 2014-10-29T11:49:02.807

Try looking for null specifically:

public bool RecordAlreadyExists(object cftcRecord)
{
    var _intExistingCount = 0;
    var _record = cftcRecord as T_CFTC_IR_SWAPS;
    if (_record != null)
    {
        _intExistingCount = 
        CftcContext.T_CFTC_IR_SWAPS.Count(
            rec => _record.TRADEVOLUME_BUCKET_2 == null 
                    ? rec.TRADEVOLUME_BUCKET_2 == null 
                    : _record.TRADEVOLUME_BUCKET_2 == rec.TRADEVOLUME_BUCKET_2
            );
    }
    // More code...
}

I've changed your code slightly (it's better to include code in your question as text so answerers can copy it). I think this is the problem you are encountering: http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/1015361-incorrect-handling-of-null-variables-in-where-cl

score -1 · Answer 3 · answered Oct 29 '14 at 11:02

-1

Use the null coalescing operator on something other than DBNull.Value.

answered Oct 29 '14 at 11:02

Paul Connolly

359
2
8

Comparing null values in LINQ expressions

3 Answers3