Problems With Entity Framework Queries

Question

I write a Class, it's has some query. attention, the whereitem is a little class, its Body member include a Expression<Fun<T,bool>> lambda expression is used to filter the query

public override List<T> Find<T>(int start, int len = 10, IWhereItem<T> whereitem = null, IOrderItem<T> orders = null, bool isDetach = true)
{
  var context = getInstence();
  var edminfo = EdmMgr[typeof (T)];//EdmMgr Include some dataservice infomation
  ObjectQuery<T> query = context.CreateQuery<T>("[" + edminfo.TableName + "]");
  if (whereitem != null && whereitem.Body != null)
    query = query.Where(whereitem.Body).AsObjectQuery();
  string orderString = orders == null ? "it." + edminfo.KeyName + " asc" : orders.OrderString;
  List<T> result = start == -1
             ? query.OrderBy(orderString).ToList()
             : query.OrderBy(orderString).Skip(start).Take(len).ToList();
  //......and else
  return result;
}

and then, when i run it, I get a error, it said "LINQ to Entities query does not support the query builder method. For more information, please refer to the Entity Framework documentation."

so i change my code.

public override List<T> Find<T>(int start, int len = 10, IWhereItem<T> whereitem = null, IOrderItem<T> orders = null, bool isDetach = true)
{
  var context = getInstence();
  var edminfo = EdmMgr[typeof (T)];
  ObjectQuery<T> query = context.CreateQuery<T>("[" + edminfo.TableName + "]");
  query = MemerVisitor.IncludeMembers.Aggregate(query, (current, str) => current.Include(str));
  string orderString = orders == null ? "it." + edminfo.KeyName + " asc" : orders.OrderString;
  List<T> result = query.OrderBy(orderString).Skip(start).Where(whereitem.Body).Take(len).ToList();
  return result;
}

so. it's ok, but is ugly,right? the WHERE is after ORDERBY. when i change their location, I get a error. Type isn't match. What other good way?

==================Additional==============================

In order to facilitate the reception call this method passing a string (e.g. "it.UserId desc") over the OrderItem sort statement, in fact WhereItem is Expression > the package, my question is not whether to implement the filter is entityframework statement, but can split chain operation, because if the first implementation of the Where in the query after he converted to IQueryObject not ObjectQuery type, so once cast, after the implementation of Orderby an error. Such as:

      using (DataService<User> db = new DataService<User>())
            {
                user = db.Find(x => x.Moreinfo.CopSchool.CopSchoolId == 13&& x.Role.Title.Equals("xxxx")).FirstOrDefault();
            }

DataService that I wrapped up the class. The equivalent of a DAO. But is realized EF. Some queries can be passed, but some are not. The reasons are not clear. But certainly because of the type conversion caused. I'm using. Net 3.5sp1 vs2010 default entity data model (. Edmx)

If I direct the implementation of query.OrderBy (xxx). Skip (xx). Where (xxx). Take (xx). ToList () to return results, but there are some problems, he is the first order filter obtained in the skip last xx months .

I would like to query.Where (XX). OrderBy (xxx). Skip (xx). Take (xx). ToList ()... but can not be performed because the Where (Expression) to return but not ObjecteQuery IEnumerable (parameter type Expression of the time) or IQueryable (parameter of type Func time). Where can only be placed on the back, while Skip to call after they have OrderBy, Where so awkward in the middle of ...

Answer 1

Here's what I believe is happening:

you can't apply the .Where(whereitem.Body) to the query because it contains a custom function expression that the Entity Framework does not know how to translate into SQL to execute on the database.

when you put the .Where(whereitem.Body) after the.OrderBy(orderString).Skip(start) it works fine because calling .OrderBy(orderString).Skip(start) has caused the sql to execute and has returned an in memory IEnumerable that you can now execute your .Where(whereitem.Body) on that in memory collection. it doesn't need to translate that expression to sql anymore, but executes it as CLR. you could use a simple lambda expression in the Where that is translatable to SQL, or you can force the sql to evaluate earlier by doing query.AsEnumerable().Where(...) ofcourse this will load a lot more results from the db into memory before performing the filtering.

also, filtering after skip and take will give you different results than filtering first.

On second thought, what you really need is this:

Find<T>(..., Func<TEntity,bool> predicate, ...)
{
...
query.Where(predicate)...


}

you need to pass the lambda expression as a simple predicate, i believe that should let it be translatable to SQL. (ofcourse given that the predicate consists of simple expressions that are themselves translatable by EF to sql.)

Answer 2

I have find the way.

http://www.codeproject.com/KB/linq/IEnumerableSortByName.aspx?msg=3005452#xx3005452xx

we can use the extend method SortEngine

private static IOrderedEnumerable<T> SortEngine<T>(this IEnumerable<T> source, string columnName, bool isAscending, bool started)
{
    var item = Expression.Parameter(typeof(T), "item");
    var propertyValue = Expression.PropertyOrField(item, columnName);
    var propertyLambda = Expression.Lambda(propertyValue, item);
    // item => item.{columnName}

    var sourceExpression = Expression.Parameter(typeof(IEnumerable<T>), "source");

    string methodName;
    Expression inputExpression;
    if (started)
    {
        methodName = isAscending ? "ThenBy" : "ThenByDescending";
        inputExpression = Expression.Convert(sourceExpression, typeof(IOrderedEnumerable<T>));
        // ThenBy requires input to be IOrderedEnumerable<T>
    }
    else
    {
        methodName = isAscending ? "OrderBy" : "OrderByDescending";
        inputExpression = sourceExpression;
    }

    var sortTypeParameters = new Type[] { typeof(T), propertyValue.Type };
    var sortExpression = Expression.Call(typeof(Enumerable), methodName, sortTypeParameters, inputExpression, propertyLambda);
    var sortLambda = Expression.Lambda<Func<IEnumerable<T>, IOrderedEnumerable<T>>>(sortExpression, sourceExpression);
    // source => Enumerable.OrderBy<T, TKey>(source, item => item.{columnName})

    return sortLambda.Compile()(source);
}

public static IOrderedEnumerable<T> OrderBy<T>(this IEnumerable<T> source, string columnName)
{
    return SortEngine(source, columnName, true, false);
}

public static IOrderedEnumerable<T> OrderByDescending<T>(this IEnumerable<T> source, string columnName)
{
    return SortEngine(source, columnName, false, false);
}

public static IOrderedEnumerable<T> ThenBy<T>(this IOrderedEnumerable<T> source, string columnName)
{
    return SortEngine(source, columnName, true, true);
}

public static IOrderedEnumerable<T> ThenByDescending<T>(this IOrderedEnumerable<T> source, string columnName)
{
    return SortEngine(source, columnName, false, true);
}