Using DateDiff with Linq.Dynamic library for fetching today records

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

Answer 1

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

Answer 2

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.

Answer 3

Your code is never executed in the database, the "issue" is that Linq.Dynamic tries to parse it as C# code, where it fails. As far as I know it is not possible to call SQL with dynamic linq.

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

Answer 4

You shouldn't need to run a SQL string when using LINQ to entities. As other have pointed out, I'm not even sure if it is possible. The correct syntax would be something like:

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;

entity framework will then compile this into SQL for you

Answer 5

Code posted below helped me fixed my issue.

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

// Fetch 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 (without time)

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