Entity-framework – Entity Framework 4 maps DateTimeOffset to SQL datetime in Visual Studio 2010

datetimeoffsetentity-frameworkvisual studio 2010

I'm using Visual Studio 2010 RTM with .NET/Entity Framework 4 RTM with a model driven design approach.
When I create an entity with a DateTimeOffset field the EF modeler tries to map the DateTimeOffset to a SQL datetime instead of a SQL datetimeoffset.
I'm using SQL Server 2008 Express so the datetimeoffset is supported in the database.

Visual Studio comes up with this error:

Error 2019: Member Mapping specified is not valid. The type 'Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]' of member 'Created' in type 'Data.SqlStorage.MyType' is not compatible with 'SqlServer.datetime[Nullable=False,DefaultValue=,Precision=3]' of member 'Created' in type 'Data.SqlStorage.Store.MyTypes

If I edit the type directly in the EDMX StorageModels xml section I get the following error:

Error 40: The Type datetimeoffset is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.

Why doesn't the modeler just correctly map this to a SQL datetimeoffset type?
This problem also occured when I was still working with the beta versions of Visual Studio 2010 & .NET framework 4.

Best Answer

In the RTM release, you can do something like this in your DbContext:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) {
     modelBuilder.Entity<EntityName>().Property(p => p.PropertyName).HasColumnType("datetimeoffset");
 }

This is also useful for telling Entity Framework Code First to use datetime2 instead of datetime when generating your database.

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – Error message ‘Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.’

This error has no true magic bullet answer. The key is to have all the information to understand the problem. Most likely a dynamically loaded assembly is missing a referenced assembly. That assembly needs to be in the bin directory of your application.

Use this code to determine what is missing.

using System.IO;
using System.Reflection;
using System.Text;

try
{
    //The code that causes the error goes here.
}
catch (ReflectionTypeLoadException ex)
{
    StringBuilder sb = new StringBuilder();
    foreach (Exception exSub in ex.LoaderExceptions)
    {
        sb.AppendLine(exSub.Message);
        FileNotFoundException exFileNotFound = exSub as FileNotFoundException;
        if (exFileNotFound != null)
        {                
            if(!string.IsNullOrEmpty(exFileNotFound.FusionLog))
            {
                sb.AppendLine("Fusion Log:");
                sb.AppendLine(exFileNotFound.FusionLog);
            }
        }
        sb.AppendLine();
    }
    string errorMessage = sb.ToString();
    //Display or log the error based on your application.
}

Related Topic