Entity Framework Add if not exist without update

Question

I like the fact that AddOrUpdate let's you specify a filter to check to avoid adding duplicates. But I would like similar functionality without the update.

Right now I do something like this:

var checkProfile = from p in db.Profile
    where p => p.LastName == newProfile.lastName
         && p => p.FirstName == newProfile.firstName
         && p => p.Middle== newProfile.middle
    select p;
if (checkProfile.FirstOrDefault() == null)
{
    db.Profile.Add(newProfile);
    db.SaveChanges();
}

I know I can do something like this:

db.Profile.AddOrUpdate(p => new {p.LastName, p.FirstName, p.Middle}, newProfile);
db.SaveChanges();

But I would rather skip modifying the data in this case.

The first example does what I want but with more code. Is there a simpler/cleaner way to do what I want in the first example?

Update:

I like Ognyan Dimitrov's suggestion. I'm trying to implement it. My models inherit from BaseEntity. Can I put a generic version of that there?

My model is defined:

public class Address :BaseEntity
{

My BaseEntity:

public class BaseEntity 
{
    public virtual T AddIfNotExists<T>(T entity, Expression<Func<T, bool>> predicate = null)
    {
        var exists = predicate != null ? DbSet.Any(predicate) : DbSet.Any();
        return !exists ? DbSet.Add(entity) : null;
    }
}

I'm getting errors for Any(...) and Add(...). The error for Add(...) is 'An object reference is required for the non-static field, method, or property 'System.Data.Entity.DbSet.Add(object)' '

Should I be using this.Add(object) ?

Update 2:

I've created this code:

public static class DbSetExtensions
{
    public static T AddIfNotExists<T>(this DbSet<T> dbSet, T entity, Expression<Func<T, bool>> predicate = null) where T : class, new()
    {
        var exists = predicate != null ? dbSet.Any(predicate) : dbSet.Any();
        return !exists ? dbSet.Add(entity) : null;
    }

}

Now I'm trying to call it like this, but it's not correct. Forgive my lack of understanding.

_db.ProfileIdentifier.AddIfNotExists(newIdentifier,
            pi => new {pi.ProfileId, pi.ProfileIdentifierTypeId, pi.ProfileIdentifierValue});

Update - Solution:

I can call the DbSetextensions like this:

_db.ProfileIdentifier.AddIfNotExists(newIdentifier,
            pi => pi.ProfileId == profileId &&  
            pi.ProfileIdentifierTypeId == (int)type &&  
            pi.ProfileIdentifierValue == value);

Thanks a lot for working with me Ognyan!!!

I don't think there is - you can write a stored procedure that will do a MERGE, but I don't think there's anything built in. — Patryk Ćwiek
– Patryk Ćwiek, Commented Jul 1, 2015 at 13:26
Take care with the AddOrUpdate method: thedatafarm.com/data-access/… — Colin
– Colin, Commented Jul 1, 2015 at 15:11
To prevent duplicates I normally override the ValidateEntity method on the DbContext stackoverflow.com/a/16647237/150342 — Colin
– Colin, Commented Jul 1, 2015 at 15:16

Steven Benitez · Accepted Answer · 2023-02-20 17:13:11Z

54

Have you tried to check if the entity exists and if not - add it? Like this :

UPDATE

using System.Linq.Expressions;

public class ContextWithExtensionExample
{
    public void DoSomeContextWork(DbContext context)
    {
        var uni = new Unicorn();
        context.Set<Unicorn>().AddIfNotExists(uni, x => x.Name == "James");
    }
}

public static class DbSetExtensions
{
    public static T AddIfNotExists<T>(this DbSet<T> dbSet, T entity, Expression<Func<T, bool>> predicate = null) where T : class, new()
    {
        var exists = predicate != null ? dbSet.Any(predicate) : dbSet.Any();
        return !exists ? dbSet.Add(entity) : null;
    }
}

You can use this method directly and remember to call DbContext.SaveChanges() after the call.

edited Feb 20, 2023 at 17:13

Steven Benitez

11k3 gold badges43 silver badges51 bronze badges

answered Jul 1, 2015 at 13:33

Ognyan Dimitrov

6,2932 gold badges54 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

20 Comments

pseudocoder Over a year ago

An extension method is a nice way to package up this solution.

pseudocoder Over a year ago

You can define this method in any assembly you are referencing from the calling code. "Extension methods are defined as static methods but are called by using instance method syntax." -MSDN msdn.microsoft.com/en-us/library/bb383977.aspx

Richard Marskell - Drackir Over a year ago

@ginkner You just have to change the return type from T to EntityEntry<T> and add using Microsoft.EntityFrameworkCore.ChangeTracking;.

tugberk Over a year ago

This will not be running in a transaction.

tugberk Over a year ago

@Shiva when you call dbSet.Any, it will evaluate eagerly. So, you still have an open window between dbSet.Any and dbSet.Add call. Isn't this the case here?

|

zs2020 · Accepted Answer · 2019-02-20 21:19:48Z

20

All the other answers are incorrect.

"Read before write" can violate data integrity without being put inside a transaction control.

In SQL Server, you can use merge statement. However merge statement is not available in EF.

edited Feb 20, 2019 at 21:19

answered Feb 19, 2019 at 22:21

zs2020

54.7k30 gold badges157 silver badges223 bronze badges

6 Comments

Rafael Herscovici Over a year ago

Can you expand on this or give referances?

DARKGuy Over a year ago

A better answer would be to provide a workaround or something that could overcome that limitation/possibility

ᴍᴀᴛᴛ ʙᴀᴋᴇʀ Over a year ago

However this question is for c# entity framework, so your answer isn't helpful. Telling someone not to do something which already solves a problem without giving an alternative solution is pointless.

HelloWorld Over a year ago

this is not a solution at all! just a rant!

Egor Okhterov Over a year ago

True. They have to use transaction for that.

|

Salaros · Accepted Answer · 2018-09-14 20:21:23Z

10

The solution is OK, when you have to add just one item, but it's very expensive in terms of performance in case you have to add multiple items. I think there is a better solution:

public static class DbSetExtensions
{
    public static EntityEntry<TEnt> AddIfNotExists<TEnt, TKey>(this DbSet<TEnt> dbSet, TEnt entity, Func<TEnt, TKey> predicate) where TEnt : class
    {
        var exists = dbSet.Any(c => predicate(entity).Equals(predicate(c)));
        return exists
            ? null
            : dbSet.Add(entity);
    }

