How to avoid duplicate key exceptions efficiently

I have an implementation that I've done in the past that I like. You may or may not find it useful.

This is how it works... I load both external and local data into memory using a model object that will work for both. For example...

public class Person{    public string FirstName { get; set; }    public string LastName { get; set; }    public string PhoneNumber { get; set; }    public string Address { get; set; }    // This comparer will be used to find records that exist or don't exist.    public class KeyFieldComparer : IEqualityComparer<Person>    {        public bool Equals(Person p1, Person p2)        {            return p1.FirstName == p2.FirstName && p1.LastName == p2.LastName;        }        public int GetHashCode(Person p)        {            return p.FirstName.GetHashCode() ^ p.LastName.GetHashCode();        }    }    // This comparer will be used to find records that are outdated and need to be updated.    public class OutdatedComparer : IEqualityComparer<Person>    {        public bool Equals(Person p1, Person p2)        {            return p1.FirstName == p2.FirstName && p1.LastName == p2.LastName && (p1.PhoneNumber != p2.PhoneNumber || p1.Address != p2.Address);        }        public int GetHashCode(Person p)        {            return p.FirstName.GetHashCode() ^ p.LastName.GetHashCode();        }    }}

We need to have some way to uniquely identify the records which I assume you have. In this example it's by FirstName and LastName (I know that's not very unique but for simplicity let's pretend it works well). The IEqualityComparer<> will do the work of finding the outdated and new records when the lists are loaded into memory.

Now we simply separate existing outdated records and brand new records like this...

List<Person> local = loadLocalRecords();List<Person> external = loadExternalRecords();var newRecordsToInsert = external.Except(local, new Person.KeyFieldComparer());var outdatedRecordsToUpdate = local.Intersect(external, new Person.OutdatedComparer());

I hope it makes sense. I can answer questions if you have them. The good thing about this method is that it does the job with the least amount of hits to the database (I think). The bad thing is that it has to load everything into memory which may not be practical for you. But your table size has to be large for it to be a problem. Somewhere above a few million records depending on how many columns.