Creating a property that LINQ to Entities can translate

Question

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

I have a Category table / class that has a ParentId column associated with itself (so the category may have subcategories)

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

For clarity, I created another property.

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

The problem is that it works

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

but it does not work

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

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

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

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

Answer 1

If you ask whether it is possible to create a property with any C # code in getters / setters, and then understand it with the standard LINQ to Entities, then no, this is not possible. C # is much more expressive than SQL, and it is unreasonable to expect the Entity Framework to act as a generic C # translator to SQL.

You can get around this a lot, but see Using the partial class property inside a LINQ statement for an example.

Update

This will help if you tell us exactly what you want to achieve, but here is 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 kinds of manipulations with expressions are possible, some of them are supported by EF and, as such, are translated into SQL.

Answer 2

in your entity data model design (.edmx file), you can rename Category1 to Parent

Answer 3

You have 4 options:

• Use code first
• Add it to the EDMX constructor
• Add your property to the CSDL section in EDMX, add the column to the SSDL section in EDMX, and then map them to each other in the EDMX mapping section.
• Bring the query into memory using .ToList (), then use LINQ instead of LINQ for Entities.