How to use DateTime.AddDays(x) in Entity Framework

Question

I have this code:

from pr in e.ProgramSetup.Include("Program").Include("Program.Client")
        where pr.DateBegin < DateTime.Now
        && pr.DateEnd > DateTime.Now.AddDays(pr.DateEndOffset) 
select pr).ToList();

It does not work, because AddDays() is not possible to use for generating sql.

So is there some another way? Now i select everything and filter it finaly by foreach, but it is not good way in my opinion.

Problem is that pr.DateEndOffset is also only in db, it is not constant...

Salomesalomi

Answer 1

using System.Data.Entity;
...
DbFunctions.AddDays(dateFromDataStore, numDaysToAdd);

Alcine

Would be nice if you explain your solution. – Dedradedric

@Mehdi it relates to the original post - it is a way to add days to a date (from a data table) in an entity framework query. – Alcine

Answer 2

You need to use one of the EntityFunctions mapped to the canonical functions. Here is an example for AddDays:

public class MyEntity
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<MyEntity> Entities { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using (var ctx = new MyContext())
        {
            if (!ctx.Entities.Any())
            {
                ctx.Entities.Add(new MyEntity() { Date = new DateTime(2000, 1, 1) });
                ctx.Entities.Add(new MyEntity() { Date = new DateTime(2012, 10, 1) });
                ctx.Entities.Add(new MyEntity() { Date = new DateTime(2012, 12, 12) });
                ctx.SaveChanges();
            }

            var q = from e in ctx.Entities
                    where e.Date > EntityFunctions.AddDays(new DateTime(2012, 10, 1), 10)
                    select e;

            foreach (var entity in q)
            {
                Console.WriteLine("{0} {1}", entity.Id, entity.Date);
            }
        }
    }
}

Kuhns

Did you try this ? If so, what connector do you have installed ? I tried and it doesn't work. It says it can't translate AddDays. I had to create the AddDays function in the DB - then it worked – Affaire

For Sql Server this should work out of the box. What database are you using? – Kuhns

Sorry I don't know MySQL very well? Does MySQL have built-in functions to manipulate dates? Canonical AddDays function maps to DateAdd function in SqlServer. If there is not a counter part in MySQL that could be the reason. Otherwise I would file a bug against the provider you are using since I think this is the provider that needs to be able to translate the function from the query tree accordingly... – Kuhns

Yes, it's probably the provider's fault, that's why I was asking what connector you used. But no worries, I got it to work as I said. Thanks for your interest in my comments. – Affaire

EntityFunctions is obsolete. Use DbFunctions instead. – Roldan

Answer 3

For MySQL you also need to create a function for the database being queried:

CREATE FUNCTION
AddDays(dateValue DateTime, days INT) RETURNS date
DETERMINISTIC
return AddDate(dateValue, days);

SQL Server automatically routes through to the intrinsic DATEADD function.

Parrott