Comparing List<T>

Question

Hello everyone and thanks for the help in advance. I have a WebApi application that accepts a List<tblResults> where the model for tblResults looks like:

public class tblResults
{
	public int Record_ID { get; set; }

	public int Description { get; set; }

	public DateTime TimeStamp { get; set; }

}

The WebApi then needs to check tblResults on the server and insert only the new records received based on the unique Record_ID. So I guess the obvious method is to query the database for any records greater than the new records timestamp, but I'm wondering if there is a more efficient Linq method to compare two List<T> to see if any of the new records exist in the table. Any help would be appreciated.

Answer 1

Use a set-based comparison instead of comparing timestamps.

For List<T>, LINQ provides set operators such as Except, Distinct, Union, and Intersect. For the specific requirement of finding records that are in the incoming list but not already present in the server-side data, Except is the relevant operator.

Two important points apply here:

1. LINQ set operators compare elements by equality.
2. For custom types, equality is based on GetHashCode and Equals, or on a custom comparer.

Since tblResults is a custom class and only Record_ID determines uniqueness, compare by Record_ID rather than by the full object instance.

Example with a custom comparer:

public class TblResultsRecordIdComparer : IEqualityComparer<tblResults>
{
    public bool Equals(tblResults x, tblResults y)
    {
        if (ReferenceEquals(x, y)) return true;
        if (x is null || y is null) return false;
        return x.Record_ID == y.Record_ID;
    }

    public int GetHashCode(tblResults obj)
    {
        return obj.Record_ID.GetHashCode();
    }
}

Then compare the incoming list with the existing records:

var comparer = new TblResultsRecordIdComparer();

var newRows = incomingResults.Except(existingResults, comparer).ToList();

newRows contains only items from incomingResults whose Record_ID does not already exist in existingResults.

If the data is coming from a database, the efficient pattern is still to do the comparison as a set operation and insert only the missing rows. Also ensure the database has an index or unique constraint on Record_ID, because that is what ultimately enforces uniqueness and improves lookup performance.

If only the IDs are needed for comparison, a simpler approach is to compare ID sequences directly:

var existingIds = existingResults.Select(r => r.Record_ID);

var newRows = incomingResults
    .Where(r => !existingIds.Contains(r.Record_ID))
    .ToList();

For large datasets, the set-based Except approach is the better LINQ fit from the available guidance, and indexing the join/comparison column is important for performance.

---

References:

• LINQ and collections
• Comparing DataRows (LINQ to DataSet)
• slow query on large data - Microsoft Q&A

Answer 2

Hello @Kmcnet ,

Thanks for your question.

Retrieving the entire database table into a List<T> for comparison purposes may increase memory consumption and could affect performance, particularly when the table contains a large number of records.

Instead, I recommend grabbing only the Record_IDs from your incoming list, ask the database which of those specific IDs already exist, and then filter your incoming list.

var incomingIds = incomingList.Select(x => x.Record_ID).ToList();

var existingIds = dbContext.tblResults
    .Where(x => incomingIds.Contains(x.Record_ID))
    .Select(x => x.Record_ID)
    .ToList();

var newRecordsToInsert = incomingList
    .Where(x => !existingIds.Contains(x.Record_ID))
    .ToList();

dbContext.tblResults.AddRange(newRecordsToInsert);
dbContext.SaveChanges();

In this example, Contains() is translated into a database query, allowing the database engine to perform the matching operation. This can help reduce the amount of data transferred from the database and may be more efficient than retrieving all existing records for comparison.

I hope this addresses your question. If this response was helpful, please consider following the guidance to provide feedback. Thank you.