Fastest way to compare 2 lists objects : <p>I allow a user to download some data to csv. They can then edit some columns and then upload it back. I need a speed efficient way to compare certain columns between like objects to see what changed.</p>

<p>Currently I pull the original data from the DB and make it a list so it's all in memory. There is about 100k items so it's not that bad. That part takes less than a second. Then I load in the csv file and put it to list. Both lists have the same class type.</p>

<p>Then I loop over the csv data (as they probably removed some rows which they didn't change but they could still have changed a lot of rows). For each row in the csv list I query the list that came from the DB to find that object. Now I have the csv object and the object from the database as the same structure. Then I run it through a custom object compare function that looks at certain columns to see if anything changed.</p>

<p>If something did change I have to validate what they entered is a valid value by query another reference list for that column. If it's not valid I write it out to an exceptions list. At the end if there are no exceptions I save to db. If there are exceptions I don't save anything and I show them the list of errors.</p>

<p>The detail compare provides a list of columns and the old vs new values that changed. I need this to query the reference list to make sure the new value is valid before I make the change. It's fairly inefficient but it gives great detail to the user about what may be an issue with an upload which is very valuable.</p>

<p>This is very slow. I'm looking for ways to speed it up while still being able to give the user detailed information about why it may have failed so they can correct it.</p>

<pre><code>// get all the new records from the csv
            var newData = csv.GetRecords&lt;MyTable&gt;().ToArray();

            // select all data from database to list
            var origData = ctx.MyTable.Select(s =&gt; s).ToList();

            // look for any changes in the new data and update the database. note we are looping over the new data so if they removed some data from the csv file it just won't loop over those and they won't change
            foreach (var d in newData)
            {
                // find data so we can compare between new (csv) and current (from db) to see what possibly changed
                var oData = (from o in origData
                             where o.id == d.id
                             select o).FirstOrDefault();

                // only the columns in the updatableColumns list are compared
                var diff = d.DetailedCompare(oData, comparableColumns.ToList());
                if (diff.Count &gt; 0)
                {
                    // even though there are differences between the csv record and db record doesn't mean what the user input is valid. only existing ref data is valid and needs to be checked before a change is made
                    bool changed = false;

                    // make a copy of this original data and we'll check after if we actually were able to make a change to it (was the value provided valid)
                    var data = CopyRecord(oData);

                    // update this record's data fields that have changed with the new data
                    foreach (var v in diff)
                    {
                        // special check for setting a value to NULL as its always valid to do this but wouldn't show up in ref data to pass the next check below
                        if (v.valA == null)
                        {
                            oData.GetType().GetProperty(v.Prop).SetValue(oData, v.valA);
                            oData.UpdatedBy = user;
                            oData.UpdatedDate = DateTime.Now;
                            changed = true;
                        }
                        // validate that the value for this column is in the ref table before allowing an update. note exception if not so we can tell the user
                        else if (refData[v.Prop].Where(a =&gt; a.value == v.valA.ToString()).FirstOrDefault() != null)
                        {
                            // update the current objects values with the new objects value as it changed and is a valid value based on the ref data defined for that column
                            oData.GetType().GetProperty(v.Prop).SetValue(oData, v.valA);
                            oData.UpdatedBy = user;
                            oData.UpdatedDate = DateTime.Now;
                            changed = true;
                        }
                        else
                        {
                            // the value provided isn't valid for this column so note this to tell the user
                            exceptions.Add(string.Format("Error: ID: {0}, Value: '{1}' is not valid for column [{2}]. Add the reference data if needed and re-import.", d.id, v.valA, v.Prop));
                        }
                    }

                    // we only need to reattach and save off changes IF we actually changed something to a valid ref value and we had no exceptions for this record
                    if (changed &amp;&amp; exceptions.Count == 0)
                    {
                        // because our current object was in memory we will reattached it to EF so we can mark it as changed and SaveChanges() will write it back to the DB
                        ctx.MyTable.Attach(oData);
                        ctx.Entry(oData).State = EntityState.Modified;

                        // add a history record for the change to this product
                        CreateHistoryRecord(data, user);
                    }
                }
            }

            // wait until the very end before making DB changed. we don't save anything if there are exceptions or nothing changed
            if (exceptions.Count == 0)
            {
                ctx.SaveChanges();
            }
</code></pre>
