CODEWITHSUNDEEP
linqdatetimeframeworksentityequality
Grogh
Grogh

Reputation: 349

LINQ Entity Framework 4.2 datetime comparison fails

Followind 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 ();

             Session s2 = ( from s in ctx.Sessions
                            where ( s.Name == "Testing" && s.TimeStamp.Equals ( ts ) )
                            select s ).First ();

             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; }
       }
    }

I've tried variations on the LINQ statement, including s.datetime.equals(ts) and datetime.compare(s.datetime, ts) == 0 but every time it throws the same exception.

Anyone have any ideas? Is it me? Does this code make my binary look big? =^)

Thanks in advance

Upvotes: 1

Views: 858

Answers (2)

Grogh
Grogh

Reputation: 349

In order to get this to work, I modified the datacolumn for the TimeStamp to be a DateTime2 datatype in the database as follows.

class Session 
{ 
   [Key] 
   public Guid Id { get; set; } 
   public string Name { get; set; } 

   [Column(TypeName = "datetime2")]
   public DateTime TimeStamp { get; set; } 
}

Ultimately consider this to be a work around given that the issue appears to lie with LINQ in some way, however it helped me to get past the hurdle and thought I would post here in case anyone else runs into this.

Upvotes: 1

000
000

Reputation: 806

Check out the below code, it works! However the way it works is not logical. Because if you look at note1 and note2 values you see that date value is saved correctly, but is not applied in the query as it is.

var date = new DateTime(ts.Year, ts.Month, ts.Day, ts.Hour, ts.Minute, ts.Second, ts.Millisecond);
Session s2 = (from s in ctx.Sessions
            where (s.Name == "Testing" && s.TimeStamp == date)
            select s).First();

var note1 = s2.TimeStamp == date;
var note2 = s2.TimeStamp == ts;

I guess this is some error related to how Entity Framework assigns the value to @p_linq_0 parameter. This is the query I traced being executed but I could not find what is the value assigned to @p_linq_0.

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[TimeStamp] AS [TimeStamp]
FROM [dbo].[Sessions] AS [Extent1]
WHERE (N'Testing' = [Extent1].[Name]) AND ([Extent1].[TimeStamp] = @p__linq__0)

Upvotes: 1

Related Questions