    public static void AddRangeIfNotExists<TEnt, TKey>(this DbSet<TEnt> dbSet, IEnumerable<TEnt> entities, Func<TEnt, TKey> predicate) where TEnt : class
    {
        var entitiesExist = from ent in dbSet
            where entities.Any(add => predicate(ent).Equals(predicate(add)))
            select ent;

        dbSet.AddRange(entities.Except(entitiesExist));
    }
}

So later it can be used like this:

using (var context = new MyDbContext())
{
    var user1 = new User { Name = "Peter", Age = 32 };
    context.Users.AddIfNotExists(user1, u => u.Name);

    var user2 = new User { Name = "Joe", Age = 25 };
    context.Users.AddIfNotExists(user2, u => u.Age);

    // Adds user1 if there is no user with name "Peter"
    // Adds user2 if there is no user with age 25
    context.SaveChanges();
}

edited Sep 14, 2018 at 20:21

answered Nov 18, 2017 at 21:36

Salaros

1,4521 gold badge14 silver badges36 bronze badges

3 Comments

rory Over a year ago

AddIfNotExists works fine but AddRangeIfNotExists gives me the full dataset for entitiesExist. Calling it like context.Accounts.AddRangeIfNotExists(accounts, t => t.ID); where accounts = List<Account>

sevenam Over a year ago

This stopped working after upgrading to .net core 3.1 it seems...

ms_devel Over a year ago

Yes, it doesn't work on .net core 3.1 as @johnb said i.imgur.com/qFWnBdW.png

Community · Accepted Answer · 2017-05-23 11:33:16Z

I used something like, read these two posts to make my code. I hope to help those in need of a similar signature to AddOrUpdate.

Entity Framework Add if not exist without update

Making AddOrUpdate change only some properties

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace System.Data.Entity.Migrations
{
    //
    // Summary:
    //     Metodos de extensão para System.Data.Entity.IDbSet
    public static class DbSetMigrationsGustavoExtensions
    {
        /// <summary>
        /// Adiciona uma entidade se ela não existe ainda
        /// Assinatura semelhante ao AddOrUpdate
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="set">Set onde serão adicionadas as entidades</param>
        /// <param name="identifierExpression">Campos usados na comparação</param>
        /// <param name="entities">Entidades para adicionar</param>
        public static void AddIfNotExists<TEntity>(this IDbSet<TEntity> set, Expression<Func<TEntity, object>> identifierExpression, params TEntity[] entities) where TEntity : class
        {

            var identifyingProperties = GetProperties<TEntity>(identifierExpression).ToList();
            var parameter = Expression.Parameter(typeof(TEntity));
            foreach (var entity in entities)
            {
                var matches = identifyingProperties.Select(pi => Expression.Equal(Expression.Property(parameter, pi.Name), Expression.Constant(pi.GetValue(entity, null))));
                var matchExpression = matches.Aggregate<BinaryExpression, Expression>(null, (agg, v) => (agg == null) ? v : Expression.AndAlso(agg, v));

                var predicate = Expression.Lambda<Func<TEntity, bool>>(matchExpression, new[] { parameter });
                if (!set.Any(predicate))
                {
                    set.Add(entity);
                }
            }
        }

        private static IEnumerable<PropertyInfo> GetProperties<T>(Expression<Func<T, object>> exp) where T : class
        {
            Debug.Assert(exp != null);
            Debug.Assert(exp.Body != null);
            Debug.Assert(exp.Parameters.Count == 1);

            var type = typeof(T);
            var properties = new List<PropertyInfo>();

            if (exp.Body.NodeType == ExpressionType.MemberAccess)
            {
                var memExp = exp.Body as MemberExpression;
                if (memExp != null && memExp.Member != null)
                    properties.Add(type.GetProperty(memExp.Member.Name));
            }
            else if (exp.Body.NodeType == ExpressionType.Convert)
            {
                var unaryExp = exp.Body as UnaryExpression;
                if (unaryExp != null)
                {
                    var propExp = unaryExp.Operand as MemberExpression;
                    if (propExp != null && propExp.Member != null)
                        properties.Add(type.GetProperty(propExp.Member.Name));
                }
            }
            else if (exp.Body.NodeType == ExpressionType.New)
            {
                var newExp = exp.Body as NewExpression;
                if (newExp != null)
                    properties.AddRange(newExp.Members.Select(x => type.GetProperty(x.Name)));
            }

            return properties.OfType<PropertyInfo>();
        }

        /// <summary>
        /// Faz um set.Any(predicate)
        /// Se não existe nada no set então adiciona
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="set">Set onde será adicionada a entidade</param>
        /// <param name="predicate">Condição (exemplo: dbUser => dbUser.Nome == "Gustavo")</param>
        /// <param name="entity">Entidade para adicionar</param>
        /// <returns></returns>
        public static T AddIfNotExists<T>(this IDbSet<T> set, Expression<Func<T, bool>> predicate, T entity) where T : class, new()
        {
            return !set.Any(predicate) ? set.Add(entity) : null;
        }
    }
}

Why do you need identifyingProperties? Expression<Func<T, bool>> predicate From Ognyan Dimitrov‘s answer should be sufficient.
I just published my code, because it has the same signature as AddOrUpdate public static void AddOrUpdate<TEntity>(this IDbSet<TEntity> set, Expression<Func<TEntity, object>> identifierExpression, params TEntity[] entities) where TEntity : class;

Brad C · Accepted Answer · 2019-11-13 19:13:00Z

4

Quoted from MSDN EF article.

Insert or update pattern

A common pattern for some applications is to either Add an entity as new (resulting in a database insert) or Attach an entity as existing and mark it as modified (resulting in a database update) depending on the value of the primary key. For example, when using database generated integer primary keys it is common to treat an entity with a zero key as new and an entity with a non-zero key as existing. This pattern can be achieved by setting the entity state based on a check of the primary key value.

Note that when you change the state to Modified all the properties of the entity will be marked as modified and all the property values will be sent to the database when SaveChanges is called.

context.Entry(profile).State = profile.Id == 0 ? EntityState.Added : EntityState.Modified; 
context.SaveChanges();

edited Nov 13, 2019 at 19:13

answered Jul 1, 2015 at 13:42

Brad C

3,02225 silver badges36 bronze badges

4 Comments

M Kenyon II Over a year ago

Actually, in this case, I do not want Modified. I do not want to SaveChanges if the record already exists.

BenKoshy Over a year ago

@br4d thank you for your answer - i do not quite understand it: where is the blog instance obtained from?

Burak Dobur Over a year ago

@BKSpurgeon Probably obtained as method parameter. Which could be safely mapped from a view model class to an entity class. As such:

public Category AddOrUpdate(CategoryVM categoryVM)     {     var category = categoryVM.Adapt<Category>();     context.Entry(category).State = category.Id == 0 ? EntityState.Added : EntityState.Modified;      }

Usman Over a year ago

The correct way would be as following : public static class DbSetExtensions { public static T AddIfNotExists<T>(this DbSet<T> dbSet, T entity, Expression<Func<T, bool>> predicate = null) where T : class, new() { var exists = predicate != null ? dbSet.Any(predicate) : dbSet.Any(); return !exists ? dbSet.Add(entity).Entity : null; } }

John Tyrrell · Accepted Answer · 2016-04-29 06:19:12Z

2

What worked for me is this:

public static void AddIfNotExists<T>(this DbSet<T> dbSet, Func<T, object> predicate, params T [] entities) where T : class, new()
{
    foreach (var entity in entities)
    {
        var newValues = predicate.Invoke(entity);
        Expression<Func<T, bool>> compare = arg => predicate(arg).Equals(newValues);
        var compiled = compare.Compile();
        var existing = dbSet.FirstOrDefault(compiled);
        if (existing == null)
        {
            dbSet.Add(entity);
        }
    }
}

answered Apr 29, 2016 at 6:19

John Tyrrell

794 bronze badges

Comments

Santiago · Accepted Answer · 2023-04-27 14:53:05Z

1

Because this answer stopped working in NET 7, and you only want to check by Id if the entity exists you can remove the Function part from the code and still works perfect

public static class DbContextExtensions
{
    public static EntityEntry<TEnt> AddIfNotExists<TEnt>(this DbSet<TEnt> dbSet, TEnt entity) where TEnt : class
    {
        var exists = dbSet.Any(c => entity == c);
        return exists
            ? dbSet.Attach(entity)
            : dbSet.Add(entity);
    }
}

As you can see in the SQL generated by EF, only look for the entity by the Id

answered Apr 27, 2023 at 14:53

Santiago

2,37010 gold badges34 silver badges65 bronze badges

Comments

Vunlinur · Accepted Answer · 2024-04-27 02:15:52Z

1

While most of this thread is focused on a single-entity insert, I was in need of a range insert. This is what I came up with after iterating through a few versions based on Salaros' AddRangeIfNotExists above (which I'll further refer to as a 'base version'):

