DateTime Ticks support for EF Code First?

[dvelitch]

I'm using dotConnect for SQLite 3.80.341, professional edition
Entity Framework 4.3.1, Code First

Everything works great when my connection uses the ISO-8601 encoding for datetime. When I change my connection to use Ticks, however, I get a FormatException when the entity is being deserialized from the database:


[code]System.FormatException : Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, ref NumberBuffer number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt64(String value, NumberStyles options, NumberFormatInfo numfmt)
at Devart.Data.SQLite.a5.b(String A_0)
at Devart.Data.SQLite.Entity.l.a(Int32 A_0)
at System.Data.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
at lambda_method(Closure, Shaper)
at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at System.Linq.Enumerable.First(IEnumerable`1 source)
at System.Linq.Queryable.First(IQueryable`1 source)
at DataWriteTests.PerformanceTest.TestSelectRange() in PerformanceTest.cs: line 175[/code]


My query is extremely simple:

pdx.DoubleData.First();

Where my DbContext is "pdx" and entity set is DoubleData

Is there some hidden API I'm not using correctly? Or are ticks just not supported?

Thanks!

[Shalex]

SQLite does not have a storage class set aside for storing dates and/or times. So DateTime in SQLite database is stored as a string. From our provider's point of view, there are two possible formats in the DateTime's string:
1) ISO8601 (like "YYYY-MM-DD HH:MM:SS.SSS")
2) Ticks (just a number: a single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond)
Depending on the DateTime Format connection string parameter value, our provider tries to parse DateTime value.

It seems like your DateTime is stored in the database in the ISO8601 format but you are reading its values like a number ("DateTime Format=Ticks;"). Does it explain the error?

[dvelitch]

Thanks for the reply.

I don't understand why the date would be stored in ISO8601 format when I explicitly am requesting ticks.

Here's my code

Code: Select all

public class TestEntity
    {
        [Key]
        public DateTime When { get; set; }
    }

public class TestDbContext : DbContext
  {
        static TestDbContext()
        {
            SQLiteEntityProviderConfig config = SQLiteEntityProviderConfig.Instance;
            config.Workarounds.IgnoreSchemaName = true;
        }

        public TestDbContext(string cnnStr) :base(new     SQLiteConnection(cnnStr), true)
        {
        }

        public DbSet TestEntities { get; set; }
    }

As simple as it gets.
An entity with a DateTime field. A DBContext that knows about the entity.

Now, here's the code that causes the error:

Code: Select all

var cnnString = "Pooling=false;Data Source=test.db;DateTime Format=Ticks;FailIfMissing=false";
            using(var ctx = new TestDbContext(cnnString))
            {
                var newEntity = new TestEntity { When = DateTime.Now };
                ctx.TestEntities.Add(newEntity);
                ctx.SaveChanges();
            }

            using (var ctx = new TestDbContext(cnnString))
            {               
                ctx.TestEntities.First(); // BOOM               
            }

Again, as simple as it gets.

My connection string is explicitly requesting that datetimes are stored as ticks.
I create my context with that connection string.
I store an entity.
I then attempt to retrieve the entity that I just stored.

Exception happens ("//BOOM")

I don't understand what I'm doing wrong?

[Shalex]

We have reproduced and are investigating the issue. We will post here about the results.

[mikoro]

I tried this with EF 4.3.1 and EF 5 prerealease.

Using ticks this gets saved in the database: "1698789-19650109-20 -19:-12:-55"

I would guess that is not correct?

Using ISO8601 this gets saved: "2012-05-06 17:02:40"

I think it's not correct either because it loses a lot of precision. How could I adjust the format of the time string to get something like this: "2012-05-06T15:31:01.7023824Z" ?

[Shalex]

Ticks-based DateTime columns are supported in Entity Framewok. We will notify you when the corresponding build of dotConnect for SQLite is available for download.

[Shalex]

Using ISO8601 this gets saved: "2012-05-06 17:02:40"

I think it's not correct either because it loses a lot of precision. How could I adjust the format of the time string to get something like this: "2012-05-06T15:31:01.7023824Z" ?

We are investigating the issue.

[Shalex]

New version of dotConnect for SQLite 4.0 is released!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=29&t=24183 .

[Shalex]

The bug with fractional seconds rounding in the INSERT command is fixed in the latest build as well.