Get the list specific rows from sharepoint where value exist

Question

I am working in c# and I have a list of strings which contains the projectid of the SharePoint list

List<string> guidlist = new List<string>(); // contains project ids

On the basis of this list I want to retrieve the data from the SharePoint list. i.e To get all the rows from the SharePoint list where projectid exists in guidlist.

        var list = clientContext.Web.Lists.GetByTitle("testlist");

        CamlQuery query = new CamlQuery();
        query.ViewXml = "<View/>";
        ListItemCollection projects = list.GetItems(query);

        clientContext.Load(projects);

        clientContext.ExecuteQuery();

Currently I am just getting all the rows from the list how can I filter it to my needs. I am beginner and any help would be useful

Answer 1

You need to loop through the List of string to build dynamic CAML query and apply filter on it... Below might help to give you an idea

List<string> guidlist = new List<string>(); // contains project ids
string camlquery = "<View><Query><Where>";

// Make sure to update column name as per your list
camlquery += "<In><FieldRef Name='ProjectId' />"; 
camlquery += "<Values>";

foreach (var projectid in guidlist)
{
    camlquery += "<Value Type='Text'>" + projectid + "</Value>";
}

camlquery += "</Values></In></Where></Query></View>";

var list = clientContext.Web.Lists.GetByTitle("testlist");

CamlQuery query = new CamlQuery();
query.ViewXml = camlquery;
ListItemCollection projects = list.GetItems(query);
clientContext.Load(projects);
clientContext.ExecuteQuery();

Please note that above logic is using IN operator, you can also use OR operator and build similar dynamic query but IN operator is more preferred because of limitation of characters you can pass in CAML query. Also above will only work if your data returned from query is less than 5000 items(threshold value).

PS. check for syntax etc on above as just typed using notepad :)

Happy coding!!