LINQ on Splistitemcollection Query on people picker

asked 2021-04-21T06:52:26.643+00:00
Prajakta Patil 21 Reputation points

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.

SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,344 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2021-04-22T07:43:58.08+00:00
    Michael 17,861 Reputation points

    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}");  
                }  
    

    90238-image.png

    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()  });  
    

    90165-image.png

    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.