Linq-to-SQL ignores SQL Server default value

Question

When using Linq-to-SQL, adding a column to an existing table, and setting a default value on that new column, it seems that Linq to SQL ignores the default value. Has anyone else experienced this behaviour? Is there a way to fix it, so that Linq-to-SQL or SQL Server automatically sets the default value?

The column I added was of type Bit, with a default value set to 1.

Answer 1

I've seen this. What you can do is go into the l2sql designer, view properties for the table column that has a default value. There is a property "Auto generated value", set that to true.

This same value is set to true automatically for the identity column automatically, as in that case SQL Server is generating your row IDs.

Answer 2

I've now temporarily fixed this by explicitly setting the value in my Add function, until someone posts a better answer.

public void Add(Foo foo) {
        foo.isnew = true;
        db.Foos.InsertOnSubmit(foo);
}

Answer 3

Go into the designer and select "Auto-Sync" value of "OnInsert". This will sync the value when the record is inserted into the database.

Answer 4

I have now definitively fixed this by using an example from this blog.

partial void OnCreated() {
    if (this.DateTimeCreated == null) {
           this.DateTimeCreated = DateTime.Now;
    }
}

I needed to pass this into a partial datacontext class, as the default one is automatically overwritten every time you change something in the dbml.