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.

Of course, if you moved your business logic out of your database and into your code, then you wouldn't have this issue :-) — Paul Hiles, Jan 20 '11 at 17:16
some things like date created are much better to have in the db — Andrey, Jan 20 '11 at 17:20
@Andrey: A lot of people would strongly disagree with you. Maybe this was acceptable 10 years ago, but now all business logic should arguably be centralised in code. — Paul Hiles, Jan 20 '11 at 18:13
i don't think that's business, date added is data integrity. If there is a business added date, i would store it separately. — Andrey, Jan 20 '11 at 19:06

score 4 · Accepted Answer · edited May 23 '17 at 10:33

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.

score 0 · Answer 2 · answered Jan 20 '11 at 17:12

0

I recommend you open your DBML file using XML Editor and change that column's type from DateTime to DateTime? by allowing nulls. Save it and the code will be generated for you.

Whenever LINQ to SQL generates wrong DBML, it is better to edit it yourself.

answered Jan 20 '11 at 17:12

decyclone

30,394
6
63
80

the dbml generates the wrong xml because you set up the settings wrong. See my answer – Andrey Jan 20 '11 at 17:18
3

Thanks for the response. but setting this value to NULL was not worked. As shaunmartin and Andrey mentioned turning "Auto Generated Value = true" is the correct thing. Anyway, thanks for helping. :) – Sency Jan 21 '11 at 14:22

score 0 · Answer 3 · answered Jan 20 '11 at 17:16

0

Open your dbml and turn on "Auto Generated Value" = true for the fields that are auto generated. You should be all good with passing nulls in

answered Jan 20 '11 at 17:16

Andrey

1,247
11
30

Thanks Andrey.It passing a NULL value. But since this field is NOT NULL, this error is occurring. "Cannot insert the value NULL into column 'UpdatedOn', table 'myDataContext.tbl_CurrentProperty'; column does not allow nulls. INSERT fails. The statement has been terminated." – Sency Jan 20 '11 at 17:44
This looks like a design problem. Your db should either be reviving a value to set the field, auto generating the value (in which case my solution would work), or allowing the field to be null. You should not be using something like a min date time to represent a null. If you for some reason do need the sql min date time in your code you can get it as System.Data.SqlTypes.SqlDateTime.MinValue() – Andrey Jan 20 '11 at 19:12
3

@Kushan: This answer is correct. As shaunmartin has noted, it will not work if combined with decyclone's answer. You need to set the column back to be not nullable and to `DateTime` (not `DateTime?`) and then set `Auto Generated = True` as Andrey suggested. – Allon Guralnek Jan 20 '11 at 22:27

Pass a NULL value to DateTime Field in LINQ

3 Answers3