Paginating a linq query which uses OrderBy

Question

I want to return a list of a certain entity grouped by a certain property, ordered descending by timestamp and paginated (using Skip and Take). What I got is this:

container.CoinMessageSet.Where(
                c => c.MessageState != MessageStateType.Closed &&
                     (c.DonorOperator.OperatorCode.Equals("opcode") ||
                      c.RecipientOperator.OperatorCode.Equals("opcode"))
                ).OrderByDescending(c => c.TimeStamp)
                 .GroupBy(c => c.Reference).Skip(x).Take(100);

Upon execution I got the Exception:

The method 'Skip' is only supported for sorted input in LINQ to Entities. 
The method 'OrderBy' must be called before the method 'Skip'.

...I called OrderBy() (albeit Descending) and I called it before Skip()! What am I missing?

Answer 1

You haven't ordered the groups; you need to do that before you can page. For example:

.GroupBy(c => c.Reference).OrderBy(grp => grp.Key).Skip(x).Take(100);

(you can also substitute OrderByDescending if you want the groups in reverse order)

Also: since you are grouping, the order in the original data is largely meaningless; you could probably remove the OrderByDescending(c => c.TimeStamp).

So net result:

var query = container.CoinMessageSet.Where(
            c => c.MessageState != MessageStateType.Closed &&
                 (c.DonorOperator.OperatorCode.Equals("opcode") ||
                  c.RecipientOperator.OperatorCode.Equals("opcode"))
            ).GroupBy(c => c.Reference).OrderBy(grp => grp.Key)
             .Skip(x).Take(100);

or possibly:

var query = (from c in container.CoinMessageSet
             where c.MessageState != MessageStateType.Closed &&
                  (c.DonorOperator.OperatorCode == "opcode" ||
                   c.RecipientOperator.OperatorCode == "opcode")
             group c by c.Reference into grp
             orderby grp.Key
             select grp).Skip(x).Take(100);