How to use Linq to query a property that is not in your database but in your class model

Posted on: 2012-03-29

One of my team mate would like to query some data with Linq but has always an error message saying that the Linq to Entity couldn't find the property desired. In fact, the exception was relevant because the property was made up in the model and were using two others properties available from the database.

To figure out more about the problematic let say that in the database you have the field : Id, Name_Fr, Name_En. Let say that in your model you have : Id, Name_Fr, Name_En and also Name. You would like to sort data by name. If you use directly your Entity Framework's data context to query with Linq To Entity you will get an error because database context will try to do a SQL query with the field Name which doesn't exist. The solution is to use Linq to Entity to query everything and to sort with Linq to Object.

So, instead of doing :

 MyDatabaseContext.MyPeople.OrderBy(c=>c.Name); 

You would have to do :

 MyDatabaseContext.MyPeople.AsEnumerable().OrderBy(c=>c.Name); 

The AsEnumerable() will execute the query which will return a collection of IEnumerable instead of IQueryable. The difference is that MyPeople is IQueryable which doesn't execute the query until the collection is enumerated (with ForEach for example) or until the query it transformed into a list (for example with ToList()).

The difference between AsEnumerable() and ToList() is concerning performance. The AsEnumerable() is faster if you do subsequent filtering (.Where for example) operation. The reason is that it will filter the query before looping instead of looping the whole collection and then looping to filter.