Pass a NULL value to DateTime Field in LINQ
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.开发者_运维知识库
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.
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.
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
精彩评论