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 ?

Answer 1

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

Answer 2

We can also use LINQ to filter results.

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

var items = listItems.Where(i => i.FieldValues["FileLeafRef"].ToString().Equals("1.txt")||i.FieldValues["FileLeafRef"].ToString().Equals("2.txt")).ToList();

To build lambda Expressions dynamically, check the article below:

Build Lambda Expressions Dynamically