Out-of-range Datetime issue using LINQtoSQL

Question

This is really confusing me. 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.GetValueOrDefault()
                     });

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.

Did I miss something basic? Thanks in advance!

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?

Answer 1

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.

Answer 2

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

Answer 3

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.