Creating a property that LINQ to Entities can translate

Question

I am curious on how to create a property that can be translated by LINQ. Below is a very simple example.

I have a table/class Category, that has a column ParentId linking to itself (So a category can have sub-categories)

EF automatically generates a property Category1, which is the parent category.

For the sake of clarity, I created another property

public partial class Category
{
  public Category Parent
  {
    get { return Category1; }
  }
}

The problem is, this works

var categs = ctx.Categories.Where(x => x.Category1 == null);

but this doesn't work

var categs = ctx.Categories.Where(x => x.Parent == null);

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

Is there any way to create a translatable property (LINQ to SQL) without doing .ToList()?

EDIT: I want to avoid touching Model.edmx because the database often changes during development and the .edmx often needs to be recreated

Answer 1

If you ask if it's possible to create a property with any C# code in the getters/setters and later have it understood by standard LINQ to Entities - then no, it can't be done. C# is much more expressive then SQL and it's unreasonable to expect Entity Framework to act as a general C# to SQL translator.

You can work around this is many cases though, see Using a partial class property inside LINQ statement for an example.

Update

It'd help if you told us what exactly you want to achieve, but here's an example:

public partial class Category
{
    public static Expression<Func<Category, bool>> ParentIsNullExpression
    {
        get 
        {
            return c => c.Category1 == null;
        }
    }
}

And then

var categs = ctx.Categories.Where(Category.ParentIsNullExpression);

All sorts of manipulations on Expressions are possible, some of them are supported by EF and as such translate to SQL.