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.

Duplicate: stackoverflow.com/q/201706/11683 (not voting to close, both are equally useful). — GSerg
– GSerg, Commented Nov 6, 2016 at 19:14

jao · Accepted Answer · 2009-07-20 16:45:00Z

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.

answered Jul 20, 2009 at 16:45

jao

18.7k16 gold badges68 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nicholas Head Over a year ago

Does my solution not fix the problem?

jao Over a year ago

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

Slider345 Over a year ago

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.

iraSenthil · Accepted Answer · 2011-02-16 16:36:03Z

11

Set Auto Generated property to True.

enter image description here

answered Feb 16, 2011 at 16:36

iraSenthil

11.6k6 gold badges44 silver badges50 bronze badges

1 Comment

GSerg Over a year ago

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.

Nicholas Head · Accepted Answer · 2009-07-18 04:03:27Z

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.

answered Jul 18, 2009 at 4:03

Nicholas Head

3,7265 gold badges30 silver badges39 bronze badges

3 Comments

GONeale Over a year ago

See intrepidstudios.com/blog/2009/2/6/… for more information. This is the definitive approach.

GSerg Over a year ago

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.

Nicholas Head Over a year ago

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.

Frank Schwieterman · Accepted Answer · 2009-07-13 17:22:42Z

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.

answered Jul 13, 2009 at 17:22

Frank Schwieterman

24.5k16 gold badges96 silver badges132 bronze badges

3 Comments

jao Over a year ago

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"

Frank Schwieterman Over a year ago

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.

jao Over a year ago

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"

Jasmin Chauhan · Accepted Answer · 2012-11-22 07:15:11Z

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.

answered Nov 22, 2012 at 7:15

Jasmin Chauhan

2993 silver badges2 bronze badges

Comments

Simon_Weaver · Accepted Answer · 2014-10-29 03:25:53Z

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.

answered Oct 29, 2014 at 3:25

Simon_Weaver

147k92 gold badges689 silver badges724 bronze badges

Collectives™ on Stack Overflow

Linq-to-SQL ignores SQL Server default value

6 Answers 6

3 Comments

1 Comment

3 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

1 Comment

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related