将NULL值传递给LINQ中的DateTime字段

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]).

我正在使用LINQ to SQL。在C＃中，我只需传递[Amount]和[Remarks]字段，其他字段必须使用其默认值（[PropID]和[UpdatedOn]）。

In C# I create tbl_CurrentProperties object like below,

在C＃中我创建了如下的tbl_CurrentProperties对象，

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?

然后将对象提交到数据上下文。但是在这里，Linq为UpdatedOn字段分配了“1/1/0001 12:00:00 AM”。但这违反了SQL日期时间范围1/1/1753 12:00:00 AM和12/31/9999 11:59:59 PM并发生异常。此外，我不能手动为DateTime字段分配NULL值，因为它是一个不可为空的类型。无论如何，我需要使这个使用它的DEFAULT约束。我该怎么做呢？

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.

PS：我想这样使用它，因为我的数据库是在线的，用户在不同的位置。因此，如果我使用DateTime.Now，则用户计算机中的时间可能是错误的，并且它向DB中插入了错误的值。我总是需要使用SQL服务器时间。

3 个解决方案

#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:

在dbml设计器中，在UpdatedOn列上设置以下内容：

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.

然后，如果使用SQL Server Profiler查看生成的SQL，则在INSERT上，您将看到INSERT中未包含UpdatedOn。甚至不是空值。这很重要：要使SQL Server使用该列的默认值，必须从INSERT中省略该列。如果在UpdatedOn上设置Nullable = True，则LINQ to SQL可能会在INSERT上包含空值的列。

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.

仅供参考，在INSERT之后应该有一个SELECT，其中LINQ to SQL正在检索自动生成的值，因此您的实体对象具有最新值。

#2

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

打开dbml并为自动生成的字段启用“Auto Generated Value”= true。你应该在传递空值方面做得很好

#3

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.

我建议您使用XML Editor打开DBML文件，并将该列的类型从DateTime更改为DateTime？通过允许空值。保存它，代码将为您生成。

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

每当LINQ to SQL生成错误的DBML时，最好自己编辑它。

#1