irfanmcsd irfanmcsd - 6 months ago 87
C# Question

Using DateDiff with Linq.Dynamic library for fetching today records

I am trying to fetch all records added today using DateDiff SQL syntax via Linq expression in MVC 5 / Entity Framework 6 application. DateDiff function throw runtime error

Actually i want to the following linq WHERE clause to parse with linq dynamics

.Where(p => DbFunctions.DiffDays(p.added_date, DateTime.Now) == 0)

in order to fetch today added records. Sample code that i am using shown below

var _list = new vsk_error_log();
using (var entities = new vskdbEntities())
_list = entities.vsk_error_log
.Where(p => DbFunctions.DiffDays(p.added_date, DateTime.Now) == 0)
return _list;

Regarding Linq.Dynamic Expressions - how to write where clause

LINQ - dynamic WHERE clause?


Use DbFunctions

.Where(p => DbFunctions.DiffDays(p.AddedDate, DateTime.Now) == 0)


If you want to invoke this dynamically, you'll need to modify code for the Dynamic LINQ.

  1. Download the sample project containing DynamicLibrary.cs. The file is located under App_Code folder.
  2. Find the static definition for predefinedTypes and add typeof(DbFunctions) at the very end.

Now you will be able to do this:

.Where("DbFunctions.DiffDays(AddedDate, DateTime.Now) = 0")

And it will be translated to this SQL:

WHERE 0 = (DATEDIFF (day, [Extent1].[AddedDate], SysDateTime()))