Entity Framework Add if not exist without update
C#Entity FrameworkC# Problem Overview
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!!!
C# Solutions
Solution 1 - C#
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.
Solution 2 - C#
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.
Solution 3 - C#
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();
}
Solution 4 - C#
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.
https://stackoverflow.com/questions/31162576/entity-framework-add-if-not-exist-without-update
https://stackoverflow.com/questions/25928353/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;
}
}
}
Solution 5 - C#
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();
Solution 6 - C#
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);
}
}
}
Solution 7 - C#
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.