none
LINQ Entity Framework 4.2 datetime equality comparison failing with First() RRS feed

  • Question

  • Following describes an issue I am running into using EF 4.2 and LINQ. I've tried this on multiple systems, and multiple flavors of SQL (SQL Express and SQL 2008 R2 Sp1), VS 2010 Pro and Premium, both in 64 bit and 32 binaries, both with the debugger, and attempting to run the binary directly. I'm wondering what I've stumbled against, if it's an issue with EF, or more likely an issue with my code and I'm missing some nuance either related to LINQ or EF. Any assistance would be greatly appreciated, been at it for 6 hours, my Google JuJu is not strong enough I'm afraid.

    I have a simple model named session consisting of an ID (Guid), Name (String), and Timestamp (DateTime). I am able to add a session to the dbcontext, and have verified in debugging that the session record is indeed stored in the database. The timestamp used to create the session object is stored in a local variable, and immediately after storing, I attempt to retrieve the session into a new instance of a session object. The code that retrieves the session (LINQ) throws 'System.InvalidOperationException "Sequence contains no elements"' with the following stacktrace:

       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__0[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
       at System.Linq.Queryable.First[TSource](IQueryable`1 source)
       at efwtf.Program.Main(String[] args) in d:\vs2010\efwtf\efwtf\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

    I am able to drill into ctx.Sessions in the debugger and see in the resultset that the session that I am trying to retrieve actually does exist, and if I iterate over the result set and manually compare the two timestamps I can extract the session as expected. If I run the code and pass a timestamp value for a record that was added to the database in a previous run, it works. I can find work arounds, worst case, changing the datatype to a string perhaps, but it bugs me that this doesn't work, I'm assuming, which may be part of the problem, that the functionality should be supported. Code follows.

            using System;
            using System.Collections.Generic;
            using System.Linq;
            using System.Text;
            using System.Data.Entity;
            using System.ComponentModel.DataAnnotations;
            
            namespace efwtf
            {
               class Program
               {
                  static void Main ( string[] args )
                  {
                     Context ctx = new Context ();
                     DateTime ts = DateTime.UtcNow;
                     Session s1 = new Session () { Id = Guid.NewGuid(), Name = "Testing", TimeStamp = ts };
                     ctx.Sessions.Add ( s1 );
                     ctx.SaveChanges ();
    
    //    Works
    //                 Session s2 = ( from s in ctx.Sessions
    //                                where ( s.Name == "Testing" )
    //                                select s ).First ();
    
    //    Works 
    //                 Session s2 = null;
    //                 foreach (Session s in ctx.Sessions)
    //                 {
    //                    if (s.TimeStamp == ts)
    //                       s2 = s;
    //                 }
    
    //    FAIL
                     Session s2 = ( from s in ctx.Sessions
                                    where ( s.Name == "Testing" && s.TimeStamp.Equals ( ts ) )
                                    select s ).First ();
    
    // Failed Variations, issue seems to lie with DateTime value
    
    //                 Session s2 = ( from s in ctx.Sessions
    //                                where ( s.TimeStamp == ts )
    //                                select s ).First ();
    
    //                 Session s2 = ( from s in ctx.Sessions
    //                                where ( s.TimeStamp.Equals ( ts ) )
    //                                select s ).First ();
    
    //                 Session s2 = ( from s in ctx.Sessions
    //                                where ( DateTime.Compare(s.TimeStamp, ts ) == 0 )
    //                                select s ).First ();
    
    //                 Session s2 = ctx.Sessions.First ( s => s.TimeStamp == ts );
            
                     Console.WriteLine ( s2.Name, s2.TimeStamp );
                  }
               }
            
               class Context : DbContext
               {
                  public DbSet<Session> Sessions { get; set; }
               }
            
               class Session
               {
                  [Key]
                  public Guid Id { get; set; }
                  public string Name { get; set; }
                  public DateTime TimeStamp { get; set; }
               }
            }
            
    

    Any help would be much appreciated, thanks in advance.

    Thursday, February 9, 2012 5:12 AM

Answers

  • DateTime values are not stored in SQL Server with exact accuracy, so equals will always  fail, and no records will be returned.

    Rounded to increments of .000, .003, or .007 seconds - http://msdn.microsoft.com/en-us/library/ms187819.aspx


    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    • Marked as answer by LloydT Thursday, February 9, 2012 12:48 PM
    • Unmarked as answer by LloydT Thursday, February 9, 2012 2:20 PM
    • Marked as answer by LloydT Thursday, February 9, 2012 2:21 PM
    • Unmarked as answer by LloydT Thursday, February 9, 2012 3:03 PM
    • Marked as answer by Alan_chenModerator Friday, March 16, 2012 9:24 AM
    Thursday, February 9, 2012 8:13 AM
  • Hi LloydT;

    In order to have SQL server return a DateTime value that can be compared in your program you should set the data type of the DateTime in the database as datetime2. This can be done by setting the Column attribute with a TypeName = "datetime2") as shown in the code snippet below, then all should work well.

    class Session
    {
        [Key]
        public Guid Id { get; set; }
        public string Name { get; set; }
        [Column(TypeName = "datetime2")]
        public DateTime TimeStamp { get; set; }
    }


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, February 9, 2012 7:13 PM
  • Hi LloydT;

    It is all about how the SQL server stores its data. A datetime data type is stored in the SQL serve as 8 bytes and with an accuracy of 0.00333 second while a datetime2 data type is stored in the SQL serve as 6 to 8 bytes default being 7 bytes and a accuracy of 100 nanoseconds. According to Microsoft documentation CLR/.Net DateTime data type to SQL data type of datetime can cause data overflow / data loss to and from the database where CLR/.Net DateTime data type to SQL data type of datetime2 should go to and from the database without data loss.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, February 9, 2012 8:33 PM

All replies

  • DateTime values are not stored in SQL Server with exact accuracy, so equals will always  fail, and no records will be returned.

    Rounded to increments of .000, .003, or .007 seconds - http://msdn.microsoft.com/en-us/library/ms187819.aspx


    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    • Marked as answer by LloydT Thursday, February 9, 2012 12:48 PM
    • Unmarked as answer by LloydT Thursday, February 9, 2012 2:20 PM
    • Marked as answer by LloydT Thursday, February 9, 2012 2:21 PM
    • Unmarked as answer by LloydT Thursday, February 9, 2012 3:03 PM
    • Marked as answer by Alan_chenModerator Friday, March 16, 2012 9:24 AM
    Thursday, February 9, 2012 8:13 AM
  • Actually equality works intermittently, and this explains that as well.  So if I'm understanding correctly, changing the column type to DateTime2 will solve the problem?  Doesn't seem to work though when I do that, maybe I'm missing something.  While changing the datatype to something else is a possible solution, doesn't that kind of remove some of the simplicity of EF and Linq to Entities? 

    Also, if I view both objects in the debugger, one in the set, and the date time being passed in, and compare the internal dateData, they are the same values, confused as to why equality would fail if this is the case.

       Session s2 = ( from s in ctx.Sessions
                      select s ).First ();
       var test1 = ( sess.TimeStamp == pTimeStamp )
    When starting with a blank database so only one record is returned, test1 is set to true.  Doesn't that imply it should work in the Linq / Lambda expressions?
                    
    • Edited by LloydT Thursday, February 9, 2012 3:00 PM
    Thursday, February 9, 2012 2:32 PM
  • Hi LloydT;

    In order to have SQL server return a DateTime value that can be compared in your program you should set the data type of the DateTime in the database as datetime2. This can be done by setting the Column attribute with a TypeName = "datetime2") as shown in the code snippet below, then all should work well.

    class Session
    {
        [Key]
        public Guid Id { get; set; }
        public string Name { get; set; }
        [Column(TypeName = "datetime2")]
        public DateTime TimeStamp { get; set; }
    }


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, February 9, 2012 7:13 PM
  • Thanks Fernando, I've done that already actually and it did indeed fix my issue.  I'm still curious though as to why this works, while the data type works, the issue seems to lie outside of SQL, especially in light of the stack trace mentioned previously, and is there something else at play.

      Session s2 = ( from s in ctx.Sessions
                      select s ).First ();
       var test1 = ( sess.TimeStamp == pTimeStamp )

    Thursday, February 9, 2012 7:37 PM
  • Hi LloydT;

    It is all about how the SQL server stores its data. A datetime data type is stored in the SQL serve as 8 bytes and with an accuracy of 0.00333 second while a datetime2 data type is stored in the SQL serve as 6 to 8 bytes default being 7 bytes and a accuracy of 100 nanoseconds. According to Microsoft documentation CLR/.Net DateTime data type to SQL data type of datetime can cause data overflow / data loss to and from the database where CLR/.Net DateTime data type to SQL data type of datetime2 should go to and from the database without data loss.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, February 9, 2012 8:33 PM
  • Hi Fernando:

    Not arguing that changing the data type will address the issue, and thanks again to both you and ErikEJ for the guidance, but why does the following work

      Session s2 = ( from s in ctx.Sessions
                      select s ).First ();
       var test1 = ( sess.TimeStamp == pTimeStamp )

    (test1 always evaluates to true)

    and the following fail

      Session s2 = ( from s in ctx.Sessions
                     where s.TimeStamp == pTimeStamp
                      select s ).First ();
    

    in testing both in the same manner, i.e. starts with blank db, one record is created and the ctx.Sessions DBSet only contains one Session object.  It just seems to me that while changing the datatype works, it is not the true issue, if equality works in code one way but not the other, and further the stacktrace indicates indicates EF and ELinq (below), would that not imply that the true problem exists elsewhere?

       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__0[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
       at System.Linq.Queryable.First[TSource](IQueryable`1 source)
       at efwtf.Program.Main(String[] args) in d:\vs2010\efwtf\efwtf\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

    Thursday, February 9, 2012 8:52 PM
  • Hi Lloy,

    This the difference between IQueryable and IEnumerable. You can refer this link: http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/e8ba8f61-9cc1-482c-a47c-3417dc71c878 

    you can part Year, Month and Day: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/f8bdce94-2673-4ec1-b63e-0a02c3192fba 

    Here is EntityFunctions methods: http://msdn.microsoft.com/en-us/library/dd412643.aspx 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 16, 2012 9:24 AM
    Moderator