I am updating records on a SharePoint list based on data from a SQL database. Lets say my table looks something like this:

There can be multiple keys in each table. I am trying to make a generic solution that will work for multiple different table structures. In the above example, VendorNumber and ItemNumber would be considered keys.

I am able to retrieve the SharePoint lists as c# List<Microsoft.SharePoint.Client.ListItem>

I need to search through the List to determine which individual ListItem corresponds to the current SQL datarow I am on. Since both ListItem and DataRow allow bracket notation to specify column names, this is pretty easy to do using LINQ if you only have one key column. What I need is a way to do this if I have anywhere from 1 key to N keys. I have found this solution but realize it is very inefficient. Is there a more efficient way of doing this?

List<string> keyFieldNames = new List<string>() { "VendorNumber", "ItemNumber" };
List<ListItem> itemList = MyFunction_GetSharePointItemList();
DataRow row = MyFunction_GetOneRow();

//this is the part I would like to make more efficient:
foreach (string key in keyFieldNames)
{
     //this filters the list with each successive pass.
     itemList = itemList.FindAll(item => item[key].ToString().Trim() == row[key].ToString().Trim());
}

Edited to Add: Here is a link to the ListItem class documentation: Microsoft.SharePoint.Client.ListItem

While ListItem is not a DataTable object, its structure is very similar. I have intentionally designed it so that both the ListItem and my DataRow object will have the same number of columns and the same column names. This was done to make comparing them easier.

CodePudding user response：

A quick optimization tip first:

• Create a Dictionary<string, string> to use instead of row

  List<string> keyFieldNames = new List<string>() { "VendorNumber", "ItemNumber" };
  DataRow row = MyFunction_GetOneRow();
  var rowData = keyFieldNames.ToDictionary(name=>row[name].ToString().Trim());
  foreach (string key in keyFieldNames)
  {
     itemList = itemList.FindAll(item => item[key].ToString().Trim() == rowData[key]);
  }
  

This will avoid doing the ToString & Trim on the same records over & over. That's probably taking 1/3rd to 1/2 the time of the loop. (The comparison is fast compared to the string manipulation)

Beyond that, all I can think of is to use reflection to build a specific function, on the fly to handle the comparison. BUT, that would be a big effort, and I don't see it saving that much time. Basically, whatever you do, will still have to do the same basics: Lookup the values by key, and compare them. That's what's taking the majority of the time.