CODEWITHSUNDEEP
linq
Nate Pet
Nate Pet

Reputation: 46322

Linq using nullable datetime field

I have a problem with LINQ (using EF - 4.3.1.0) using the following:

    DateTime? dtcollected = DateTime.TryParse(dateCollected, out dateVal) ? dateVal : (DateTime?)null;
    DateTime?  dtanalyzed =  DateTime.TryParse(dateanalyzed, out dateVal) ? dateVal : (DateTime?)null; 


     var doesexist = (from pw in dbContext.WtTbl                                      
                      where pw.CompanyId == 13
                      && pw.DateCollected == dtcollected
                      && pw.DateAnalyzed == dtanalyzed                                    
                      select pw).Any();

Note that dateCollected came in as a string so I had to convert it to a nullable DateTime. Same goes for dateanalyzed.

What I am struck at is that I have a companyId of 13. A null value of dtcollected. And a value for dtanalyzed already in the table so I would expect doesexist to return true, but it returns false.

If I comment out 

     var doesexist = (from pw in dbContext.WtTbl                                      
                      where pw.CompanyId == 13
                    //   && pw.DateCollected == dtcollected
                      && pw.DateAnalyzed == dtanalyzed                                    
                      select pw).Any();

or put:

      var doesexist = (from pw in dbContext.WtTbl                                      
                      where pw.CompanyId == 13
                      && pw.DateCollected == null
                      && pw.DateAnalyzed == dtanalyzed                                    
                      select pw).Any();

Then I get a true. How come it is not able to comprehend null value of dtcollected? Am I doing something wrong.

Upvotes: 1

Views: 1093

Answers (1)

casperOne
casperOne

Reputation: 74560

In most database systems (definitely SQL Server), if one side of the comparison is null, then the result of the comparison is unknown, and therefore not included in the result set (or, for all intents and purposes, false).

That said, you need to perform a check for null against your variables, only checking against the database field if the parameter is non-null, like so:

var doesexist = (
    from pw in dbContext.WtTbl                                      
    where 
        pw.CompanyId == 13 && 
        (dtcollected == null || pw.DateCollected == dtcollected) &&
        (dtanalyzed == null || pw.DateAnalyzed == dtanalyzed)
    select pw).Any();

This translates roughly to:

declare @dtcollected date = null
declare @dtanalyzed date = null

select 
    * 
from 
    Table as t
where
    (@dtcollected is null or t.DateCollected = @dtcollected) and
    (@dtanalyzed is null or t.DateAnalyzed  = @dtanalyzed)

Upvotes: 1

Related Questions