Hi I've been reading about group by in SharePoint 2013 CSOM from this article here. https://piyushksingh.com/2016/11/21/retrieve-grouped-listitems-sharepoint
I've recently upgraded to 2013 and though to give his example a go. All seems to work fine however when I switched from a standard field to a lookup field then I got duplicates.
My code.
CamlQuery test = new CamlQuery();
test.ViewXml = @"<View Scope='Recursive'>
<Query>
<GroupBy Collapse='TRUE' GroupLimit='300'>
<FieldRef LookupId='TRUE' Name='Doc_x002e__x0020_Type_x003A_Section'/>
</GroupBy >
<Where>
<And>
<Eq>
<FieldRef Name='RiO_x0020_ID' />
<Value Type='Text'>1000002</Value >
</Eq>
<Neq>
<FieldRef Name='_Status' />
<Value Type='Text'>Hidden</Value>
</Neq>
</And >
</Where >
</Query>
<ViewFields>
<FieldRef Name='Doc_x002e__x0020_Type_x003A_Section' />
</ViewFields>
<RowLimit paged='TRUE'>30</RowLimit>
<Aggregations >
<FieldRef Name='Doc_x002e__x0020_Type_x003A_Section' Type='COUNT'/>
</Aggregations>
</View>";
test.FolderServerRelativeUrl = "/Drafts/Documents/1000002";
//Idea for aggregations
var l = _sp.Web.Lists.GetByTitle('Documents');
var q = l.RenderListData(test.ViewXml);
_sp.ExecuteQuery();
return Content(q.Value);
And some of the response.
[{
"Doc_x002e__x0020_Type_x003A_Section": "Advanced Decisions\u002fStatements",
"ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')",
"Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23Advanced%20Decisions%2FStatements%3B%23",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "1",
"Doc_x002e__x0020_Type_x003A_Section.newgroup": "1",
"Doc_x002e__x0020_Type_x003A_Section.groupindex": "1_",
"Doc_x002e__x0020_Type_x003A_Section.COUNT": "140",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "1"
}
,{
"Doc_x002e__x0020_Type_x003A_Section": "AHP - Acorn Further Education Centre",
"ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')",
"Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Acorn%20Further%20Education%20Centre%3B%23",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "3",
"Doc_x002e__x0020_Type_x003A_Section.newgroup": "1",
"Doc_x002e__x0020_Type_x003A_Section.groupindex": "2_",
"Doc_x002e__x0020_Type_x003A_Section.COUNT": "140",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "3"
}
,{
"Doc_x002e__x0020_Type_x003A_Section": "AHP - Acorn Further Education Centre",
"ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')",
"Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Acorn%20Further%20Education%20Centre%3B%23",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "3",
"Doc_x002e__x0020_Type_x003A_Section.newgroup": "",
"Doc_x002e__x0020_Type_x003A_Section.groupindex": "2_",
"Doc_x002e__x0020_Type_x003A_Section.COUNT": "140",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "3"
}
,{
"Doc_x002e__x0020_Type_x003A_Section": "AHP - Arts and Music Therapies",
"ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')",
"Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Arts%20and%20Music%20Therapies%3B%23",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "10",
"Doc_x002e__x0020_Type_x003A_Section.newgroup": "1",
"Doc_x002e__x0020_Type_x003A_Section.groupindex": "3_",
"Doc_x002e__x0020_Type_x003A_Section.COUNT": "140",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "10"
}
,{
"Doc_x002e__x0020_Type_x003A_Section": "AHP - Arts and Music Therapies",
"ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')",
"Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Arts%20and%20Music%20Therapies%3B%23",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "10",
"Doc_x002e__x0020_Type_x003A_Section.newgroup": "",
"Doc_x002e__x0020_Type_x003A_Section.groupindex": "3_",
"Doc_x002e__x0020_Type_x003A_Section.COUNT": "140",
"Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "10"
}