Include derived property in linq query on entity

Question

Here is a simple project based on the Poco class called Task :

 class Program { static void Main(string[] args) { using (MyDbContext ctx = new MyDbContext()) { // first query DateTime compareDate = DateTime.Now + TimeSpan.FromDays(3); var res = ctx.Tasks.Where(t => t.LastUpdate < compareDate).ToList(); // second query res = ctx.Tasks.Where(t => t.ShouldUpdate).ToList(); } } } public class MyDbContext : DbContext { public DbSet<Task> Tasks { get; set; } } public class Task { public int ID { get; set; } public DateTime LastUpdate { get; set; } public bool ShouldUpdate { get { return LastUpdate < DateTime.Now + TimeSpan.FromDays(3); } } } 

What I want to do is to query the dbset context by including the derived ShouldUpdate property in the where ShouldUpdate .

"The first query works fine" (I can't write it on one line, but that doesn't matter).

As you know, we get a NotSupportedException in the “second query” with the following message: The specified member of the “ShouldUpdate” type is not supported in LINQ to Entities. Only initializers, entities, and entity navigation properties are supported.

That's right, and I can understand why this happens, but I need to encapsulate the received information inside the Task object so that I can display the property in the grid or use it anywhere without duplicating the logic behind it.

Is there a clever technique for this?

NB: What is the technical name of the ShouldUplate property? received? calculated? calculated?

Answer 1

Finally, I found a solution. You can store partial queries (expressions) in static files and use them as follows:

 class Program { static void Main(string[] args) { using (MyDbContext ctx = new MyDbContext()) { res = ctx.Tasks.Where(Task.ShouldUpdateExpression).ToList(); } } } public class MyDbContext : DbContext { public DbSet<Task> Tasks { get; set; } } public class Task { public int ID { get; set; } public DateTime LastUpdate { get; set; } public bool ShouldUpdate { get { return ShouldUpdateExpression.Compile()(this); } } public static Expression<Func<Task, bool>> ShouldUpdateExpression { get { return t => t.LastUpdate < EntityFunctions.AddDays(DateTime.Now, 3); } } } 

Answer 2

An exemplary template will provide a better abstraction in this case. You can centralize the logic as follows. Define a new TaskSource property in your context.

 public class MyDbContext : DbContext { public DbSet<Task> Tasks { get; set; } public IQueryable<Task> TaskSource { get { return Tasks.Where(t => t.LastUpdate < EntityFunctions.AddDays(DateTime.Now, 3)); } } } 

Answer 3

You need to put ShouldUpdate logic in the linq-to-enitites request. You can use EntityFunctions.AddDays to help you this way.

 res = ctx.Tasks.Where(t => t.LastUpdate < EntityFunctions.AddDays(DateTime.Now, 3)).ToList();