Out-of-range Datetime issue using LINQtoSQL

Here is a snippet of my model:

* SQL Server *

Event
-----
Id (int, PK) NOT NULL
Title (varchar(100)) NULL
LastModified (datetime) NULL

EventDates
----------
Id (int, PK) NOT NULL
StartDate (datetime) NULL
EndDate (datetime) NULL

* C# *

Event
-----
public int Id
public string Title
public DateTime LastModified

EventDates
----------
public int Id
public DateTime StartDate
public Datetime EndDate

The LastModified field has been in the database since its creation. I have been saving it's value when I save an event, but I want to display it in a table, so I changed up my Event repository's GetEvents's return value:

return (from e in GetDbEvents()
                select new Event
                {
                    // Miscellaneous fields..
                    LastModified = e.LastModified.GetValueOrDefault() // Shiny new code
                });

When I call this now, I get yelled at:

The conversion of a char data type to a datetime data type 
resulted in an out-of-range datetime value.

If I strip down the above code to this, it still doesn't help and I get the same error if I attempt to enumerate over the result:

var test = (from e in _db.Events
                    select e.LastModified.GetValueOrDefault());

As a test, I did the same statement for my EventDates table (again, with 2 datetime columns):

 var test4 = (from ed in _db.EventDates
                     select new EventDate
                     {
                         StartDate = ed.StartDate.GetValueOrDefault(),
                         EndDate = ed.EndDate.GetValueOrDefaul开发者_如何学运维t()
                     });

This works fine, of course. No errors when I enumerate, all values are correct.

I should point out that some LastModified values in the Events table are NULL while all values in EventDates are populated with data.

Edit

My main question is why does Events give me out-of-range issues and EventDates does not, even though the model is quite similar?

The problem is with the GetValueOrDefault(). This will return DateTime.MinValue (01-01-0001) in the "default" case and sqlserver doesn't accept that (it refuses dates before about 1753).

If you use a Nullable<DateTime>, then sql will be happy with the null it will get.

If you change the declaration of your C# variable LastModified to public DateTime? LastModified that should fix your problem. The addition of the question mark indicates that it is a nullable type.

Perhaps this is related?

One possible fix (if you don't want to change LastModified to DateTime?, requiring you to litter your code with .Value everywhere..) would be to get the values from the Database as DateTime?'s, but translate them to DateTime's in your code. For example:

return from e in GetDbEvents()
       select new Event(e.LastModified);

...

//In Event class:
public Event(DateTime? lastModified)
{
    LastModified = lastModified.GetValueOrDefault();
}

This will cause GetValueOrDefault() to be called client-side, rather than being part of the SQL.

Note that this approach does have problems of its own...

To expand on Hans Kesting's answer, and help people who encounter this issue in a filtering/WHERE clause rather than in the SELECT clause:

The problem is with the GetValueOrDefault() call. LINQ to SQL (somewhat stupidly IMO) translates this to real SQL by using a COALESCE clause (similar to ISNULL) like this:

COALESCE(LastModified, '1/1/0001 12:00:00 AM')

Unfortunately, the SQL Server datetime data type doesn't accept dates before about 1753. So SQL Server throws the error back to LINQ to SQL, which throws it back to you.

If you use a Nullable, then SQL Server will be happy with the null it will get. But if you still want to use GetValueOrDefault(), e.g. in a query to filter the results you have a couple options:

1. LastModified.GetValueOrDefault(System.Data.SqlTypes.SqlDateTime.MinValue.Value)
2. LastModified.HasValue && LastModified > DateTime.Now