not setting date property to object results in error

Question

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 current 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.

Similar question: http://stackoverflow.com/questions/201706/how-do-i-ensure-linq-to-sql-doesnt-override-or-violate-non-nullable-db-default-v — bdukes, Nov 29 '10 at 16:34

score 3 · Accepted Answer · answered Nov 29 '10 at 16:32

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])?

Thanks for the tip on the ISDbGenerated property. – Xaisoft Nov 29 '10 at 16:46 — Xaisoft, Nov 29 '10 at 16:46

John Gietzen · Answer 2 · 2010-11-29T16:32:01.707

1

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.

alt text

edited Nov 29 '10 at 16:32

answered Nov 29 '10 at 16:26

John Gietzen

48,783
32
145
190

Ok, I will give that a shot and let you know. Out of curiousity, what is the default that LINQ to SQL sends along? – Xaisoft Nov 29 '10 at 16:28
It uses whatever .NET thinks the default for DateTime is. In practice, I believe that it sends `January 1 0000`. – John Gietzen Nov 29 '10 at 16:29
ok, you would think they would at least be kind enough to send the current date, lol. – Xaisoft Nov 29 '10 at 16:31

D'Arcy Rittich · Answer 3 · 2010-11-29T16:43:16.003

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 ...
        }
    }

Is it that it does observe it, you just have to tell it to observe it? — Xaisoft, Nov 29 '10 at 16:30
John's answer is partially correct. You can do that where you are never going to update the field as bdukes indicated. — D'Arcy Rittich, Nov 29 '10 at 16:38

score 0 · Answer 4 · answered Nov 29 '10 at 16:32

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.

alt text

Alternately, you'd have to specify this yourself:

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

not setting date property to object results in error

4 Answers4