Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?
- by Todd Ropog
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.
