System.DateTime can take a wider range of values than SQL Server's DateTime. Hence there is class System.Data.SqlTypes.SqlDateTime which mimics the later.
Consequently I would have expected Entity Framework to choose SqlDateTime, but it didn't.
So my questions are...
What are the best practices to insure that your DateTime values will not cause problems when you try to save them to your database?
Is there any way of forcing EF to use SqlDateTime?
There's a number of things you can do:
if you're using SQL Server 2008 or newer, you can use the DATE or DATETIME2 data types on the database which offer the same date range as .NET's DateTime
if you can't use those new data types, it will be up to you to handle some checking / validation on your date fields before things are being stored into the persistent store. EF EntityObject offers lots of ways to tap into the process of validating and saving objects - pick one approach that works for you
Maybe this is an old thread but I will post my findings on this for the others :
Let say that we have dev env : EF 5, CodeFirst, SqlCE 4.0 :
public abstract class Entity : IEntity, IEquatable<Entity>
{
public virtual int Id { get; protected set; }
public virtual DateTime LastModified { get; set; }
[DataType(DataType.Date)]
public virtual DateTime CreatedOn { get; set; }
[DataType(DataType.DateTime)]
public virtual DateTime CreatedOn2 { get; set; }
[DataType(DataType.Time)]
public virtual DateTime CreatedOn3 { get; set; }
public virtual DateTime CreatedOn4 { get; set; }
}
with such a custom mapping :
public EntityMapping()
{
HasKey(e => e.Id);
Property(e => e.Id);
Property(e => e.LastModified).IsRequired().IsConcurrencyToken();
Property(e => e.CreatedOn).IsRequired();
Property(e => e.CreatedOn2).IsRequired();
Property(e => e.CreatedOn3).IsRequired();
Property(e => e.CreatedOn4).IsRequired();
}
This produces this, which means that we will have the overflow exception.
Changing the mappings to this while still working with SQL CE 4.0 :
Property(e => e.CreatedOn).IsRequired().HasColumnType("datetime2");
Property(e => e.CreatedOn2).IsRequired().HasColumnType("date");
Property(e => e.CreatedOn3).IsRequired().HasColumnType("date");
Property(e => e.CreatedOn4).IsRequired().HasColumnType("datetime2");
Gives this error. Switching to SQL Server Standart 2012 seems to solve the problem ( That is not a solution for sure - just for the experiment ). The created SQL Server schema is this.
I am not an expert in Sql but it looks like to me that SQL CE does not support these dates. the problem with development env. remains. DateTime can be substituted but can bring a lot of refactoring here and tehere.
Remember also that SqlDateTime and DateTime are very different.
The solution that I find good - for the code and for the project lifecycle - is to switch between LocalDb and SQL standart as suggested by one of the links above from stackoverflow combined with custom fluentApi mapping settings to equalize model creation or both.
Introducing custom convention in EF as a safety net looks good too.
If anybody has a better all-round solution, for code and for dev-production both, post it.
