not setting date property to object results in error

I have a table that has a smalldatetime NOT NULL field with a default value of getdate(). When creating a new record in the table, I don't set the smalldatetime field in code through LINQ To SQL syntax, I just let the database set the default value which is the cu开发者_JS百科rrent date. If I don't explicitly set it in code, it throws 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 am setting a default in the database, why should I have to set it in code? I have noticed funky things with dates when it comes to LINQ To SQL.

Rather than setting the field as IsDbGenerated, you may want to consider setting its AutoSync value to OnInsert. IsDbGenerated won't let you set the field's value ever (which may be what you want for a "created date" field, but not for a "last modified" field).

However, if you're using an ORM, I would ask you to consider whether you want your application logic in both the database and the application code. Does it make more sense to implement the default value in code (via partial methods like Insert[Entity])?

You have to set the generated property so that LINQ to SQL doesn't send its default value along for creation.

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

To get around this, ensure that your LINQ To SQL model knows that your smalldatetime field is auto-generated by the database.

Select the table's field in your LINQ To SQL diagram, and find the Properties window. Adjust the Auto Generated Value property to True. This will ensure that the field IS NOT included in the INSERT statement generated by LINQ To SQL.

Alternately, you'd have to specify this yourself:

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

LINQ To SQL does not observe database defaults in a way that you can then subsequently update the value. In order to allow this, you need to set default values in your code.

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

This is a known design issue with LINQ To SQL. For an in-depth discussion, see this link:

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

Some example code for setting default values in your 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 ...
        }
    }