Entity Framework 5 performing full table scan

Question

I have the following code:

public List<anEntity> Get(int page, int pagesize, Func<anEntity, IComparable> orderby)
{            
    using (var ctx = new MyContext())
    {                
        return ctx.anEntity.OrderBy(orderby).Skip(pagesize * page).Take(pagesize).ToList();                    
    }           
}

When I check my database profile (SqlServer 2012) I can see an horrible full table scan without any "TOP" clause.

The interesting part:

If I do something similar but specifying a concrete orderby:

return ctx.anEntity.OrderBy(x => x.aField).Skip(pagesize * page).Take(pagesize).ToList();

The profile shows a beautiful "TOP" clause.

I would like to avoid having lot's of methods, one for each "orderby" possibility. Any hint would be very appreciated.

Answer 1

That's because your orderby parameter is a function rather than expression. There is no way to translate an arbitrary function into SQL, so all your data has to be on the client side before that function is called.

Change the parameter type to Expression<Func<anEntity, T>>, where T is a new generic parameter that you should add to your Get method, and it will work as expected.

Of course, you won't be able to use a custom IComparable, but there is no way around that: custom code cannot be translated into SQL.