public static async Task AddRangeIfNotExists<TEnt, TKey>(this DbSet<TEnt> dbSet, IEnumerable<TEnt> entities, Func<TEnt, TKey> keySelector) where TEnt : class {
  var existingKeys = dbSet.Select(keySelector).ToHashSet();
  var newEntities = entities.Where(e => !existingKeys.Contains(keySelector(e)));
  await dbSet.AddRangeAsync(newEntities);
}

Works on .Net 8 and EF Core 8.0.4
Leverages HashSet for key lookup, which reduces time complexity from ~ O(entities*existingEntities) of the base version to around O(entities)
Executes only one transaction, for what it's worth
After some simple testing I see performance increase 6x-16x for my small data set, with more benefits the larger the data set
Decided to forgo compiling the keySelector as it was giving me a significant overhead and a perf drop - but might be viable for large data sets

answered Apr 27, 2024 at 2:15

Vunlinur

312 bronze badges

1 Comment

Frank Schwieterman Over a year ago

Hmm, for SqlLite I am seeing 3 commands executed for writing two elements- the first command is reading back all the keys. the second two are inserts. What database did you see it run as 1 command? And do you happen to have the executed command available?

AllFallD0wn · Accepted Answer · 2015-07-01 13:28:30Z

0

The only thing that comes to mind is to use IEqualityComparer<T>, but this doesn't really stop the work, merely abstracts it away and creates cleaner code.

