mercredi 14 octobre 2015

Sqlite and strftime format string in generated sql code

I have to query my table for datetime interval. Suppose I have following 4 rows:

id  typeId    start         end

1    11    18.09.2014    18.09.2014 23:45:00

2    11    19.09.2014    19.09.2014 23:45:00

3    12    18.09.2014    18.09.2014 23:45:00

4    12    19.09.2014    19.09.2014 23:45:00

Linq query is following:

model.Entities.Where(t => t.StartTime >= start && t.EndTime <= end);

which becomes something like that:

//some omitted sql select from 

where   a.[StartTime] >= strftime('%Y-%m-%d %H:%M:%f', @p2)
        AND
        a.[EndTime] <= strftime('%Y-%m-%d %H:%M:%f', @p3)

p2 = '2014-09-18' and p3 =2014-09-19 23:45:00.000

As a result I get 2 rows instead of 4 rows. I've investigated the problem and saw that if format string had %S instead of %f then everything works great. I don't know whether it is a bug or an expected behavior, but I would like to get correct results.

I guess that System.Data.Sqlite provider is responsible for generating sql code, but reading source code of this provider ( SqlGenerator.cs and SQLite3.cs) I didn't find anything that looks strange.

The question is: can I influence some how on the sql code generation, can I provide a hint to use​ '%Y-%m-%d %H:%M:%S' format string instead of ​ '%Y-%m-%d %H:%M:%f' for strftime function?

Aucun commentaire:

Enregistrer un commentaire