Using DateDiff with Linq.Dynamic library for fetching today records

c# entity-framework entity-framework-6 linq

Question

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

Dynamic WHERE clause in LINQ

1
7
5/23/2017 12:24:29 PM

Accepted 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()))
25
10/16/2019 12:13:45 PM

Popular Answer

flindeberg is correct when he say that System.Linq.Dynamic parses the expression that you give as C#, not SQL.

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

DbFunctions.DiffDays is the method that you are looking for. With this, you also don't need to be using System.Linq.Dynamic.

Your code would 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 would 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;

HOWEVER! System.Linq.Dynamic will not recognize the class DbFunctions, and as such, this will not work out of the box. However, we can "patch" this functionality in using a bit of reflection, although it can be a bit 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 will now recognize DbFunctions as a type that can be used in the parsed C# expressions.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow