How to make Linq join using Group by on 2 different list of sharepoint

Jan 4 2021 1:50 PM
 
I have made join between two different list .
Problem: Group by is not working
 
Solution Required : how to get data using group by 
attached is the image of code
 
 
My Code:
 
SPQuery querycountry = new SPQuery();
SPList lstcountry = web.Lists[lst_usercountrydetails];
querycountry.Query = @"<Where><And><Eq><FieldRef Name=UserFk LookupId='True'/><Value Type='Lookup'>" + userID + "</Value></Eq><And><Eq><FieldRef Name='CountryFk' LookupId='True'/><Value Type='Lookup'>" + 18 + "</Value></Eq><Eq><FieldRef Name='Flag' /><Value Type='Number'>" + 1 + "</Value></Eq> </And></And></Where>"; ;
SPListItemCollection oitmcountry = lstcountry.GetItems(querycountry);
SPQuery querydossier = new SPQuery();
SPList sPlstapplied = web.Lists[lst_appliedproducts];
querydossier.Query = "<Where><And><Eq><FieldRef Name ='Flag'/><Value Type='Number'>" + 1 + "</Value></Eq><Neq><FieldRef Name ='DossierType'/><Value Type='Number'>" + 1 + "</Value></Neq></And></Where><OrderBy><FieldRef Name='ID' Ascending='False'/></OrderBy>";
SPListItemCollection oitmdossier = sPlstapplied.GetItems(querydossier);
var dossiserAll = from SPListItem itmdossier in sPlstapplied.GetItems(querydossier)
join SPListItem itmcountry in lstcountry.GetItems(querycountry)
on Convert.ToInt32(Convert.ToString(itmdossier[p_CountryFk]).Split(';')[0].ToString()) equals Convert.ToInt32(itmcountry[p_CountryFk].ToString().Split(';')[0].ToString())
// equals new SPFieldLookupValue(itmcountry[p_CountryFk] as string).LookupValue
group itmdossier by new { resDosParent = Convert.ToInt32(itmdossier[_DossierParentID])} into dossierRegisterFinal
// group itm by new { grp_dossierParent = Convert.ToInt32(itm["DossierParentID"]) } into gDosser
select new { m_registerFinaloutput = dossierRegisterFinal,m_lstREgisterDossier= dossierRegisterFinal.ToList() };
foreach (var oitm in dossiserAll)
{
if (oitm.m_registerFinaloutput.Count() > 0)
foreach (var childitem in oitm.m_lstREgisterDossier)
{
if (!childitem[_DossierType].Equals(1))
{
countryID = +';' + Convert.ToString(childitem[_DossierParentID]);
oDossier.registercount = +Convert.ToInt32(childitem[_DossierParentID]);
}
}
}
 

Answers (2)