How does one use a custom property in a LINQ-to-Entities query?

Question

I have a class Post which is an Entity Framework model. It contains a property like this:

public bool Showable {
  get {
    return this.Public && this.PublishedDate > DateTime.now
  }
}

I can use it in a query like this:

from p in db.Posts where p.Showable select p;

but when I have a property that uses it, like this

public IEnumerable<Post> ShowablePosts {
  get {
    return from p in db.Posts where p.Showable select p;
  }
}

then I can't do:

from p in ShowablePosts where p.Id > 42 select p;

It says:

The specified type member 'Showable' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Bloodfin

Answer 1

You can do this if you write the property as an Expression which is translatable to SQL.

Here's how to do it.

That's a bit complicated, because it's a general solution to a complicated problem.

The general idea is this: LINQ to Entities (like all LINQ providers) can't translate compiled code like your property into SQL at runtime. LINQ to Objects can execute compiled code, but it can't translate it. But they can translate an Expression<T>. So you could write:

public static Expression<Func<Post, bool>> WhereActive
{
    get
    {
        return p => p.Public && p.PublishedDate > DateTime.Now;
    }
}

Then you could write:

public IEnumerable<Post> ShowablePosts 
{
    get 
    {
        return db.Posts.Where(WhereActive);
    }
}

...and LINQ to Entities could translate that. The code in the post I link generalizes this idea.

Toneless

Is there a way to pass a parameter to WhereActive? For example the DateTime. If so, you might be able to answer https://mcmap.net/q/750012/-write-a-translatable-method-for-linq-to-entities/2968001 – Keener

Answer 2

Unfortunately, the Entity Framework simply does not support computed properties (that is to say, a property that returns a computed value rather than a reference to a backing field) but it may be supported at a future date.

Invalidity

I can call computed properties fine in a query but not in a method that runs a query. It's weird. – Dreamworld

I live in the Linq-to-SQL world at the moment, is return from p in db.Posts where p.Showable select p; natively an IQueryable<Post> or IEnuemerable<Post> in EF? And would that matter on his question? – Valenba

@J. Pablo Fernandez: Linq query are not execute when you define them, only when you do something that requires the actual results. There are many things that Linq supports, bu that the Entity Framework does not, and these things only become obvious upon execution of the query. – Kinglet

Answer 3

I think easiest way to do this is using Computed attribute of DelegateDecompiler.EntityFramework package that mentioned in this answer.

Microscope