” … is not supported in LINQ to Entities” – Partial Classes and EDMX

Just a quick observation, which might be useful to anyone using ASP.Net MVC 4 EF DB First via Entity Data Model

The code generator will look at the DB schema, and will create a set of classes representing (as closely as possible) the intended schema. Problem: if you have to foreign keys pointing to the same relation (e.g. Person table), you will end up with navigational properties of this sort: Class.Persons1, Class.Persons2 and so forth. This is not so handy/clean, as you’d want something more descriptive and intuitive, such as: Class.Teachers, Class.Students

1) After generating models via the edmx’s code generator, create partial classes to override the auto-generated properties (e.g. such as ICollection Persons1)

public partial class Class
{
public virtual ICollection Students
{
get { return this.Persons1; }
set { this.Persons1 = value; }

}

public virtual ICollection Teachers
{
get { return this.Persons2; }
set { this.Persons2 = value; }

}
}

2) This is not enough though. Linq-to-Entities is not able to translate Students in a ‘complex’ Linq query, and you’d need to help it do so by splitting the query in two. First load list of parent entities in memory (in this case Classes) using db.Classes.Where(c => c.SomeID == ID).ToList(); (filtered to the current context, rather than loading all tuples from the Class table as entities in memory), then in the second part of the query (another call to DB context) carry on with the required linq expression using the newly created navigational properties. This bypasses the need for Linq-to-Entities to resolve property names (declared in partial classes) in terms of DB queries (which would causes an error – “The specified type member ‘Students’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties”.

3) Personal observation on Entity Framework and Code First vs Database First (after refactoring a whole project to Database First) – don’t use Code First. It’s a waste of time and energy. Really and truly, reasoning on your data entities (without writing a line of code) helps you understand potential issues in the overall processes, while giving you a clearer design direction.

I’m writing this as a personal note (so there might be errors in judgement/text), however I feel that it is worth sharing!

Chris

Leave a Reply