Using Fluent NHibernate, I would like to check a future date with a give date. For simplicity's sake, I've reduced my code to the bare minimum below. The property "MyDate" is mapped to a column in Sql Server of type "Date". However, I've tried the same code below using the Sql Type "DateTime" but I still got the same result. I'm trying using the below code:
var myTbl = IQuerable<mytable>();
var result = myTbl.Where(x => SqlFunctions.DateAdd("month", 6, x.MyDate).Value <= DateTime.Now);
An error is not being throw, however, while debugging, when I try to see the Result View of the variable result, I can see the following in the watch window "{"PartialEvalException (NotSupportedException (\"This function can only be invoked from LINQ to Entities.\"), DateAdd(\"dd\", 6, 31/10/2016 20:33:05))"} System.SystemException {System.NotSupportedException}"
Any help would be greatly appreciated!
What I have tried:
I tried variations to the above with the following code, however, all code resulted in errors being thrown in NHibernate.
var result1 = myTable.Where(x => SqlFunctions.DateAdd("month", 6, x.MyDate) <= DateTime.Now);
var result2 = myTable.Where(x => SqlFunctions.DateAdd("month", 6, DateTime.Now) <= DateTime.Now);
var result3 = myTable.Where(x => SqlFunctions.DateAdd("month", 6, x.MyDate) != null && SqlFunctions.DateAdd("month", 6, x.MyDate) <= DateTime.Now);
var result4 = myTable.Where(x => EntityFunctions.AddMonths(x.myDate, 6) <= DateTime.Now);