How to add year to LINQ to entities query?

user_3990513 0
Viewed 185

I am attempting to query a table in SQL using LINQ to entities and add a year to a Date column.

I have tried the following query below:

data = data.Where(x => x.DueDate.Value.AddYears(1) >= DateTime.Now);

When I do this, I get the following error message:

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

I have done some research and it seems as if most people fix this issue by separating the query out and using a variable they then input into the expression.

I can't do this because I need to add the years to my data lambda expression and can't separate them out.

Is there is a simple way to fix this or is there a way to create a pseudo column that adds a year without actually creating an actual table column? I am trying to avoid having to create an entire new SQL column just for the purpose of adding a year to the date displayed.

Thanks for any help.

asp.net-mvc sql linq c#
1 Answers

You can do the inverse and subtract 1 year from DateTime.Now and use that as the comparison value.

var yearAgo = DateTime.Now.AddYears(-1); 
data = data.Where(x => x.DueDate >= yearAgo);

Side notes

  1. The comparison value has a time component but based on the naming of the persisted value DueDate might not have a time component. You can remove the time component by using .Date (DateTime.Now.Date.AddYears(-1);) or .Today instead of .Now (DateTime.Today.AddYears(-1);).
  2. If working in different time zones you will need a more robust solution. I would recommend reading Storing UTC Is Not a Silver Bullet by Jon Skeet.
user_12602040
Related