How to compare dates in LINQ?

Question

I want to check if a given date is more than a month earlier than today's date using LINQ.

What is the syntax for this?

Thanks in advance.

Answer 1

I assume that you're talking about in the where clause. It's basically the same way you would compare two DateTime objects elsewhere.

using (DataContext context = new DataContext()) {
   var query = from t in context.table
               where t.CreateDate.Date < DateTime.Today.AddMonths(-1)
               select t;
}

Answer 2

Just to add, in LINQ To Entities you have to compare to a variable. Example:

        DateTime lastMonth = DateTime.Today.AddMonths(-1);
        using (var db = new MyEntities())
        {
            var query = from s in db.ViewOrTable
                        orderby s.ColName
                        where (s.StartDate > lastMonth)
                        select s;

            _dsResults = query.ToList();
        }

Answer 3

If you don't want your code to throw

LINQ to Entities does not recognize the method 'System.DateTime AddYears(Int32)' method, and this method cannot be translated into a store expression.

I would suggest using DbFunctions.AddYears. Also, if you are concerned only about dates and not the times, you could use DbFunctions.TruncateTime

Something like this-

DbFunctions.TruncateTime(x.date) ==
DbFunctions.TruncateTime(DbFunctions.AddYears(DateTime.Today, 1))