2

I have a function, which takes in a number of days (var daysPrior), and a date (var inDate). The date is the current date we are looking at (for live system, it will be today's date). The number of days needs to be subtracted from a date field in our database (var SomeDate, type:timestamp with time zone).

I am attempting to write a linq query, in this function, to subtract the number of days from a date field in our postgres database. I have tried a couple of things and done a lot of searching around with no luck.

Attempt 1

I started off with this query.

var negatedDaysPrior = System.Math.Abs(daysPrior) * (-1);
var query = dataContext.Table.Where(x => x.SomeDate.Value.AddDays(negatedDaysPrior) <= inDate);

This converts to the following SQL (taken from ErrorSQL field of exception) and gives the following error: 

SELECT
    x.SomeFields
FROM
    schema.table x
WHERE
    x.some_date + Interval '((E'-5')) Day' <= ((E'2015-01-16 09:45:26.513059'))

Error: {"ERROR: 42601: syntax error at or near \"')) Day'\""}

Attempt 2

var negatedDaysPrior = System.Math.Abs(daysPrior) * (-1);
TimeSpan timespanDays = new TimeSpan(daysPrior, 0, 0, 0);

var query = dataContext.Table.Where(x => x.SomeDate.Value.Subtract(timespanDays) <= inDate);

When I run this, I get a BLToolkit.Data.Linq.LinqException:

'ConvertNullable(x.SomeDate).Subtract(value(ReferenceToMyFile+<>c__DisplayClass15+<>c__DisplayClass17).timespanDays)' cannot be converted to SQL.

Does anyone know if it is possible to subtract days from a date in the database using linq?

The tools we are using are: postgres database, BLToolkit for the linq to sql conversion.

GreymondTheLong
  • 48
  • 1
  • 10

1 Answers1

3
inDate = inDate.AddDays(-daysPrior)
var query = dataContext.Where(x => x.SomeDate >= inDate)
Richard Schneider
  • 34,944
  • 9
  • 57
  • 73
  • 1
    Please add some explanatory text describing why this would work when the OP's other attempts did not. That will help future visitors understand why this is the best answer (if indeed it is :)). – Heretic Monkey Jan 15 '15 at 22:26
  • 1
    @MikeMcCaughan I think in my opinion that Richard's code is self explanatory unless you are not familiar with lambda expressions.. – MethodMan Jan 15 '15 at 23:16
  • Not a problem @RichardSchneider – MethodMan Jan 16 '15 at 13:59
  • 1
    I added that while reviewing it in the Low Quality review queue so apparently I wasn't the only one concerned about the lack of explanation. I didn't vote to close it, though, as I do feel it answers the OP's question. All that needed to be added was some text about how the calculation should be done outside of the lambda so that LINQ doesn't try to convert it to SQL. It's certainly up to the author whether or not to add that explanatory text. – Heretic Monkey Jan 16 '15 at 17:10