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?

  • Have you tried creating a view with grouped results and query the view instead? – Aveenav Apr 24 '14 at 22:11

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);
  • I use GroupBy because i want get first item of each group such as "Group By" in LINQ.And I think we have performance problem if we fetch all items and then group by them,because of that we use paging. – Khosro Apr 24 '14 at 12:13
  • If you are going to use limit anyway because of your paging I doubt there will be any performance issue. I at least don't think I see much difference between GroupBy().Select() and OrderBy() which is basically what you get when the result from SPQuery is flat anyway – eirikb Apr 24 '14 at 12:25
  • Ok.I have a question : does "items.OfType<SPListItem>().GroupBy(item => item["Plan2PlanIdea"]);" loads items to memory or it is queried to SharePoint List to group items? – Khosro Apr 24 '14 at 12:33
  • It should simply group the items already returned from your SPQuery. And if you limit this to 10 items as you mentioned in your question then it should be rather trivial – eirikb Apr 24 '14 at 12:37
  • I think the difference between using "Group by" in SPQuery and after getting items from SPQuery is that the latter one loads all items to memory but the first on uses SQL query on database and the retrieve only 10 of items.But maybe i am wrong. – Khosro Apr 24 '14 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.