How to Check If Entity Exists Before Attaching?

Question

When a new Post is input into the system, a number of Tags will need to be instantiated and associated with the Post instance. Some of these Tags will already exist in the db, others will not and will need inserting.

An example:

var post = new Post {
    Slug = "hello-world",
    Title = "Hello, World!",
    Content = "this is my first post.",
    Tags = new List<Tag>()
};

var tag = new Tag { Name = "introduction" };
post.Tags.Add(tag);

When an associated Tag does not exist in the db, I can rely on a simple call to DbSet<T>.Add to insert both the post and the associated tags into the db. However, attempting to insert a post with associated tags that already exist in the db causes a primary key violation on the tags table.

In an attempt to solve this problem, I tried to Attach each tag to the database context which works super when the tags already exist in the database but otherwise, an exception is thrown with the following inner exception:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.TagPosts_dbo.Tags_Tag_Name". The conflict occurred in database "EF.Domain.BlogDb", table "dbo.Tags", column 'Name'. The statement has been terminated."}

I want to insert tags associated with the post into the database only when it is required. How can I achieve this?

Answer 1

Your predicament is quite interesting. If you wouldn't use a generic repository but a specific repository that knows the primary key of the entity you want to attach is you could just use a code like this:

var tagExists = Tags.Any(t => t.Name == tag.Name);

or

var tag = Tags.Find(tag.Name);

however in your case we need a more general approach such as getting the primary key of the entity the Repository class uses regardless the type of the entity. To achieve this I've created two extension methods on the DbContext class:

public static IList<string> GetPrimaryKeyNames<TEntity>(this DbContext context)
    where TEntity : class
{
    var objectContext = ((IObjectContextAdapter)context).ObjectContext;
    var set = objectContext.CreateObjectSet<TEntity>();

    return set.EntitySet.ElementType
        .KeyMembers
        .Select(k => k.Name)
        .ToList();
}

public static IList<object> GetPrimaryKeyValues<TEntity>(this DbContext context, TEntity entity)
    where TEntity : class
{
    var valueList = new List<object>();
    var primaryKeyNames = context.GetPrimaryKeyNames<TEntity>();

    foreach(var propertyInfo in entity.GetType().GetProperties())
    {
        if (primaryKeyNames.Contains(propertyInfo.Name))
        {
            valueList.Add(propertyInfo.GetValue(entity));
        }
    }

    return valueList;
}

Utilizing these methods you can change the Attach method on the Repository class like the following:

public void Attach(TEntity entity)
{
    var storeEntity = _context.Set<TEntity>().Find(
       _context.GetPrimaryKeyValues(entity).ToArray());

    if (storeEntity != null)
    {
        _context.Entry(storeEntity).State = EntityState.Detached;
        _context.Set<TEntity>().Attach(entity);
    }
}