c# - 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

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