How to set Sql DateTime to null from LINQ

Question

I have two cases when I would need to set DateTime field in Sql to null. I use C# and LINQ to SQL Classes. I read many questions on stackoverflow which are like my question but still I feed mine a bit different.

When we insert a new customer.

Corresponding code:

 Customer customer = new Customer();
 customer.CustomerName = txt_name.Text;
 customer.DOB = dtp_dob.Checked ? DateTime.Parse(dtp_dob.Value.ToString("yyyy-MM-dd")) : //send null here;

customer.DOB is System.DateTime.

What I tried is:

1)

 customer.DOB = dtp_dob.Checked ? DateTime.Parse(dtp_dob.Value.ToString("yyyy-MM-dd")) : SqlDateTime.Null;

But this will not succeed as SqlDateTime cannot be converted to System.DateTime.

2)

    DateTime? nullDateTime = new DateTime();
 customer.DOB = dtp_dob.Checked ? DateTime.Parse(dtp_dob.Value.ToString("yyyy-MM-dd")) : nullDateTime.Value
 

In this case, build succeeds but it will throw an SqlDateTime overflow exception.

So then how to pass null value

Property of DOB Member in LINQ Dataclass

Many of them suggest to set Auto Generated Value to true and not to insert any value. True, it works in insert cases.

But now assume, there is already a customer entry where some datetime value is set to DOB field. Now user wants to clear this value (remove the birthdate), then in this case I have to pass a null value using UpdateOn to clear the datetime filed in corresponding customer row.

Thank you very much in advance :)

Solution

You should be able to use:

Customer customer = new Customer();
customer.CustomerName = txt_name.Text;
customer.DOB = dtp_dob.Checked 
                  ? DateTime.Parse(dtp_dob.Value.ToString("yyyy-MM-dd")) 
                  : (DateTime?) null;

Note the cast of null to DateTime?. The current problem is that the compiler can't work out the type of the conditional expression based on the operands.

The problem with your second attempt is that you're creating a new non-nullable DateTime value, i.e. 1st January 0001 AD. You could change that to:

DateTime? nullDateTime = null;
customer.DOB = dtp_dob.Checked 
                  ? DateTime.Parse(dtp_dob.Value.ToString("yyyy-MM-dd")) 
                  : nullDateTime;

Comments

[Jeff Mercado]

It's nullable, couldn't you just set it to null?

[Jon Skeet]

Customer.DOB should be DateTime? rather than DateTime if the column is nullable.