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

you need to create a navigation property from category to parent like foreign key — COLD TOLD, May 09 '12 at 02:51
Hi Cold Told, sorry I'm not sure what you mean. There is already a foreign key (Otherwise EF will not create Category1) — Aximili, May 09 '12 at 03:29
Have you actually looked at `Category1` in Model.Designer.cs and/or Reflector to see what attributes or other paraphernalia is added to its definition? — Mark Hurd, May 10 '12 at 02:51
Yeah but I don't understand it, I tried copying them but didn't work — Aximili, May 11 '12 at 01:44

score 2 · Accepted Answer · edited May 23 '17 at 12:10

2

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.

edited May 23 '17 at 12:10

Community

1
1

answered May 09 '12 at 08:43

Jacek Gorgoń

3,206
1
26
43

It can't be done in C#? But Model.Designer.cs itself (the CS file for EDMX) is C#, isn't it?? Are you referring to Properties returning Expressions from that link? I'll look at it. – Aximili May 10 '12 at 01:49
Yes, I was referring to the Expression Properties. It's not the property code that makes it work in Model.Designer.cs, it's the expression you use to access the property, e.g. ``c => c.Category``. EF knows that you want the ``Category`` property and translates that to ``Category`` column/table. – Jacek Gorgoń May 10 '12 at 06:53
Sorry I've never used Expression Properties. Are you saying it's possible that an Expression Property gets translated to SQL? Can you replace the Parent property in my question with an Expression Property so LINQ can translate it? Thanks Jacek – Aximili May 10 '12 at 07:17
Very interesting! Thank you Jacek for the complete answer! It's btw – Aximili May 11 '12 at 01:56

score 0 · Answer 2 · answered May 09 '12 at 03:48

0

in your Entity Data Model Designer (.edmx file), you can rename the Category1 to Parent

answered May 09 '12 at 03:48

Nikita Ignatov

6,872
2
34
34

That was just a very-simplified example. I was asking how to write a property, not renaming an existing one. Thanks anyway. – Aximili May 09 '12 at 06:18

score 0 · Answer 3 · answered May 09 '12 at 08:54

You have 4 options:

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

Creating a property that LINQ to Entities can translate

3 Answers3