So in typical tool fashion I posted this little gem without realizing a glaring error… the order by clause. The whole idea is to create a method that can get a collection, sort it, then grab a certain number for paging. The issue was this:
Expression<Func<K, IComparable>> orderBy
The problem comes in when the entire expression is run, meaning when Entity Frame work takes:
context.Select(selectMethod).Where(whereClause).OrderBy(orderBy).ToList();
and creates SQL out of it. Entity Framework doesn’t really know how to handle IComparable as it has not primitive/sql type to match to it. Why it can’t see the underlying type of say DateTime, no idea, but this is life.
So this should be an easy fix, right? Eh… yeah. First I thought instead of IComparable I could just convert to some kind of primitive type so that the EF could be on it’s merry. Not so much. Turns out this probably isn’t possible.
Well thanks to a push from Ben M at The O Flow I got to thinking about how to attack this. Instead of sending in an expression for the order by, why not send in a method that would take in a query and tell it how to order itself. Sounds hard right? (If not then you’re obviously too cool for this school) Well it’s not, just a different way to think about it.
Right off the bat, the change to the method signature would look like this:
Old order by parameter signature:
Expression<Func<K, IComparable>> orderBy
New order by parameter signature:
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy
So what does that mean? It means that I am going to supply the method with a method that will take in a query and return an ordered query… well not ordered yet per se but the blueprint on how to order when that time comes around. Now here’s how that’s used:
First you need the query:
var initialQuery = query
.Where
(
somethingEqualsSomething
);
Then you apply the orderby method, and in the case of the original paging method, the paging too:
var orderedQuery = orderBy(initialQuery);
returnValue = orderedQuery
.Skip(numberToShow * realPage)
.Take(numberToShow)
.ToList();
So overall, doesn’t look too much different. Just instead of supplying the OrderBy method with a Func, you give a method that creates an ordered query.
How would you use this? Remember the signature was (whereClause, selectClause, orderBy, pageNumber, numberToShow, realPage, totalCountOfPages)
Tools.GetListForGrid
(
tool => tool.EntityId == userId,
tool => new { Name = tool.Name }, //Select Clause, I'll get to that next
toolOuter => toolOuter.OrderBy(toolInner => toolInner .Name), //OrderBy
...
)
Two things you might notice. One would be the OrderBy signature:
toolOuter => toolOuter.OrderBy(toolInner => toolInner .Name),
What the hell? Remember the method you are sending takes in a query and returns an ordered query. toolOuter is your query, toolOuter.OrderBy(toolInner => toolInner .Name) is your blueprint (IOrderedQueryable) on how it should be queried.
Second thing is that when I showed how to use the OrderBy method above:
var orderedQuery = orderBy(initialQuery);
returnValue = orderedQuery
.Skip(numberToShow * realPage)
.Take(numberToShow)
.ToList();
I didn’t include the select clause. Partially because I didn’t want to complicate it yet, partially because it has it’s own issue. If you’re like me, you use the select clause a lot. Why? Because it limits the amount of information you take from the database. Say if you are trying to fill a drop down list, why select an entire Tool object (Which could have a ton of mapped properties) when all you need is Id and Name? Seems silly. That’s where the Select clause comes in. Now the issue is where to put the order by. You would think after the select clause, since you want to sort on only what you are selecting. Problem is, with paging that gets screwed up. The way sql server “pages” is that it selects twice.
Select all the things that match this where clause.
Select a certain number of items from that starting at X.
The first select creates a dataset with row numbers, and the second one selects the items based on those row numbers. IE take 5 starting at row 5. Now the way the EF handles the order by is to grab the info you need from the Select (Ordered by something… you don’t get to choose) and THEN order and grab. As you can guess this may not give the needed results. So how can this be solved? Order before the select as witnessed in the new and improved method:
public static IList<K> GetListForGrid<T, K>
(
this ObjectQuery<T> query,
Expression<Func<T, Boolean>> somethingEqualsSomething,
Expression<Func<T, K>> selectClause,
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy,
Int32 pageNumber,
Int32 numberToShow,
out Int32 realPage,
out Int32 totalCountOfPages
)
{
IList<K> returnValue;
Int32 totalItemCount =
query
.Count
(
somethingEqualsSomething
);
totalCountOfPages = Methods.TotalCountOfPages(totalItemCount, numberToShow);
realPage = Methods.GetRealPage(totalCountOfPages, pageNumber);
var initialQuery =
query
.Where
(
somethingEqualsSomething
);
var orderedQuery = orderBy(initialQuery);
returnValue = orderedQuery
.Select
(
selectClause
)
.Skip(numberToShow * realPage)
.Take(numberToShow)
.ToList();
return returnValue;
}
And usage:
Tools.GetListForGrid
(
tool => tool.Id == userId,
tool => new { Name = tool.Name }, //Select Clause
toolOuter => toolOuter.OrderBy(toolInner => toolInner .Name), //OrderBy
pageNumber,
amountToShow,
out realPage,
out totalCountOfPages
);
Now this one actually works with Entity Framework and not just Linq to objects like the last one.
