Pass a NULL value to DateTime Field in LINQ

Question

My database table is like this

CREATE TABLE MYBUDGET.tbl_CurrentProperty
(
    [PropID]            INT             NOT NULL  IDENTITY(1,1),
    [UpdatedOn]         DATETIME        NOT NULL,
    [Amount]            MONEY           NOT NULL,
    [Remarks]           VARCHAR(100)    NOT NULL,
)
ALTER TABLE MYBUDGET.tbl_CurrentProperty ADD CONSTRAINT PK_CurrentProperty_PropID PRIMARY KEY ([PropID])
ALTER TABLE MYBUDGET.tbl_CurrentProperty ADD CONSTRAINT DF_CurrentProperty_UpdatedOn DEFAULT (DATEADD(MINUTE,30,DATEADD(HOUR, 5, GETUTCDATE()))) FOR [UpdatedOn]
ALTER TABLE MYBUDGET.tbl_CurrentProperty ADD CONSTRAINT CK_CurrentProperty_Amount CHECK([Amount] > -1)
GO

I'm using LINQ to SQL. In C# I need to pass only [Amount] and [Remarks] fields and other fields must be used with their default values ([PropID] and [UpdatedOn]).

In C# I create tbl_CurrentProperties object like below,

tbl_CurrentProperties currentProperties = new tbl_CurrentProperties();
currentProperties.Amount = 50.00M;
currentProperties.Remarks = "remarks";

and then submit the object to the data context. But here, Linq assigned '1/1/0001 12:00:00 AM' for UpdatedOn field. But this violate the SQL datetime range 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM and Occurring an exception. Also I can't assign a NULL value manually for a DateTime field since its a not nullable type. Anyhow I need to make this to use its DEFAULT Constraint. How do I do this?

---

PS: I want to use it like this because, My database is Online and Users are in different locations. So If I used DateTime.Now, the time in the user machine may be wrong, and It insert a wrong value into DB. I need to use SQL server time always.

Answer 1

Andrey's answer is partly right. I just tested this and here's what I found.

In your dbml designer, on your UpdatedOn column set the following:

Auto Generated Value = True 
Nullable = False

Then, on an INSERT if you use SQL Server Profiler to look at the generated SQL, you'll see that UpdatedOn is not included in the INSERT. Not even a null value. This is important: for SQL Server to use a default value for that colum, the column must be omitted from the INSERT. If you set Nullable = True on the UpdatedOn, LINQ to SQL might be including the column on the INSERT with a null value.

FYI, immediately after the INSERT there should be a SELECT where LINQ to SQL is retrieving the auto-generated value, so your entity object has the latest value.