25

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.

jao
  • 18,273
  • 15
  • 63
  • 96
  • 1
    Duplicate: http://stackoverflow.com/q/201706/11683 (not voting to close, both are equally useful). – GSerg Nov 06 '16 at 19:14

6 Answers6

11

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.

jao
  • 18,273
  • 15
  • 63
  • 96
  • Does my solution not fix the problem? – Nicholas Head Jul 20 '09 at 22:49
  • 1
    your solution works on insert, but when I update the record I get an error about some member generated in the database cannot be changed – jao Jul 21 '09 at 06:13
  • 3
    This works best for me because when I modify a table and regenerate it on the linq-to-sql dbml file, I lose all the custom properties I set on the fields in the table, leading to obscure errors in my website. Creating a partial OnCreated method in a separate class file avoids this. – Slider345 Jun 06 '12 at 17:04
11

Set Auto Generated property to True.

enter image description here

iraSenthil
  • 11,307
  • 6
  • 39
  • 49
  • 2
    This doesn't work. It works if you always want to use the default value, but it will not let you use a non-default value when inserting an object. Linq2Sql will ignore the value you supplied and read back the default. – GSerg Nov 06 '16 at 18:52
7

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

Nicholas Head
  • 3,716
  • 5
  • 27
  • 39
  • See http://www.intrepidstudios.com/blog/2009/2/6/using-default-values-in-linq-to-sql.aspx for more information. This is the definitive approach. – GONeale Aug 24 '11 at 01:23
  • 2
    This doesn't work. It will not let you omit the value when inserting an object. If you do, it will try to insert `null` as the column value, when the correct action would be to not list any value at all in the query. So when the column is non-nullable, you get the "cannot insert null into a non-nullable column" error. – GSerg Nov 06 '16 at 18:54
  • Fair enough. This whole thing doesn't have a clean solution it looks like (even the accepted answer is kinda a hack). L2S just doesn't handle this scenario properly. – Nicholas Head Nov 14 '16 at 19:19
4

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.

Frank Schwieterman
  • 24,142
  • 15
  • 92
  • 130
  • 1
    This is not working, as I need to update the column afterwards. When I try your solution I get an error: "A member calculated or generated in the database can not be changed" – jao Jul 13 '09 at 18:38
  • Thats too bad. I never tried it with a field I wanted to change later (I was using it for a DateCreated column). Even in that case I decided not to use a SQL default value and set it explicitly in my code- the reasoning being its part of my domain logic so it belongs with the rest of my domain logic code. – Frank Schwieterman Jul 14 '09 at 16:45
  • I think the error I get is a bug in Linq-to-sql. The tooltip of 'auto generated value' says "Value is autogenerated on insert" – jao Jul 20 '09 at 16:33
1

I was faced the same problem of boolean data type with default as false and not updating that value So when i see the database table, i was found there is not primary key was set, therefore when i was set the primary key it was updated find. so check a table if it has the primary key or not.

Jasmin Chauhan
  • 289
  • 3
  • 2
1

If you ever need to change the value in your own code (eg. a type table value with a default) then don't use 'Auto Generated Value' or you'll get this later when you try updating it.

Value of member 'AlternativeQuoteStatus' of an object of type 'OrderDetail' changed. A member that is computed or generated by the database cannot be changed.

I prefer to set such values when I create the object, with a fallback in OnCreated as described by others here.

Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689