Linq to Entityframework Datetime

Linq to EntityFramework DateTime

When using LINQ to Entity Framework, your predicates inside the Where clause get translated to SQL. You're getting that error because there is no translation to SQL for DateTime.Add() which makes sense.

A quick work-around would be to read the results of the first Where statement into memory and then use LINQ to Objects to finish filtering:

Context.Article.Where(p => p.StartDate < DateTime.Now)
               .ToList()
               .Where(p => p.StartDate.AddDays(p.Period) > DateTime.Now);

You could also try the EntityFunctions.AddDays method if you're using .NET 4.0:

Context.Article.Where(p => p.StartDate < DateTime.Now)
               .Where(p => EntityFunctions.AddDays(p.StartDate, p.Period)
                   > DateTime.Now);

Note: In EF 6 it's now System.Data.Entity.DbFunctions.AddDays.

Comparing dates in LINQ Entity Framework

Even if the query could be translated, it would be a bad query because it would have to calculate the difference for every single row before filtering. Indexing wouldn't help, because indexes are built from the stored values.

The solution is to calculate the cutoff date in advance and compare with the field directly.

This code will find records in the past 30 days

var cutoff=DateTime.Now.AddDays(-30);

List<EPCRA_Events> x =  _dbContext
              .Where(e => e.DueDt > cutoff)
              .ToList();

While this will find records up to 30 days in the future :

var cutoff=DateTime.Now.AddDays(30);

List<EPCRA_Events> x =  _dbContext
              .Where(e => e.DueDt < cutoff)
              .ToList();

While this will return records in the next 30 days, including today:

var cutoff=DateTime.Now.AddDays(30);

List<EPCRA_Events> x =  _dbContext
              .Where(e => e.DueDt>=DateTime.Today &&  e.DueDt < cutoff)
              .ToList();

This will ignore NULLs and use any indexes that cover DueDt

LINQ to Entities DateTime Compare

Just create 2 dates:

var datePlusOneDay = date.AddDays(1);

return context.Events.Any(x => x.UserId == id 
                         && x.Date >= date 
                         && x.Date < datePlusOneDay 
                         && x.Type == EventType.StartWork);

Also I'm not sure but problem could be that your date can have not only date part but also time part.

So to be sure that you select only date part of your DateTime variable you can do like this:

date = date.Date;
var datePlusOneDay = date.AddDays(1);

Entity Framework: Linq where clause compare date and time value with seconds difference

Finally i got solution for my problem. The hidden problem is that my sql server is at south-Acia and i am accessing it from south-India, so time zones are mismatching because Linq converting Datetime.Now into SysDateTime() at runtime. To overcome this i had added 330 minutes(that is 5 hours 30 minutes) to the Datetime.Now as shown in below code snippet.

  var onLineP = (from od in db.RTLS_ONLINEPERSONSTATUS
                where (lsdAts <= od.created_time && od.created_time <= DbFunctions.AddMinutes(DateTime.Now, 330))
                                           select od).ToList(); 

Convert String to DateTime in LINQ query with Entity Framework Core

You can relatively easily add the desired unsupported method using the EF Core scalar function mapping.

For instance, add the following class (with the necessary usings):

namespace Microsoft.EntityFrameworkCore
{
    public static class SqlFunctions
    {
        public static DateTime? ToDateTime(this string s, int format) => throw new NotSupportedException();

        public static ModelBuilder AddSqlFunctions(this ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(() => ToDateTime(default, default))
                .HasTranslation(args => new SqlFunctionExpression(
                    functionName: "CONVERT",
                    arguments: args.Prepend(new SqlFragmentExpression("date")),
                    nullable: true,
                    argumentsPropagateNullability: new[] { false, true, false }),
                    type: typeof(DateTime),
                    typeMapping: null));

            return modelBuilder;
        }
    }
}

The second method is for convenience and does the actual mapping.

Now all you need is to call it from OnModelCreating override:

if (Database.IsSqlServer()) modelBuilder.AddSqlFunctions();

and then use it inside the LINQ to Entities query:

var query = db.Set<MyEntity>()
    .Where(e => e.MyProp.ToDateTime(102) > DateTime.Today
    .ToQueryString();
// SELECT ..... WHERE Convert(date, [e].[MyProp], 102) > CONVERT(date, GETDATE())

LINQ expression could not be translated with DateTime comparison in LINQ query?

If the time component is static (same for all rows) then a simple option is to apply it to the current date to form a cutoff to compare against:

Instead of:

TimeSpan bookTimeToLive = TimeSpan.FromHours(10);
IList<Book> expiredBooks = dbContext.Value.Books
                .Where(x => DateTime.UtcNow - x.UtcTimeStamp > bookTimeToLive)
                .ToList();

Something like this. No need for DbFunctions.

DateTime expiryCutoff = DateTime.UtcNow.AddHours(-10);
Ilist<Book> expiredBooks = dbContext.Books
    .Where(x => x.UtTimeStamp < expiryCutoff)
    .ToList();

If it is dynamic, DateTime methods like AddHours will still translate:

Ilist<Book> expiredBooks = dbContext.Books
    .Where(x => x.UtTimeStamp.AddHours(x.ExpiryCutoff) < DateTime.UtcNow)
    .ToList();

Where ExpiryCutoff is a data-driven value in the record. (or a related expression)

---

Related Topics