Allow Entity Framework 4.5 to use datetime2 with SQL Server CE4

Question

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.

score 3 · Answer 1 · answered Oct 04 '12 at 15:51

3

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

answered Oct 04 '12 at 15:51

Erik Funkenbusch

92,674
28
195
291

Good thought. As Entity Framework is doing all the conversations with the database it should probably be fairly compatible. Can one talk with LocalDB without having gone up to the latest version of .net/Visual Studio? – glenatron Oct 04 '12 at 16:24
@glenatron - yes, all it needs is the proper client installed. – Erik Funkenbusch Oct 04 '12 at 16:42
1

.NET 4.0.3 is required, not 4.5 – ErikEJ Oct 04 '12 at 17:26

score 1 · Accepted Answer · answered Oct 10 '12 at 12:15

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.

Allow Entity Framework 4.5 to use datetime2 with SQL Server CE4

2 Answers2

Linked