Something like a VLOOKUP

Question

I'm attempting to merge two lists of different objects where a specific field (employeeID) is equal to a specific field[0,0] in another list. My code looks like this:

int i = Users.Count() - 1;
int i2 = oracleQuery.Count() - 1;
for (int c = 0; c <= i; c++)
{
    for (int d = 0; d <= i2; d++)
    {
        if (Users[c].getEmployeeID().ToString() == oracleQuery[d][0,0].ToString())
        {
            Users[c].setIDMStatus(oracleQuery[d][0,1].ToString());
        }
    }
}

This works... but it doesn't seem efficient. Any suggestions for more efficient code that will ultimately lead to the Users list containing the new information from the oracleQuery list?

Answer 1

You could use a join with Enumerable.Join:

var matches = Users.Join(oracleQuery, 
                         u => u.getEmployeeId().ToString(), 
                         oq => oq[0,0].ToString(), 
                         (u,oc) => new { User = u, Status = oc[0,1].ToString() });

foreach(var match in matches)
    match.User.setIDMStatus(match.Status);

Note that you could eliminate the ToString() calls if getEmployeeId() and the oracleQuery's [0,0] element are of the same type.

Answer 2

The only thing I notice as far as efficiency is that you use the Enumerable.Count() method, which enumerates the results before you loop through again explicitly in your for loops. I think the LINQ implementation will get rid of the pass through the results to count the elements.

I don't know how you feel about using LINQ QUERY EXPRESSIONS, but this is what I like best:

var matched = from user in Users
              join item in oracleQuery on user.getEmployeeID().ToString() equals item[0,0].ToString()
              select new {user = user, IDMStatus = item[0,1] };

foreach (var pair in matched) 
{
     pair.user.setIDMStatus(pair.IDMStatus);
}

You could also use nested foreach loops (if there are multiple matches and set is called multiple times):

foreach (var user in Users) 
{
     foreach (var match in oracleQuery.Where(item => user.getEmployeeID().ToString() == item[0,0].ToString()) {
         user.setIDMStatus(match[0,1]);
     }
}

Or if there will only be one match for sure:

foreach (var user in Users) 
{
    var match = oracleQuery.SingleOrDefault(item => user.getEmployeeID().ToString() == item[0,0].ToString()); 
    if (match != null) {
        user.setIDMStatus(match[0,1]);
    }
}

I don't think there is any real efficiency problem in what you've written, but you can benchmark it against the implementation in LINQ. I think that using foreach or a Linq query expression might make the code easier to read, but I think there is not a problem with efficiency. You can also write the LINQ query expression using LINQ method syntax, as was done in another answer.