Devart Forums

Posted: **Tue 17 Apr 2012 23:28**

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!

Posted: **Thu 19 Apr 2012 16:36**

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?

Posted: **Thu 19 Apr 2012 17:10**

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?

Posted: **Tue 24 Apr 2012 08:32**

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

Posted: **Sun 06 May 2012 17:09**

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" ?

Posted: **Mon 07 May 2012 15:19**

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.

Posted: **Mon 07 May 2012 16:34**

mikoro wrote: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.

Posted: **Wed 23 May 2012 16:13**

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 .

Posted: **Fri 25 May 2012 12:21**

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

Devart Forums

DateTime Ticks support for EF Code First?

DateTime Ticks support for EF Code First?

Re: DateTime Ticks support for EF Code First?

Re: DateTime Ticks support for EF Code First?

Re: DateTime Ticks support for EF Code First?

Re: DateTime Ticks support for EF Code First?

Re: DateTime Ticks support for EF Code First?