How do I ensure Linq to Sql doesn't override or violate non-nullable DB default values?

Question

I have an SQL Server DB with a table with these fields:

1. A bit with the default value 1, NOT NULL.
2. A smalldatetime with the default value gettime(), NOT NULL.
3. An int with no default value, IDENTITY, NOT NULL.

When I generate Linq to SQL for this table, the following happens:

1. The bit is given no special treatment.
2. The smalldatetime is given no special treatment.
3. The int is marked as IsDbGenerated.

This means that when I make inserts using Linq to SQL, the following will happen:

1. The bit will be sent as 0, overriding the default value. Right?
2. The smalldatetime will be sent as an uninitialized System.DateTime, producing an error in SQL server since it doesn't fall with the SQL Server smalldatetime range. Right?
3. The IsDbGenerated int will not be sent; the DB will generate a value which Linq to SQL will then read back.

What changes do I have to make to make this scenario work?

To summarize: I want non-nullable fields with DB-assigned default values, but I don't want them IsDbGenerated if it means I cannot provide values for them when making updates or inserts using Linq to SQL. I also do not want them IsDbGenerated if it means I have to hand-modify the code generated by Linq to SQL.

EDIT: The answer seems to be this is a limitation in the current Linq to SQL.

Answer 1

This means that when I make inserts using Linq to SQL, the following will happen:

1. The bit will be sent as 0, overriding the default value. Right? - Correct
2. The smalldatetime will be sent as an uninitialized System.DateTime, producing an error in SQL server since it doesn't fall with the SQL Server smalldatetime range. Right? - What is sent is DateTime.MinValue
3. The IsDbGenerated int will not be sent; the DB will generate a value which Linq to SQL will then read back. - If DB generated is set then the value is created by the database, if not then Linq expects the user to set the value.

Your best bet is to set them in the constructor of the object, or on the private fields, if you are not using automatic properties.

Answer 2

You can create another file for your datacontext (partial class) and then use the InsertYOURENTITY and UpdateYOURENTITY partial methods to inspect your properties and assign the proper values. Call ExecuteDynamicInsert or ExecuteDynamicUpdate after your code and you're set.

Answer 3

Linq-To-Sql generated classes do not pick up the Default Value Constriants.

Maybe in the future, but the issue is constraints aren't always simple values, they can also be scalar functions like GetDate(), so linq would somehow have to know how to translate those. In short, it doesn't even try. It's also a very database-specific type of thing.

• You could write a code generator to create entity partial classes where you can extract the default value constriant.
• Alternatively your business layer code could set the defaults in constructors from an xml file, and all you need to do is keep the xml file up-to-date.
• Instead of doing the work in constructors, you could emulate sql and add default values by inspecting the ChangeSet before submitting changes to the database.

The issue you are having is described at length in CodeProject - Setting Default Values for LINQ Bound Data

Answer 4

I've run into the same problem, bzlm, and come to the same conclusion. There's simply no good way to get non-nullable fields with DB-assigned default values working with Linq To Sql.

The work around I've gone with is to add a SetDefaults() method very similar to the one Robert Paulson linked to on CodeProject and call it in the default constructor of my table entity base class. It works well for me, because 95% of the time, I'm setting a 0, empty string, or getdate().