I am working on an Entity Framework project using SQL Server 2008. We recently changed to use the datetime2 field type for a lot of our Dates as we need the precision.
This works fine against our live and development databases, but as part of our end-to-end tests we have been using SQL Server CE 4.0, which doesn't support the datetime2 type. The moment Entity Framework tries to construct the database it returns a series of exceptions like this:
error 0040: The Type datetime2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.
Obviously, there is no value in changing our production code for test purposes, so is there a way to tell it to convert the datetime2 values to a regular datetime or converting them to a varchar?
The purpose of the test is to ensure that everything from the data layer up to the interface is working as expected, so if there is a better way to implement this kind of test that might provide a useful alternative.
You may be better off using SQL Server 2012 LocalDB rather than CE. I realize that using SQL Server 2012 may introduce potential compatibility issues (though it really shouldn't), but LocalDB is a full SQL-Server functionality file-based database. It supports datetime2
In the end I found a solution to this problem that works sufficiently for the end-to-end testing configuration I am working with. The solution I went for was to use a special DataContext to handle Sql Server CE requests, thus:
public class TestDataContext : DataContext
{
protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
{
// list of available Conventions: http://msdn.microsoft.com/en-us/library/system.data.entity.modelconfiguration.conventions(v=vs.103).aspx
// Remove the column attributes that cause the datetime2 errors, so that
// for SQL Server CE we just have regular DateTime objects rather than using
// the attribute value from the entity.
modelBuilder.Conventions.Remove<ColumnAttributeConvention>();
// Attempt to add back the String Length restrictions on the entities. I havent
// tested that this works.
modelBuilder.Configurations.Add( new ComplexTypeConfiguration<StringLengthAttributeConvention>());
// SQL Server CE is very sensitive to potential circular cascade deletion problems
modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
}
}
By replacing the regular DataContext with a TestDataContext in my test classes I have the same behaviour without SQL Server CE crashing out.
