DATEDIFF filter in OrmLite

ServiceStack.OrmLite
1

Hi, I’m trying to filter on a date difference, like so:

WHERE DATEDIFF(DAYS, Table.EndDate, Table.BeginDate) > 5

How can I do that in OrmLite? The following does not work and fails to parse:

q.Where<LRAPaziente, LRARichieste>(paziente.DataDiNascita.Value - richiesta.DataOraAccettazione).Days >= etaInGiorni)
2

You’d need to use custom SQL for executing non-standard RDBMS functions:

.Where("DATEDIFF(DAYS, Table.EndDate, Table.BeginDate) > 5")

You can also use Column and Table APIs to provide a more typed API, e.g:

q.Where($$"DATEDIFF(DAYS, {q.Column<Table>(x => x.EndDate)}, {q.Column<Table>(x => x.BeginDate)}) > 5")

Aside: don’t try to use non-documented properties of C# types in your C# expressions, if it’s not documented anywhere there’s no support for it.

3

Ok.

But did I used a non documented property? TimeSpan.Days is pretty well documented.

4

Documented usage that it’s something OrmLite supports.

You can’t navigate a property on a C# object and expect it to be magically translated to server-side SQL. Everything needs to be especially catered for, essentially the only standard RDBMS functions OrmLite supports are on the Sql class.

5

I’m sorry, I though you meant undocumented from the .net perspective.

Anyway, i’ve used the sql fragment, thanks.