How do I prevent duplicate entries using the UnitOfWork pattern with code first Entity Framework?

Question

I am using the Unit of Work and Generic Repository pattern. Here is the statement that checks for a duplicate entry:

int id = int.Parse(beer.id); //id comes from the item we're hoping to insert

if (_unitOfWork.BeerRepository.GetByID(id) == null)
     \\create a new model br
     _unitOfWork.BeerRepository.Insert(br);
     _unitOfWork.save();

Apparently this is failing to check to see if the beer is already in the database because I get this inner exception:

Violation of PRIMARY KEY constraint 'PK_Beers_3214EC2703317E3D'. Cannot insert duplicate key in object 'dbo.Beers'.\r\nThe statement has been terminated.

I also get this message:

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

The UnitOfWork class has my BeerRecommenderContext which implements DbContext and the UnitOfWork has a generic repository for each entity:

namespace BeerRecommender.Models
{
public class GenericRepository<TEntity> where TEntity : class
{
    internal BeerRecommenderContext context;
    internal DbSet<TEntity> dbSet;

    public GenericRepository(BeerRecommenderContext context)
    {
        this.context = context;
        this.dbSet = context.Set<TEntity>();
    }

    public virtual IEnumerable<TEntity> Get(
        Expression<Func<TEntity, bool>> filter = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
        string includeProperties = "")
    {
        IQueryable<TEntity> query = dbSet;

        if (filter != null)
        {
            query = query.Where(filter);
        }

        foreach (var includeProperty in includeProperties.Split
            (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
        {
            query = query.Include(includeProperty);
        }

        if (orderBy != null)
        {
            return orderBy(query).ToList();
        }
        else
        {
            return query.ToList();
        }
    }

    public virtual TEntity GetByID(object id)
    {
        return dbSet.Find(id);
    }

    public virtual void Insert(TEntity entity)
    {
        dbSet.Add(entity);
    }

    public virtual void Delete(object id)
    {
        TEntity entityToDelete = dbSet.Find(id);
        Delete(entityToDelete);
    }

    public virtual void Delete(TEntity entityToDelete)
    {
        if (context.Entry(entityToDelete).State == EntityState.Detached)
        {
            dbSet.Attach(entityToDelete);
        }
        dbSet.Remove(entityToDelete);
    }

    public virtual void Update(TEntity entityToUpdate)
    {
        dbSet.Attach(entityToUpdate);
        context.Entry(entityToUpdate).State = EntityState.Modified;
    }

    }
}

Do you set the primary key property of your new model br to id? — Slauma
The primary key of my model classes is "id". When a new beer is created, I use the given id — travis1097
This should actually work. Did you make some very simple checks? For example: 1) Note the id you are testing with, for example 5. 2) Look into the database if there is a beer with key = 5. 3) Check the result of GetByID -> If there was a beer number 5 in the DB but GetByID returns null, stop using EF and drink all the beers in your database. If there was no beer number 5 and GetByID returns null (which is expected) check in debugger if the ID of the new beer is really 5 before you call insert. Etc., etc., etc. — Slauma

Davin Tryon Davin Tryon · Accepted Answer · 2012-01-19T13:40:55

I have a similar usage of repository using code-first. Occasionally, I would see conflicts like the one you described. My issue was with change tracking across multiple processes. Are you inserting items into the database inside one process (using a single entity context)?

If you are, you should look at the Merge Options available with Entity Framework. If you are using the default merge option (AppendOnly), then you could be querying the in memory context instead of going to the database. This could cause the behaviour you are describing.

Unfortunately, as far as I understand, all the merge options are not yet exposed to Code-First. You can choose the default (AppendOnly) or NoTracking, which will go to the database every time.

Hope this helps, Davin

How do I prevent duplicate entries using the UnitOfWork pattern with code first Entity Framework?

1 Answers