F# query expressions - restriction using string comparison in SqlProvider with SQLite

Question

SQLite doesn't really have date columns. You can store your dates as ISO-8601 strings, or as the integer number of seconds since the epoch, or as Julian day numbers. In the table I'm using, I want my dates to be human-readable, so I've chosen to use ISO-8601 strings.

Suppose I want to query all the records with dates after today. The ISO-8601 strings will sort properly, so I should be able to use string comparison with the ISO-8601 string for today's date.

However, I see no way to do the comparison using the F# SqlProvider type provider. I'm hoping that this is just a reflection of my lack of knowledge of F# query expressions.

For instance, I can't do:

   query { 
       for calendarEntry in dataContext.``[main].[calendar_entries]`` do
       where (calendarEntry.date >= System.DateTime.Today.ToString("yyyy-MM-dd hh:mm:ss"))        
   ... }

I get:

The binary operator GreaterThanOrEqual is not defined for the types 'System.String' and 'System.String'.

I also can't do any variation of:

   query { 
       for calendarEntry in dataContext.``[main].[calendar_entries]`` do
       where (calendarEntry.date.CompareTo(System.DateTime.Today.ToString("yyyy-MM-dd hh:mm:ss")) >= 0)
   ... }

I get:

Unsupported expression. Ensure all server-side objects appear on the left hand side of predicates.  The In and Not In operators only support the inline array syntax.

Anyone know how I might do string comparisons in the where clause? It seems that my only option for filtering inside the query is to store seconds-since-epoch in the database and use integer comparisons.

Answer 1

This was a temporary bug with old SQLProvider version and it should be working now. If not, please open a new issue to the GitHub repository: https://github.com/fsprojects/SQLProvider