LINQ-to-SQL 能否在插入时省略未指定的列，以便使用数据库默认值? [英] Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?

问题描述

我有一个不可为空的数据库列，它有一个默认值集.插入一行时，有时会为该列指定一个值，有时则没有.当列被省略时，这在 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-to-SQL (L2S) 时.我想产生相同的行为，但我不知道如何让 L2S 做到这一点.我希望能够运行以下代码并让第一行获取我指定的值，第二行获取数据库中的默认值:

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 的 Auto Generated Value 属性为 False，则根据需要创建第一行，但第二行失败，并在 col1 上出现空错误.如果 Auto Generated Value 为 True，则使用数据库中的默认值创建两行.我尝试了自动生成值、自动同步和 Nullable 的各种组合，但我尝试过的任何组合都没有给出我想要的行为.

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 从插入语句中省略一列?或者有没有其他方法可以获得我想要的行为?我需要数据库级别的默认值，因为并非所有行插入都是通过 L2S 完成的，并且在某些情况下，默认值比硬编码值稍微复杂一些(例如，基于另一个字段创建默认值)所以我会而是避免重复该逻辑.

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/

他们建议您为相关实体提供 Insert 方法的实现.签名是

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)

因此，如果您有一个名为 Person 的实体，并且希望 PhoneNumberDesc 字段的默认值为Home"，则可以通过以下方式实现:

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!

这篇关于LINQ-to-SQL 能否在插入时省略未指定的列，以便使用数据库默认值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！