How to filter ListItemCollection sharepoint object model

Question

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
                            <Query>
                            </Query>
                        </View>";
camlQuery.FolderServerRelativeUrl = folder.ServerRelativeUrl;
ListItemCollection listItems = list.GetItems(camlQuery);
clientContext.Load(listItems);
clientContext.ExecuteQuery();

listItems is getting all 4 files I want to filter list using filename.if filename matches with database table file name then exclude that item from listItems

for example -

4 files - 1.txt 2.txt 3.txt 4.txt
in `database` table if `1.txt and 2.txt` is present then it will match with listItems filename and need to exclude these two items from listItems.

above is just an example I have 100's of file which I need to compare using filename and exclude from list if present into database table.

So we listItems is having only 2 items - 3.txt 4.txt

How can I achieve this without foreach loop ? is there anything I can use like LINQ or CamlQuery ?

Bassie Bassie · Accepted Answer · 2018-05-02T05:57:18

Try changing your caml query to something like this

@"<View Scope='RecursiveAll'>
    <Query>
    <Where>
        <Or>
            <Eq><FieldRef Name='FileLeafRef'/><Value>3.txt</Value></Eq>
            <Eq><FieldRef Name='FileLeafRef'/><Value>4.txt</Value></Eq>
        </Or>
    </Where>                            
    </Query>
</View>";

So you can query for items where the FileLeafRef field Equals 3.txt or 4.txt

But you should check which property contains the file name you are after. In my case I needed to change FileLeafRef to Title

So for a dynamic list of file names, you can append a new line for each filename before executing the query. Maybe something like

// Declare the query string
var queryString = "<View Scope='RecursiveAll'><Query><Where><or>";

// For each filename, append a new <eq> element containing the relevant details
foreach (var filename in fileNames) { 
    // Process string here, eg check if its in the db or whatever you need to do
    queryString += $"<Eq><FieldRef Name='FileLeafRef'/><Value>{filename}</Value></Eq>";
}

// Append the closing tags of the rest of the query
var queryString += "</or><Where></Query></View>";

How to filter ListItemCollection sharepoint object model

2 Answers