Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?

Question

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 ('')     

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

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();

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 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, '')

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

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.

Answer 1

I'm not sure about LINQ, but in SQL when you want a default do it either way:

INSERT INTO Table1 (col2) VALUES ('X')  

or

INSERT INTO Table1 (col1, col2) VALUES (DEFAULT, 'X')  

Answer 2

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)

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());
    }

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!