answered Jul 1, 2015 at 13:28

AllFallD0wn

5517 silver badges23 bronze badges

Comments

ZZY · Accepted Answer · 2024-07-17 10:44:36Z

As mentioned by zs2020, the accepted answer (read to check existance and create in two DB operations) isn't concurrent safe. Multiple threads may pass the existance check at the same time, then all proceed to create multiple new records. AKA, race condition.

On top of the accepted answer, I rely on DB unique constaint to ensure concurrent safe. First, create unique indexes or primary key properly. Then:

// list of SqlState values: https://www.ibm.com/docs/en/i/7.5?topic=codes-listing-sqlstate-values
public static class SqlState
{
    public const string UniqueConstraintViolation = "23505";
}


using System.Data.Common;
using Microsoft.EntityFrameworkCore;

public static class DbContextExtensions
{
    public static void SaveChangesAndIgnoreErrors(this DbContext dbContext, params string[] ignoreSqlStates)
    {
        try
        {
            dbContext.SaveChanges();
        }
        catch (DbUpdateException ex)
        {
            if (ex.InnerException is DbException dbException)
            {
                if (ignoreSqlStates.Contains(dbException.SqlState))
                {
                    return;
                }
            }

            throw;
        }
    }
}

// Usage
dbContext.SaveChangesAndIgnoreErrors(SqlState.UniqueConstraintViolation);

boaz levinson · Accepted Answer · 2023-12-04 14:02:14Z

-1

Returning to the basics - A table with primary key will save the hustle . Catch the exception if there is already a row of this type . However , the frequency of insertion & probability of duplicate rows can determine if you want to use this approach as we want to save the exceptions at runtime. If not suitable in some cases, @Santiago's answer seems very good to me (seeing the generated sql query). Also there is a possibility of using TransactionScope \ Transaction.BeginTransaction to ensure rollback in case of failures.

answered Dec 4, 2023 at 14:02

boaz levinson

951 silver badge8 bronze badges

Collectives™ on Stack Overflow

Entity Framework Add if not exist without update

11 Answers 11

20 Comments

6 Comments

3 Comments

2 Comments

4 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

20 Comments

6 Comments

3 Comments

2 Comments

4 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related