Get grouped items by "Group by" and applied pagination

Question

I use "Group by" CAML element to group items and also want to applied pagination(10 first grouped items) to SPQuery. But SPList.GetItems(spquery) returned 10 items without grouping them. My code is as follow :

        SPQuery spquery = new SPQuery();

        SPList spList = ListDAL.GetListByName("PlanIdeas", Constants.EideUrl);

        string query = @"<GroupBy Collapse='TRUE'  ><FieldRef Name='Plan2PlanIdea' /></GroupBy>";

        spquery.Query = query;

        if (rowLimit != 0)
        {
            spquery.RowLimit = rowLimit;
        }

        if (pageInfo != null && !pageInfo.Equals(""))
        {
            SPListItemCollectionPosition pos = new SPListItemCollectionPosition(pageInfo);
            spquery.ListItemCollectionPosition = pos;
        }

        SPListItemCollection items = spList.GetItems(spquery);

        foreach (SPListItem item in items)
        {
            var d = new SPFieldLookupValue(item["Plan2PlanIdea"].ToString()).LookupId;
        }

How to achieve this by SPQuery and CAML?

Answer 1

When you say you want it grouped, I suspect you mean you want something like IEnumerable<IGrouping<something, SPListItem>>? I don't think that SPQuery can do this by itself (CAML support this because of visual aspects I think), but what benefit do you get from using CAML for this?

Wouldn't ordering/sorting them in the CAML query result in the same as grouping, if the layout is flat anyway? So just add something like:

<OrderBy><FieldRef Name='Plan2PlanIdea' Ascending='FALSE' /></OrderBy>

I imagine that doing the grouping after the query wouldn't be that much slower, you can do something like this:

var grouped = items.OfType<SPListItem>().GroupBy(item => item["Plan2PlanIdea"]);

If you want to group by the LookupId you could do something like this:

var grouped = items.OfType<SPListItem>()
  .GroupBy(item => new SPFieldLookupValue("" + item["Plan2PlanIdea"]).LookupId);