DateTime Error with SQLite and EF

[DevilSuichiro]

Hello,

I hope I'm in the correct sub forum for this, if not, please move this.

I have a Voyage table in my database with a few Attributes, BeginUtc is a datetime value.

When I try to execute a query involving any datetime field, for example

Code: Select all

var voyages = instance.Voyage.Where(x =>x.BeginUtc <= er.EventTimeUtc.Value);

it produces the following query to the database:

Code: Select all

SELECT 
Extent1.Id,
Extent1.ServiceKey,
Extent1.ServiceValue,
Extent1.Type,
Extent1.Name,
Extent1.BeginUtc,
Extent1.BeginLt,
Extent1.EstimatedEndUtc,
Extent1.EstimatedEndLt,
Extent1.LastSendAtUtc,
Extent1.LastSendAtLt,
Extent1.FirstSendAtUtc,
Extent1.FirstSendAtLt,
Extent1.IsDeleted,
Extent1.ConfigurationId
FROM Voyage AS Extent1
WHERE Extent1.BeginUtc <= ( CAST(:p__linq__0 AS datetime))

which looks perfectly fine. However, it returns the wrong results.
I have a voyage with BeginUtc=2014-09-16 11:00:00 in the database, but with er.EventTimeUtc=2014-09-17 11:26:00 the query returns no results.
I would suspect wrong encoding of datetimes in Linq2SQL when generating queries.
If not, could you tell me what I'm doing wrong, and what to do to work around this issue?

UPDATE: the following code works, but I don't really want to execute all LINQ Queries outside of DB.

Code: Select all

var allvoyagestest = instance.Voyage.ToList();
var voyagetest = allvoyagestest.Where(x => x.BeginUtc <= er.EventTimeUtc.Value).ToList();

greetings

P.S.: I made a mini example showing the issue.

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using SQLite.Net.Cipher;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using Devart.Data.SQLite;

namespace devartTest
{
    class DataContext : DbContext
    {
        public DataContext()
            : base(
                new SQLiteConnection(new SQLiteConnectionStringBuilder
                {
                    DataSource = @"C:\Users\CHLAN\Downloads\newDatabase.s3db",
                    FailIfMissing = false,
                    Password = "password",
                    Encryption = EncryptionMode.SQLiteCrypt
                }.ToString()), true)
        {
            Database.SetInitializer<DataContext>(null);
        }
        public DbSet<Datetimetest> Datetimetest { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            modelBuilder.Entity<Datetimetest>().ToTable("Datetimetest");
            modelBuilder.Entity<Datetimetest>().HasKey(x => x.ID);
            modelBuilder.Entity<Datetimetest>()
                .Property(x => x.ID)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            base.OnModelCreating(modelBuilder);
        }
    }

    class Datetimetest
    {
        public int ID { get; set; }
        public DateTime? Date { get; set; }
    }

    static class Program
    {
        static void Main(string[] args)
        {
            using (var instance = new DataContext())
            {
                instance.Database.ExecuteSqlCommand("CREATE TABLE Datetimetest(ID int primary key, Date datetime);");
                var date = new Datetimetest
                {
                    ID = 1,
                    Date = new DateTime(2014, 1, 1,0,0,0)
                };
                instance.Datetimetest.Add(date);
                instance.SaveChanges();

                Datetimetest da = new Datetimetest
                {
                    Date = new DateTime(2014, 1, 1, 12, 0, 0)
                };
                var results = instance.Datetimetest.Where(x => x.Date <= da.Date.Value);
                foreach (var datetimetest in results)
                {
                    Console.WriteLine("ID: {0}, Time: {1}",datetimetest.ID,datetimetest.Date);
                }
                Console.ReadLine();
            }
        }
    }
}

[Shalex]

Code: Select all

    var monitor = new Devart.Data.SQLite.SQLiteMonitor() { IsActive = true };
    var date = new DateTime(2014, 09, 17, 11, 26, 00);
    using (var context = new MainModel.MainEntities()) {

        var voyages = context.Voyages.Where(x => x.BeginUtc <= date).ToList();
    }

This code returns the record with BeginUtc=2014-09-16 11:00:00 in the database in our environment. Please send us a small test project with the corresponding SQLite database for reproducing.

[DevilSuichiro]

Please refer to the test project in my original post. The issue only seems to occur when the datetime is a property of an object.

[Shalex]

Please refer to the test project in my original post. The issue only seems to occur when the datetime is a property of an object.

It doesn't include the database file, definition of properties and mapping, versions of EF and Devart assemblies, etc. Please send us a small test project with the corresponding SQLite database for reproducing.

[Shalex]

Sorry, I didn't notice your update in the original message. We will investigate the issue and notify you about the result.

[Shalex]

The bug with comparing DateTime properties is fixed in the newest (5.3.506) build of dotConnect for SQLite: http://forums.devart.com/viewtopic.php?f=29&t=32576.