asked on

linq problem with nullable datetime when returning data with a view

Hi
In the code below I am returning data with linq using a sql server view.
The DOB field is nullable and works fine if a date is entered in the database table however it returns nothing if the DOB field is null in database.

This forms part of a dynamic query where user can enter optional criteria. So if they do not enter DOB is defaults to "1900, 1, 1"

The DOB field needs to be nullable

var Data  =  (from qryReport in this.viewReportsData                           
                           && qryReport.DOB >= DOB
                           select qryReport);

Open in new window

mugsey

ASKER

Forgot to say the above DOB database field is of type smalldatetime

Meir Rivkin

the default value u get (1/1/1990) is SQLBaseDate of SqlDateTime structure.
unfortunately, it's not a public property so I'd suggest use a constant to validate the DOB return.
for example:

const DateTime SQL_BASE_DATE = DateTime.Parse("1/1/1990");

if the return equals SQL_BASE_DATE then u can return null inside the linq.

Fernando Soto

Hi mugsey;

The variable DOB should be defined with the DateTime? and not a DateTime. For example

DateTime? DOB = null;

Fernando

Fernando Soto

The data type DateTime? stands for a nullable DateTime.

mugsey

ASKER

OK guys thanks

So maybe all I need to do is to add

&& qryReport.DOB >= DOB && qryReport.DOB == null

So regardless it will bring back all nulls within the query?

ASKER CERTIFIED SOLUTION

kumar754

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Meir Rivkin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mugsey

ASKER

OK thanks guys