irfanmcsd irfanmcsd - 17 days ago 14
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("DateDiff(DAY,added_date,getdate())=0")
.Where(p => DbFunctions.DiffDays(p.added_date, DateTime.Now) == 0)
.ToList();
}
return _list;


Regarding Linq.Dynamic Expressions - how to write where clause

LINQ - dynamic WHERE clause?

Answer

Use DbFunctions

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

Edit:

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()))