Calling SQL’s DateDiff Function in LINQ-to-Entities (A SqlFunctions Post)

Thursday, December 04, 2014 @ 09:00

By Corey Adler

Much like my last post, this one is also inspired by a recent question on StackOverflow. User Robert (http://stackoverflow.com/users/1073700/robert) had asked a question (http://stackoverflow.com/questions/26911037/how-to-get-around-only-parameterless-constructors-and-initializers-error) in which he was trying to execute a LINQ-to-Entities query on their database for specific records which had a due date a week (or less) away from the current date. No matter what he did to fix it he kept getting the same error message, and had thus turned to StackOverflow for guidance. So for this post I will discuss (in greater detail than on StackOverflow) the solution to this problem, introducing you to the SqlFunctions class in the process.

So what exactly was going wrong? He had the something similar to the following:

DateDiff Query Question

He was trying to grab all courses that were set to expire at some point in the next 7 days that met some other conditions as well. To do so he was trying to instantiate a new DateTime object in order to compare the record’s DateDue property with the current date (DateTime.Now) to find out the total number of days between them, and then only select records where that value was less than 8 (i.e. a week). He kept getting the following error message: Only parameterless constructors and initializers are supported in LINQ to Entities.

Obviously this error was due to instantiating the DateTime object from within the LINQ query. LINQ’s query methods, it should be recalled, are essentially a layer of abstraction over some concrete providers which translate the query to best grab the data requested. In this case, when the provider attempts to translate the instantiation of the DateTime object into a SQL expression, it can’t figure out what to make of it, and spits out the error message above. So then how can someone generate the correct query for this case?

The answer lies in a little-known helper class for LINQ to Entities called SqlFunctions (documentation of which can be found here: http://bit.ly/1ANO6gz). All of the different methods in this class were constructed for the sole purpose of being able to write more complex LINQ to Entities queries by granting the ability to call some functions that are baked into SQL. The SqlFunctions class contains many of the functions that you’ve come to know and love from SQL—including Checksum, DatePart, and many others. For the purpose of this post, however, I would like to take a look at the DateDiff function, the many overloads of which are shown below. DateDiff takes 2 dates, compares them based on a specified part of that date (like the day or the month), and returns the difference between them as a number.

DateDiff Overloads

The first one is what I’ll be using to solve this question; I just wanted to show you the other overloads so you could see how much attention the Entity Framework people over at Microsoft gave to fixing problems like this one. The first thing that needs to be passed in is which part of the date we want to do the diff, using the exact same syntax that regular SQL would expect to see. Afterwards, just pass in the 2 dates that you want to compare and you’re off and running. The best part of this is that the DateTime objects can be null and it will still work out just fine.

So, to solve the problem mentioned above all one has to do is to adjust the query as follows:

DateDiff Query Answer

And that did it. The above now queries for courses that are expiring in a week (or less) from now, and it doesn’t even require the use of a “let” clause! I hope you take a good look at some of the functionality of the SqlFunctions class, and all that Entity Framework has to offer.

Until next time, I wish you good coding.