Click here to Skip to main content
15,849,130 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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);
Updated 31-Oct-16 11:12am

1 solution

The SqlFunctions will only work with LINQ to SQL; the EntityFunctions can only be invoked from Entity Framework.

If you want to call a specific function from NHibernate, then you'll need to create a custom dialect:
Pure Dot Net Coder: Using DateAdd with NHibernate Linq[^]

Alternatively, change your criteria:
var maxDate = DateTime.Now.AddMonths(-6);
var result1 = myTable.Where(x => x.MyDate <= maxDate);

That will also have the benefit of being able to use an index or partition on the date column to satisfy the query, rather than having to read every record in the table.
Share this answer
Ryan Zahra 31-Oct-16 16:18pm    
Thanks for the quick answer. Changing the criteria will effect my results so most probably I would be going for the custom dialect. But before that, isn't there any "simpler" way to just add the date for my query?
Richard Deeming 31-Oct-16 16:20pm    
Why would it affect your results? MyDate + 6 months <= Today is exactly the same as MyDate <= Today - 6 months.
Ryan Zahra 31-Oct-16 16:22pm    
You're right about that, my mistake! Thanks a lot for your help! I'll follow this and will test it out more. Thanks again!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900