Entity Framework 5 wrong data type in query

Question

We are using EF 5.0 as our ORM of choice in our business solution, structured in a n-layer fashion with everything decoupled and a nice composition root with ninject.

Lately, we've been building a database that uses partitioning underneath, and we have some important indexes on DATE columns.

The columns are correctly declared on Sql Server 2008. We also added the correct data type in the EF mappings, with the HasColumnType("Date") instruction.

Still, when querying the table through Linq to Entities, the parameters that we filter the dates on are created of type DateTime2 and even the columns are cast to DateTime2 in the queries so the type matches the parameters.

This behaviour has several problems. First of all, if I'm telling EF engine that the column on the database is DATE why should it cast it to DateTime2?

Second, this cast is making the database ignore the indexes, thus not using partitioning. We have one year per phisical partitioning, and if I ask a date range, let's say, february 2013 to march 2013 the scan should happend only on one physical partition. It works correctly if manually using the correct data type DATE but with the cast to DateTime2 all the partitions get scanned, reducing performance drastically.

Now, I'm sure I'm missing out something, because it would be rather stupid that Microsoft ORM doesn't work well on Microsoft Sql Server.

I've been unable to find any documentation on how have EF use the correct data types in queries, so I'm asking here. Any help will be appreciated.

Thanks.

Answer 1

I don't believe that this is possible in Entity Framework. This requested enhancement would probably do what you need. This MSDN page shows the mapping between SQL Server types and CLR types. Note that date is supported and is mapped to DateTime, but since several SQL types map to the same CLR type, EF is evidently picking one SQL type as the preferred eqivalent of the CLR type.

Could you wrap your selection code in a stored procedure? If so, this would seem to be a reasonable solution. You could use DbSet{T}.SqlQuery to materialize objects from executing the sp.

Code sample

The following short console application demonstrates the concept. Note how the related entities are successfully lazy-loaded.

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApplication1
{
    [Table("MyEntity")]    
    public class MyEntity
    {
        private Collection<MyRelatedEntity> relatedEntities;

        [Key]
        public virtual int MyEntityId { get; set; }

        [DataType(DataType.Date)]
        public virtual DateTime MyDate { get; set; }

        [InverseProperty("MyEntity")]
        public virtual ICollection<MyRelatedEntity> RelatedEntities
        {
            get
            {
                if (this.relatedEntities == null)
                {
                    this.relatedEntities = new Collection<MyRelatedEntity>();
                }

                return this.relatedEntities;
            }
        }

        public override string ToString()
        {
            return string.Format("Date: {0}; Related: {1}", this.MyDate, string.Join(", ", this.RelatedEntities.Select(q => q.SomeString).ToArray()));
        }
    }

    public class MyRelatedEntity
    {
        [Key]
        public virtual int MyRelatedEntityId { get; set; }

        public virtual int MyEntityId { get; set; }

        [ForeignKey("MyEntityId")]
        public virtual MyEntity MyEntity { get; set; }

        public virtual string SomeString { get;set;}
    }

    public class MyContext : DbContext
    {
        public DbSet<MyEntity> MyEntities
        {
            get { return this.Set<MyEntity>(); }
        }
    }

    class Program
    {
        const string SqlQuery = @"DECLARE @date date; SET @date = @dateIn; SELECT * FROM MyEntity WHERE MyDate > @date";

        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());

            using (MyContext context = new MyContext())
            {
                context.MyEntities.Add(new MyEntity
                    {
                        MyDate = DateTime.Today.AddDays(-2),
                        RelatedEntities =
                        {
                            new MyRelatedEntity { SomeString = "Fish" },
                            new MyRelatedEntity { SomeString = "Haddock" }
                        }
                    });

                context.MyEntities.Add(new MyEntity
                {
                    MyDate = DateTime.Today.AddDays(1),
                    RelatedEntities =
                        {
                            new MyRelatedEntity { SomeString = "Sheep" },
                            new MyRelatedEntity { SomeString = "Cow" }
                        }
                });

                context.SaveChanges();
            }

            using (MyContext context = new MyContext())
            {
                IEnumerable<MyEntity> matches = context.MyEntities.SqlQuery(
                    SqlQuery,
                    new SqlParameter("@dateIn", DateTime.Today)).ToList();

                // The implicit ToString method call here invokes lazy-loading of the related entities.
                Console.WriteLine("Count: {0}; First: {1}.", matches.Count(), matches.First().ToString());
            }

            Console.Read();
        }
    }
}

Answer 2

The range of DateTime type in .NET and SQL server is different.

.NET DateTime range is : 0000-Jan-01 to 9999-Dec-31 SQL DateTime range is: 1900-Jan-01, 2079-Jun-06

To match the range, EF convert your .NET DateTime to SQL server DateTime2 type which has same range as .NET DateTime range.

I think your issue only happens when you have date property that is not assigned and passed to SQL server via EF. When the date is not assigned with specific value, it is default to DateTime.Min which is 0000-Jan-01 and that is causing the conversion to DateTime2.

I think you can either make your DateTime property nullable --> DateTime? or write a helper to convert your DateTime.Min to meet SQL DateTime range.

Hopefully, this helps.

Answer 3

I don't have a solution. I've never seen a LINQ-to-Entites query with .NET DateTime parameters involved that had used a parameter type in the SQL query other than datetime2(7). I doubt that you can get rid of that. Just a try to explain why it is as it is:

Suppose you have an entity with a property SomeNumber of type int. What result would you expect for a query like this:

....Where(e => e.SomeNumber >= 7.3)....

Probably all entities where SomeNumber is 8 or greater. If the (floating point decimal) parameter 7.3 would be cast to the type int stored in the database you had to decide how to round 7.3 - to 7 (will lead to wrong result) or to 8? OK, you could say, because my query says >= and I know the type in the DB is a whole number, rounding to 8 must be correct. If I would use <=, then rounding to 7 must be correct. If I would use ==, oh... I must not round at all or I know that the result must be empty and I could directly translate this Where clause to false. And != to true. But a parameter of 7.0 is a special case. Etc....

Well, the dilemma in this example has an easy solution: Decide on client side what you want by using an int parameter (7 or 8) in the first place.

The solution with DateTime is not so simple because .NET does not have a Date type. Queries with DateTime parameters will always have the form...

DateTime dateTime = new DateTime(2013, 5, 13, 10, 30, 0);
....Where(e => e.SomeDateTime >= dateTime)....

...and if SomeDateTime is stored as date in SQL Server you have again the rounding dilemma. Do I have to cast to 2013.05.13 or 2013.05.14? For the query above the client would surely expect all entities with a date of 14th and later.

Well, you could do it smart, like: if the time portion of my DateTime parameter is midnight, cast to the date portion. If I use >= cast to the next day, etc., etc.... Or you could always cast to datetime2(7). Then the result of the query is always correct and as the (.NET) client expects it. Correct... but perhaps with suboptimal index usage.