Failure to set the date property of an object results in an error

Question

I have a table with a smalldatetime NOT NULL field with the default getdate() value. When creating a new record in the table, I do not set the smalldatetime field to the code via LINQ To SQL syntax, I just let the database set the default value, which is the current date. If I don’t specify it explicitly in the code, it gives the following error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

If I set the default value in the database, why should I set it in the code? I noticed funky dates when it comes to LINQ To SQL.

Answer 1

Instead of setting the field as IsDbGenerated , you might want to set its AutoSync value to OnInsert . IsDbGenerated will not allow you to set the field value ever (this may be what you want for the "created date" field, but not for the "last modified" field).

However, if you use ORM, I would ask you to consider whether you need application logic in both the database and the application code. Does it make sense to implement the default value in the code (via partial methods , for example Insert[Entity] )?

Answer 2

You must set the generated property so that LINQ to SQL does not send a default value for creation.

The property is called the "Auto Generated Value" entity.

Answer 3

LINQ To SQL does not respect the default values ​​for the database so that you can subsequently update the value. To resolve this, you need to set default values ​​in your code.

When creating new NOT NULL objects with a default database, C # will use default values, such as MinValue for numbers and dates, empty GUIDs (zeros), etc. You can search for these conditions and replace them with your own default value.

This is a known design issue with LINQ To SQL. For a detailed discussion, see this link:

http://www.codeproject.com/KB/linq/SettingDefaultValues.aspx

Sample code for setting default values ​​in an application:

 private void SetDefaults(object LinqObj) { // get the properties of the LINQ Object PropertyInfo[] props = LinqObj.GetType().GetProperties(); // iterate through each property of the class foreach (PropertyInfo prop in props) { // attempt to discover any metadata relating to underlying data columns try { // get any column attributes created by the Linq designer object[] customAttributes = prop.GetCustomAttributes (typeof(System.Data.Linq.Mapping.ColumnAttribute), false); // if the property has an attribute letting us know that // the underlying column data cannot be null if (((System.Data.Linq.Mapping.ColumnAttribute) (customAttributes[0])).DbType.ToLower().IndexOf("not null") != -1) { // if the current property is null or Linq has set a date time // to its default '01/01/0001 00:00:00' if (prop.GetValue(LinqObj, null) == null || prop.GetValue(LinqObj, null).ToString() == (new DateTime(1, 1, 1, 0, 0, 0)).ToString()) { // set the default values here : could re-query the database, // but would be expensive so just use defaults coded here switch (prop.PropertyType.ToString()) { // System.String / NVarchar case "System.String": prop.SetValue(LinqObj, String.Empty, null); break; case "System.Int32": case "System.Int64": case "System.Int16": prop.SetValue(LinqObj, 0, null); break; case "System.DateTime": prop.SetValue(LinqObj, DateTime.Now, null); break; } } } } catch { // could do something here ... } } 

Answer 4

To get around this, make sure your LINQ To SQL model knows that your smalldatetime field smalldatetime automatically created by the database.

Select the table field in the LINQ To SQL chart and find the Properties window. Set the Auto Generated Value property to True . This ensures that the field is NOT included in the INSERT generated by LINQ To SQL.

Alternatively, you will need to specify this yourself:

 if (newCustomer.DateTimeCreated == null) { newCustomer.DateTimeCreated = DateTime.Now; // or UtcNow }