1

I have an Entity class that I have extended using the partial class syntax to have some derived properties. I would like to perform a query using the IQueryable<T> interface that uses the information from these fields, but I currently get an exception that states

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

Here are the relevant code snippets. You may assume that the Entity object has a String member named Id. 

public partial class MyEntityObject
{
   public String Title { get { return MyStrings.ResourceManager.GetString(Id) ?? ""; } }
}

/**
 * Throws exception trying to sort on the 'Title' field of a 'MyEntityObject'
 */
public IEnumerable<T> Query<T>(String fieldName, int low, int high)
{
   // Get the ObjectContext<T> using a Repository pattern
   var query = context.GetRepository<T>()

   // Create an OrderBy clause based on the field by dynamically building an expression tree
   //  see http://stackoverflow.com/questions/4546463/help-with-linq-and-generics-using-getvalue-inside-a-query
   PropertyInfo propertyInfo = typeof(T).GetProperty(fieldName);

   ParameterExpression e = Expression.Parameter(typeof(T), "e");
   MemberExpression mexpr = Expression.MakeMemberAccess(e, propertyInfo);

   IQueryable<T> sortedQuery = query.OrderBy(Expression.Lambda<Func<T,Object>>(mexpr, e));
   return sortedQuery.Skip(low).Take(high - low + 1).AsEnumerable();
}

As a last resort I can always do the AsEnumerable() before the Skip and Take, but that will involve retrieving all of the objects from the database even if the selection can be done by SQL.

Perhaps the best alternative is to use reflection to check that the object property has a DataMemberAttribute and then chose to do either query.OrderBy().Skip().Take().AsEnumerable() or query.AsEnumerable().OrderBy().Skip().Take()?

Lucas
  • 8,035
  • 2
  • 32
  • 45

1 Answers1

3

No you can't because linq-to-entities query is just expression tree translated to SQL. To translate the tree to SQL your entity mapping is used and because Title is not in your mapping it will throw exception.

Calling AsEnumerable before Skip and Take sounds like very bad design because it will always transfer content of whole data table to your application. This will be very slow and in case of big data tables also useless.

If this is a problem of localization your database must contain localized strings to make this effective. In the same time if you need to do this only with few records you can load all of them.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • The data sets are expected to be small (low hundreds of records, at most), so loading all of the items is an acceptable compromise. – Lucas Apr 13 '11 at 19:41
  • Hundreds of records doesn't sound like small data. If multiple clients requests these data in the same time it will be a lot of data. Moreover for EF context houndreds of records is a lot because of materialization default tracking etc. These queries still can be slow. – Ladislav Mrnka Apr 13 '11 at 19:45
  • These records are being passed directly to a web client via a Dojo JsonRestStore, so we're operating in a detached mode. Is there a way to ask EF to not create tracking information and just return the objects in a detached state? – Lucas Apr 13 '11 at 19:51
  • 1
    Yes. You must first get `ObjectQuery` from `ObjectSet` and set `MergeOption` to `MergeOption.NoTracking`. – Ladislav Mrnka Apr 13 '11 at 20:01
  • Has anyone found a workable solution for this sort of problem. The reason I would like one is to abstract certain logic away from the linq statement i.e. so I don't have business logic repeated all over the place. I have been thinking about a solution for this for ages.... – GarethReid May 16 '13 at 04:41
  • @GarethReid Your comment is very old, but [this](https://github.com/ldiego08/ExpressionFramework/tree/master/ExpressionFramework.Projections) put together by [u/Luis Aguilar](http://stackoverflow.com/users/543712/luis-aguilar) was very helpful. – user326608 Dec 08 '15 at 05:48