Search ListItemCollection for ListItem with specific field value C#

Question

this relates to SharePoint Online and a Console Application in C#

I have an app which collects a list of all Employees from a SQL table, I then want to check each employee number for a matching record in two SharePoint lists. using the CamlQuery.CreateAllItemsQuery() I can get all items from each list into a variable. What is the best way to then look through all those items for an item which has a specific value in a field? should I just enumerate all items or is there a better way?

I know I can use CamlQuery to search for a single item but this is highly inefficient when I am making a call to two SharePoint lists for 12,000 SQL records.

Is there something like ListItemCollection.Contains() which takes a field name and string to search?

EDIT 1

I found out that I could use threading and Task.WhenAny() method to run a search simultaneously in both lists returning the matching list item and ending the searching when an Item was found (Each Item is Unique and only appears in one list) this sped things up slightly

EDIT 2

Then I remembered I could just cast my ListItemCollection to a Dictionary<string, ListItem> and use TryGetValue(string key, out listItem) This appears to be the fastest method I have found so far, code below, is there a better way?

ListItemCollection sharePointActiveEmployees = SPContext.secureHr.GetAllListItems("Employees_Active");
ListItemCollection sharePointWithdrawnEmployees = SPContext.secureHr.GetAllListItems("Employees_Withdrawn");

// Cast ListItemCollection to Dictionary for Searching
Dictionary<string, ListItem> sharePointActiveEmployeesDictionary = sharePointActiveEmployees.Cast<ListItem>().ToDictionary(i => i["EmployeeNumber"].ToString(), i => i);
Dictionary<string, ListItem> sharePointWithdrawnEmployeesDictionary = sharePointWithdrawnEmployees.Cast<ListItem>().ToDictionary(i => i["EmployeeNumber"].ToString(), i => i);

ListItem result;
sharePointActiveEmployeesDictionary.TryGetValue(employee.EmployeeNumber, out result);
// If there is no result in Active, Search Withdrawn
if (result == null)
{
sharePointWithdrawnEmployeesDictionary.TryGetValue(employee.EmployeeNumber, out result);
}          

Thanks.

Answer 1

I found the most efficient method for speed and for minimal calls to SharePoint Online was the dictionary approach, final solution below:

// Use List Service to get Active/ Withdrawn lists
List activeEmployeesList = SPContext.employeeFiles.GetList("Employees_Active");
List withdrawnEmployeesList = SPContext.employeeFiles.GetList("Employees_Withdrawn");

// Get all current Active / Withdrawn Items
ListItemCollection sharePointActiveEmployees = activeEmployeesList.GetItems(CamlQuery.CreateAllItemsQuery());
SPContext.employeeFiles.ctx.Load(sharePointActiveEmployees, item => item.Include(i => i["Title"], i => i["Status"], i => i["EmployingEntity"], i => i["EmployeeNumber"]));
SPContext.employeeFiles.ctx.ExecuteQuery();

ListItemCollection sharePointWithdrawnEmployees = withdrawnEmployeesList.GetItems(CamlQuery.CreateAllItemsQuery());
SPContext.employeeFiles.ctx.Load(sharePointWithdrawnEmployees, item => item.Include(i => i["Title"], i => i["Status"], i => i["EmployingEntity"], i => i["EmployeeNumber"]));
SPContext.employeeFiles.ctx.ExecuteQuery();       

// Cast ListItemCollection to Dictionary for Searching
Dictionary<string, ListItem> sharePointActiveEmployeesDictionary = sharePointActiveEmployees.Cast<ListItem>().ToDictionary(i => (string)i["EmployeeNumber"], i => i);
Dictionary<string, ListItem> sharePointWithdrawnEmployeesDictionary = sharePointWithdrawnEmployees.Cast<ListItem>().ToDictionary(i => (string)i["EmployeeNumber"], i => i);

sharePointActiveEmployeesDictionary.TryGetValue(employee.EmployeeNumber, out result);