Compare Two date "Strings" Sqlite using Nhibernate

Question

I am using SQLite and NHibernate and Im storing my date as Strings on SQLite since I cant store as Date. Everything was just fine until a need to compare dates.. I tried the following codes:

            var initialDate = DateTime.Parse(_InitialDate);
            var finalDate = DateTime.Parse(_FinalDate);
            return session.QueryOver<Locacoes>()
                .Where(c => DateTime.Parse(c.InitialDate) >= initialDate )
                .Where(c => DateTime.Parse(c.FinalDate) <= finalDate).List();

but I got an exception on the first "Where": "a variable 'c' of type 'Locacoes' is referenced on scope '', but it is not defined".
How can I compare date on SQLite using NHibernate? I've tried many things with above code, but didnt work.

Edit: Its not duplicate, the error can be the same, but the result is different

Possible duplicate of [variable '' of type '' referenced from scope '', but it is not defined](http://stackoverflow.com/questions/30556911/variable-of-type-referenced-from-scope-but-it-is-not-defined) — Rabban, Nov 04 '16 at 09:48
@Rabban its not duplicate, the error can be the same, but how I will Parse my string into DateTime ? I tried to do following that answer, but Expression is deprecated and I dont know how to convert my string to date — GuiPetenuci, Nov 04 '16 at 10:44
as i understand, your issue has nothing to do with the DateTime as string. The exception says that `Locacoes` is not defined in the scope. But to fix your Datetime/string problem, you can use `CustomTypeConverter` in NHibernate. With this, you can define your InitialDate/FinalDate Properties as DateTime in your entity and still save it as string. [This](http://stackoverflow.com/a/242056/6666799) could help you. — Rabban, Nov 04 '16 at 10:54
Your QueryOver query is translated into SQL. If SQLite doesn't have a date datatype, then it will have no way of comparing dates. So basically there's no way to do what you're trying to do without pulling the records into memory and doing the comparison that way. — Andrew Whitaker, Nov 06 '16 at 02:24

score 0 · Accepted Answer · answered Nov 07 '16 at 00:44

First, while Sqlite likes to pretend everything is a string, that should be considered a storage format and NO REASON why your object model should use strings to hold date values. Your object model should of course type the properties as DateTime or DateTimeOffset. Then configure your NHibernate mappings properly to map the values to what SQLite can handle (actually I think NHibernate would handle that automatically if you just tell it to use the SQLiteDialect).

Second, I don't think QueryOver() can handle things like DateTime.Parse(). Don't confuse QueryOver() with Linq2NHibernate (the Query() method), which have more advanced expression interpretation abilities. On the other hand, you no longer need to use Parse() when you begin to use correct types in your object model.

At least if you use LINQ, it should be able to handle DateTime.Date for SQLite, if you need it:

session.Query<Locacoes>()
       .Where(l => c.InitialDate.Date >= initialDate)

Of course, you would only need to put Date in there if there is a non-zero time-of-day component that you need to ignore.

Thats exactly whats I was needing, I didnt knew about NHibernate automatically considering DateTime as String if Im using SQLite. — GuiPetenuci, Nov 07 '16 at 12:04

Compare Two date "Strings" Sqlite using Nhibernate

1 Answers1