Select all columns but group by only one in linq,How to Get only Id from Lookup field in LINQ

Question

question

MohammadQasim-1438 asked Sep 14, '20 MohammadQasim-1438 commented Sep 15, '20

Select all columns but group by only one in linq,How to Get only Id from Lookup field in LINQ

Greetings

I am using Sharepoint 2016 on Prem

I want to Select all columns but apply "group by" only one using LINQ ( retrieve data from Sharepoint list )

Problem : Currently having 2 problems/challenges

Problem 1: I have lookup field which returns id with name like "23;#abcd" , for this I have to split to get Id only ( mention in below code using split(';').

Problem 2: in LINQ query ,I want to select stautus and documentname columns from list not on "group by".
Group by is just for 1 column like we do in sql server like below

select id,stautus ,documentname,comments from abc groupby userFk

My Code:
oQuery.Query = "<Where><Eq><FieldRef Name='Flag'/><Value Type ='number'>" + 1 + "</Value></Eq></Where> ";

var resultfoldeDoc11 = (from SPListItem itm in pLis.GetItems(oQuery)
orderby itm["ID"]
group itm by new { m_folderID22 = itm["DocumentFolderFk"] } into g
select new { m_folderId22 = g.Key.m_folderID22.ToString().Split(';'), m_totaldocsfoldercount = g.Count() });

Solution Required:for Both Problem
How can I select multiple columns in select clause with one field in "Groupby " Clause ?
How can I get only ID from Look up field in LINQ

Thanks

office-sharepoint-server-development

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2020-09-15T03:11:03.24Z

Jerryzy answered Sep 15, '20 MohammadQasim-1438 commented Sep 15, '20

Hi @MohammadQasim-1438,

Change code like this:

 oQuery.Query = "<Where><Eq><FieldRef Name='Flag'/><Value Type ='number'>" + 1 + "</Value></Eq></Where> ";
    
    
 var resultfoldeDoc11 = (from SPListItem itm in pLis.GetItems(oQuery)
 orderby itm["ID"]
 group itm by new { m_folderID22 = itm["DocumentFolderFk"] } into g
 select new { m_folderId22 = g.Key.m_folderID22.ToString().Split(new char[] { ';', '#' })[0], m_totaldocsfoldercount = g.Count(), ListItemsData = g });
    
  foreach (var oitem in resultfoldeDoc11)
  {
           Console.WriteLine($"{oitem.m_folderId22}:{oitem.Total}");
           foreach (var item in oitem.ListItemsData)
           {
             //OutPut Other Columns here
             Console.WriteLine(item["Title"]);
           }
  }

ListItemsData = g will output the ListItem Collection object, then you can get any other fields using item["ColumnInternalName"] like the code sample above.

Reference:

Group By SPListItem in SharePoint ListItemCollection

If the Answer is helpful, please click "Accept Answer" and upvote it.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MohammadQasim-1438 · Sep 15, 2020 at 06:01 PM

Thanks, works fine

0 ·

question