Ignore duplicate key insert with Entity Framework

Question

I'm using ASP.NET MVC4 with Entity Framework Code First. I have a table called "users", with primary key "UserId". This table may have 200,000+ entries.

I need to insert another 50 users. I might do this like

foreach(User user in NewUsers){
    context.Add(user);
}
dbcontext.SaveChanges();

The problem is, one or more of those new users might already exist in the DB. If I add them and then try to save, it throws an error and none of the valid ones get added. I could modify the code to do this:

foreach(User user in NewUsers){
    if(dbcontext.Users.FirstOrDefault(u => u.UserId) == null)
    {
        dbcontext.Users.Add(user);
    }
}
dbcontext.SaveChanges();

which would work. The problem is, then it has to run a query 50 times on a 200,000+ entry table. So my question is, what is the most performance efficient method of inserting these users, ignoring any duplicates?

Answer 1

You can do this:

var newUserIDs = NewUsers.Select(u => u.UserId).Distinct().ToArray();
var usersInDb = dbcontext.Users.Where(u => newUserIDs.Contains(u.UserId))
                               .Select(u => u.UserId).ToArray();
var usersNotInDb = NewUsers.Where(u => !usersInDb.Contains(u.UserId));
foreach(User user in usersNotInDb){
    context.Add(user);
}

dbcontext.SaveChanges();

This will execute a single query in your database to find users which already exist, then filter them out of your NewUsers set.

Answer 2

Since this is your primary key, your options are limited. If this wasn't your primary key, and just a unique index, assuming SQL Server, you could set up your unique key to ignore duplicates.

What I might suggest is to simply wrap a try/catch around the Add and eat the exception if the exception is a duplicate key error.

You might also see if your object supports the AddOrUpdate() method. I know this is supported in Code First implementations. I believe in this case it will do an add on a new or update if the row exists. However, this might still involve a trip to the DB to see if the user already exists in order to know whether to do an add or update. And, in some cases, you might not want to actually perform an update.

I think if it were me, I'd go the Try/Catch route.

Answer 3

You can filter out the existing users with one query

foreach(User user in NewUsers.Where(us => !dbcontext.Users.Any(u => u.userId == us.userId)))
{
    dbcontext.Users.Add(user);
}
dbcontext.SaveChanges();

EDIT:

As pointed out in the comments the proposal above will result in an sql call for each element in the NewUsers collection. I could confirm that with SQL Server Profiler.

One intresting result of the profiling is the somewhat wierd sql generated by EF for each item(Model names are different than in the OP, but the query is the same):

exec sp_executesql N'SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[EventGroup] AS [Extent1]
    WHERE [Extent1].[EventGroupID] = @p__linq__0
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[EventGroup] AS [Extent2]
    WHERE [Extent2].[EventGroupID] = @p__linq__0
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',N'@p__linq__0 int',@p__linq__0=10

Quite a nice piece of code to do the job of a simple one-liner.

My point of view is that writing nice and readable declarative code and let the compiler and optimizer do the dirty job is a great attitude. This is one of the cases when the result of such a style is surprising and you have to go dirty.

Answer 4

You can just filter out the failures and keep trying until it succeeds or you get another sort of Exception

public partial class YourEntities: DbContext
{
    public override int SaveChanges()
    {
        var isSaved = false;
        do
        {
            try
            {
                return base.SaveChanges();
            }
            catch (DbUpdateException ex)
            {
                var entries = ex.Entries;
                foreach (var entry in entries)
                {
                    // change state to remove it from context 
                    entry.State = EntityState.Detached;
                }
            }
        }
        while (!isSaved);

        return null;    // never gets here
    }
}

You might want to put in some extra logic to avoid an infinite loop.

Answer 5

The following extension method will allow you to insert records of any type while ignoring duplicates:

 public static void AddRangeIgnore(this DbSet dbSet, IEnumerable<object> entities)
    {
        var entitiesList = entities.ToList();
        var firstEntity = entitiesList.FirstOrDefault();

        if (firstEntity == null || !firstEntity.HasKey() || firstEntity.HasIdentityKey())
        {
            dbSet.AddRange(entitiesList);
            return;
        }

        var uniqueEntities = new List<object>();

        using (var dbContext = _dataService.CreateDbContext())
        {
            var uniqueDbSet = dbContext.Set(entitiesList.First().GetType());

            foreach (object entity in entitiesList)
            {
                var keyValues = entity.GetKeyValues();
                var existingEntity = uniqueDbSet.Find(keyValues);

                if (existingEntity == null)
                {
                    uniqueEntities.Add(entity);
                    uniqueDbSet.Attach(entity);
                }
            }
        }

        dbSet.AddRange(uniqueEntities);
    }

    public static object[] GetKeyValues(this object entity)
    {
        using (var dbContext = _dataService.CreateDbContext())
        {
            var entityType = entity.GetType();
            dbContext.Set(entityType).Attach(entity);
            var objectStateEntry = ((IObjectContextAdapter)dbContext).ObjectContext.ObjectStateManager.GetObjectStateEntry(entity);
            var value = objectStateEntry.EntityKey
                                        .EntityKeyValues
                                        .Select(kv => kv.Value)
                                        .ToArray();
            return value;
        }
    }

    public static bool HasKey(this object entity)
    {
        using (var dbContext = _dataService.CreateDbContext())
        {
            var entityType = entity.GetType();
            dbContext.Set(entityType).Attach(entity);
            var objectStateEntry = ((IObjectContextAdapter)dbContext).ObjectContext.ObjectStateManager.GetObjectStateEntry(entity);
            return objectStateEntry.EntityKey != null;
        }
    }

    public static bool HasIdentityKey(this object entity)
    {
        using (var dbContext = _dataService.CreateDbContext())
        {
            var entityType = entity.GetType();
            dbContext.Set(entityType).Attach(entity);
            var objectStateEntry = ((IObjectContextAdapter)dbContext).ObjectContext.ObjectStateManager.GetObjectStateEntry(entity);
            var keyPropertyName = objectStateEntry.EntityKey
                                        .EntityKeyValues
                                        .Select(kv => kv.Key)
                                        .FirstOrDefault();

            if (keyPropertyName == null)
            {
                return false;
            }

            var keyProperty = entityType.GetProperty(keyPropertyName);
            var attribute = (DatabaseGeneratedAttribute)Attribute.GetCustomAttribute(keyProperty, typeof(DatabaseGeneratedAttribute));
            return attribute != null && attribute.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity;
        }
    }

Answer 6

I've found a package https://github.com/artiomchi/FlexLabs.Upsert in the comments for the issue of this feature on the EF Core repo: https://github.com/dotnet/efcore/issues/16949#issuecomment-911120274

By using its NoUpdate() which maps to INSERT IGNORE syntax:

db.Set<Users>().UpsertRange(NewUsers).NoUpdate().Run()

you can just insert entities without handing duplicate errors or doing SELECT ... FOR UPDATE and filter entities that already exist in DB out of your entities to be inserted.