Using DateDiff with Linq.Dynamic library to retrieve today's records

Question

I am trying to retrieve all the records added today using DateDiff SQL syntax via Linq expression in MVC 5 / Entity Framework 6 application. Error executing DateDiff date function

Actually, I want the following LINQ WHERE expression to parse linq dynamics

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

to get the entries you added today. Sample code that I use 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 where

Dynamic WHERE clause in LINQ

Answer 1

Use DbFunctions

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

Edit:

If you want to call this dynamically, you need to change the code for dynamic LINQ.

1. Download a sample project containing DynamicLibrary.cs . The file is located in the App_Code folder. Install the Dynamic LINQ library from nuget, original or non-standard support .
2. Find the static definition for predefinedTypes and add typeof(DbFunctions) at the very end.

Now you can do this:

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

And it will be translated into this SQL:

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

Answer 2

flindeberg is correct when it says that System.Linq.Dynamic parses an expression that you give as C #, not SQL.

However, the Entity Framework defines the "DbFunctions" class, which allows you to call sql functions as part of your Linq queries.

DbFunctions.DiffDays is the method you are looking for. However, you also do not need to use System.Linq.Dynamic.

Your code will look something like this, I think:

  var _list = new vsk_error_log(); using ( var entities = new vskdbEntities() ) { _list = entities.vsk_error_log .Where( entry => DbFunctions.DiffDays( entry.added_date, DateTime.UtcNow ) == 0 ) .ToList(); } return _list; 

If you want to use this function with System.Linq.Dynamic, it will look something like this:

  var _list = new vsk_error_log(); using ( var entities = new vskdbEntities() ) { _list = entities.vsk_error_log .Where( "DbFunctions.DiffDays( added_date, DateTime.UtcNow ) == 0" ) .ToList(); } return _list; 

BUT! System.Linq.Dynamic does not recognize the DbFunctions class, and therefore it will not work out of the box. However, we can “fix” this function using a little reflection, although it can be a little ugly:

  var type = typeof( DynamicQueryable ).Assembly.GetType( "System.Linq.Dynamic.ExpressionParser" ); FieldInfo field = type.GetField( "predefinedTypes", BindingFlags.Static | BindingFlags.NonPublic ); Type[] predefinedTypes = (Type[])field.GetValue( null ); Array.Resize( ref predefinedTypes, predefinedTypes.Length + 1 ); predefinedTypes[ predefinedTypes.Length - 1 ] = typeof( DbFunctions ); field.SetValue( null, predefinedTypes ); 

By running this code, System.Linq.Dynamic now recognizes DbFunctions as a type that can be used in C # expressions.

Answer 3

Your code is never executed in the database, the “problem” is that Linq.Dynamic trying to Linq.Dynamic it as C# code, where it fails. As far as I know, it is not possible to call SQL with dynamic linq.

I believe that what you are looking for is using raw SQL , not the .NET code that Linq.Dynamic is, this MSDN page will give you more information about using raw SQL .

Answer 4

You do not need to run the SQL string when using LINQ for objects. As others have pointed out, I'm not even sure if this is possible. The correct syntax would look something like this:

 var _list = new List<vsk_error_log>(); using (var entities = new vskdbEntities()) { _list = entities.vsk_error_log .Where(log => log.added_date >= DateTime.Now.AddDays(-1)) .ToList(); } return _list; 

the entity structure then compiles this into SQL for you

Answer 5

The code below helped me fix the problem.

 var _list = new List<vsk_error_log>(); using (var entities = new vskdbEntities()) { _list = entities.vsk_error_log .Where("added_date >= @0", DateTime.Now.AddDays(-1)) .OrderBy(entity.Order) .Skip(entity.PageSize * (entity.PageNumber - 1)) .Take(entity.PageSize) .ToList(); } 

// Retrieve data between two dates using dynamic linq

 .Where("added_date >= @0 AND added_date < @1", DateTime.Now.AddDays(-7), DateTime.Now); 

Answer 6

// specify date range (no time)

  DateTime currentDate = System.DateTime.Now.Date; query = query.Where(p => p.added_date == currentDate);