LINQ on Splistitemcollection Query on people picker

Question

I want to loop through a list and find out same people picker field count. Menaing list A has people picker coulmn named "Manager". I want to loop thoughr all items in list A and want result in following format:

Manager X: count of list items in A having manager as A is 4
Manager Y: count of list items in A having manager as A is 9
Manager Z: count of list items in A having manager as A is 2.

I have used LINQ but it gives error when people picker has invalid user(empoyee left or not valid in AD).

int managercount= listitemColl.Cast <SPListItem>().where (p=>new SPFieldUserValue (oitem.Web,p["Manager"].ToStrimg()).User.ID == singleuservalue.User.ID).Count()

Here singleuservalue is value of ppl picker lookfield obtained while looping each list item.

Answer 1

Hi @Prajakta Patil ,

Below is my demo to use LINQ to get the result in the format you want:

            SPList list = web.Lists["Mylist"];  
            var result = (from SPListItem item in list.Items  
                          orderby item["Manager"]  
                          group item by new {p1= item["Manager"] } into g  
                          select new { managerName=g.Key.p1, total=g.Count()  });  
            foreach (var oitem in result)  
            {  
                Console.WriteLine($"Manger {oitem.managerName}:count of list items in A having manager as A is: {oitem.total}");  
            }  

And you could make some changes to item["Manager"] to get the manager name like this:

var result = (from SPListItem item in list.Items  
              orderby item["Manager"]  
              group item by new {p1= item["Manager"].ToString().Substring(item["Manager"].ToString().IndexOf("#")+1) } into g  
              select new { managerName=g.Key.p1, total=g.Count()  });  

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.