DBnull in Linq query causing problems

Question

hi there

i am doing a query thus:

int numberInInterval = (from dsStatistics.J2RespondentRow item in jStats.J2Respondent
                   where item.EndTime > dtIntervalLower && item.EndTime <= dtIntervalUpper
                   select item).count();

there appear to be some dbnulls in the endtime column.. any way i can avoid these?

tried adding && where item.endtime != null.. and even != dbnull.value

do i have to do a second (first) query to grab all that arent null then run the above one?

im sure its super simple fix, but im still missing it.. as per

thanks nat

Answer 1

The way I typically do this in a T-SQL query is to use ISNULL on the date, and set the date to something like '12/31/2099' when it's null.

With Linq it could be something like this:

from t in MyTable where Convert.ToString((Convert.ToString(t.MyDateColumn) ?? "12/31/2099")) < MyTargetValue

Answer 2

I think you want to use item.EndTime.HasValue, and not item.EndTime == null.

Answer 3

The simplest way to do it is to use .GetValueOrDefault(...Some reasonable default...) on the value that can be null and it will avoid the error.