CODEWITHSUNDEEP
c#linqentity-frameworknull
Asif
Asif

Reputation: 393

Comparing null values in LINQ expressions

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?

Upvotes: 1

Views: 2184

Answers (3)

RobH
RobH

Reputation: 3612

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

Upvotes: 1

Mez
Mez

Reputation: 4726

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

Upvotes: 1

Paul Connolly
Paul Connolly

Reputation: 371

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

Upvotes: -1

Related Questions