我有一个不可为空数据库列其中有一个默认值集。当插入行，有时一个值被指定为列，有时一个不是。这TSQL工作正常省略列时。例如，给定如下表：

I have a non-nullable database column which has a default value set. When inserting a row, sometimes a value is specified for the column, sometimes one is not. This works fine in TSQL when the column is omitted. For example, given the following table:

CREATE TABLE [dbo].[Table1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col1] [nvarchar](50) NOT NULL,
    [col2] [nvarchar](50) NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO
ALTER TABLE [dbo].[Table1] 
    ADD CONSTRAINT [DF_Table1_col1] DEFAULT ('DB default') FOR [col1]

下面的两个语句将工作：

The following two statements will work:

INSERT INTO Table1 (col1, col2) VALUES ('test value', '')  
INSERT INTO Table1 (col2) VALUES ('')     

在第二份声明中，默认值用于COL1。

In the second statement, the default value is used for col1.

使用LINQ到SQL（L2S）有这样一个表时，我的问题是。我要产生相同的行为，但我无法弄清楚如何使L2S做到这一点。我希望能够运行以下code和拥有的第一行得到我指定的值，第二行从数据库中获取的默认值：

The problem I have is when using LINQ-to-SQL (L2S) with a table like this. I want to produce the same behavior, but I can't figure out how to make L2S do that. I want to be able to run the following code and have the first row get the value I specify and the second row get the default value from the database:

var context = new DataClasses1DataContext();
var row1 = new Table1 { col1 = "test value", col2 = "" };
context.Table1s.InsertOnSubmit(row1);
context.SubmitChanges();
var row2 = new Table1 { col2 = "" };
context.Table1s.InsertOnSubmit(row2);
context.SubmitChanges();

如果COL1的自动生成的值属性为False，第一行是创建为需要，但第二排失败，在col1空的错误。如果自动生成的值是true，这两个行从数据库的默认值创建的。我试过的自动生成的值，自动同步和可空各种组合，但没有我试过给我想要的行为。

If the Auto Generated Value property of col1 is False, the first row is created as desired, but the second row fails with a null error on col1. If Auto Generated Value is True, both rows are created with the default value from the database. I've tried various combinations of Auto Generated Value, Auto-Sync and Nullable, but nothing I've tried gives the behavior I want.

L2S从插入语句未指定值时，不会省略列。相反，它确实是这样的：

L2S does not omit the column from the insert statement when no value is specified. Instead it does something like this:

INSERT INTO Table1 (col1, col2) VALUES (null, '')

...这当然会导致在col1空的错误。

...which of course causes a null error on col1.

有没有办法让L2S省略的INSERT语句列，如果没有给定值？或者是有一些其他的方式来获得我想要的行为？我需要在数据库级的默认值，因为不是所有的行插入经由L2S完成的，并且在某些情况下，默认值是稍微比硬$ C $光盘值更复杂（例如，在创建基于另一字段缺省值），以便我宁愿避免重复的逻辑。

Is there some way to get L2S to omit a column from the insert statement if no value is given? Or is there some other way to get the behavior I want? I need the default value at the database level because not all row inserts are done via L2S, and in some cases the default value is a little more complex than a hard coded value (e.g. creating the default based on another field) so I'd rather avoid duplicating that logic.

推荐答案

不幸的是，LINQ to SQL的不支持数据库的默认值。看到这里的第一个问题：

Unfortunately, Linq to SQL does not support database default values. See the first question here:

http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/3ae5e457-099e-4d13-9a8b-df3ed4ba0bab/

他们是怎么建议的是，您提供的插入方法有关实体的实现。作为签名

What they're suggesting is that you provide an implementation of the Insert method for the entity in question. The signature being

partial void Insert[EntityName](Entity instance)

所以，如果你有被称为人的实体，你想拥有家的默认值一PhoneNumberDesc场，你可以实现这种方式：

So if you had an entity called Person, that you wanted to have a default value of "Home" for a PhoneNumberDesc field, you could implement it this way:

    partial void InsertPerson(Person instance)
    {
        if (string.IsNullOrEmpty(instance.PhoneNumberDesc))
            instance.PhoneNumberDesc = "Home";

        var insertParams = new List<object>();
        var insertStatement = "insert into ...";

        // build the rest of the insert statement and fill in the parameter values here...

        this.ExecuteQuery(typeof(Person), insertStatement, insertParams.ToArray());
    }

您也许能逃脱实施OnCreated事件，这就是所谓的与每个构造为每个实体。本文介绍了一点关于如何扩展点在LINQ to SQL：http://msdn.microsoft.com/en-us/library/bb882671.aspx

You might be able to get away with implementing the OnCreated event, which is called with each constructor for each entity. This article explains a little on how the extensibility points in Linq To SQL: http://msdn.microsoft.com/en-us/library/bb882671.aspx

总而言之，该解决方案真的还挺很烂。祝你好运！

All in all, the solution really kinda sucks. Good luck!