Entity Framework GetOrCreate?

Question

To start here are my tables. Not very interesting, just a primary key, data and foreign key connecting them. Everything with a unique constraint.

CREATE TABLE [dbo].[Providers] (
    [id]   BIGINT         IDENTITY (1, 1) NOT NULL,
    [name] NVARCHAR (850) NOT NULL,
    CONSTRAINT [PK_Providers] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [UQ_Providers_name] UNIQUE NONCLUSTERED ([name] ASC)
);

---

CREATE TABLE [dbo].[Templates] (
    [id]           BIGINT         IDENTITY (1, 1) NOT NULL,
    [provider]     BIGINT         NOT NULL,
    [repositoryId] NVARCHAR (842) NOT NULL,
    CONSTRAINT [PK_Templates] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [UQ_Templates_repositoryId] UNIQUE NONCLUSTERED ([provider] ASC, [repositoryId] ASC),
    CONSTRAINT [FK_Templates_ToProviders] FOREIGN KEY ([provider]) REFERENCES [dbo].[Providers] ([id])
);

Using Entity Framework, I want to insert a row into Templates. However, I don't know at the time of insert whether the matching row in Providers exists yet. I want to be defensive against race conditions if many people are inserting at the same time (two templates with the same Provider).

I believe that in order to accomplish this I need to first get-or-create the provider, then after that is persisted (at least one full DB round trip) create the template.

I have come up with a solution that I think will work, but it seems incredibly complicated for what I have to imagine is a pretty common scenario. I'm hoping someone can show me what I am doing wrong or how I can do it better.

The following code lives inside my DbContext implementation.

public async Task<Provider> GetOrCreateProvider(String name)
{
    var provider = new Provider { name = name };
    return await GetOrCreateEntity(Providers, provider);
}

public async Task<Template> GetOrCreateTemplate(Provider provider, String repositoryId)
{
    var template = new Template { Provider = provider, repositoryId = repositoryId };
    return await GetOrCreateEntity(Templates, template);
}

private async Task<T> GetOrCreateEntity<T>(DbSet<T> dbSet, T newEntity) where T : class
{
    var maybeEntity = await dbSet.Where(entity => entity.Equals(newEntity)).FirstOrDefaultAsync();
    if (maybeEntity != null)
        return maybeEntity;

    try
    {
        dbSet.Add(newEntity);
        await SaveChangesAsync();
        return newEntity;
    }
    catch (UpdateException exception) when ((exception.InnerException as SqlException)?.Number == 2601 || (exception.InnerException as SqlException)?.Number == 2627)
    {
        return await dbSet.Where(entity => entity.Equals(newEntity)).FirstOrDefaultAsync();
    }
}

Entity classes are auto-generated by VS from my DB tables with a .Equals override added to them. Here is Provider, Template is similar but with a reference to a single Provider rather than a collection:

public partial class Provider
{
    public Provider()
    {
        Templates = new HashSet<Template>();
    }

    public long id { get; set; }

    [Required]
    [StringLength(850)]
    public string name { get; set; }

    public virtual ICollection<Template> Templates { get; set; }

    public override Boolean Equals(Object otherObject)
    {
        if (Object.ReferenceEquals(this, otherObject))
            return true;

        if (otherObject == null)
            return false;

        var other = otherObject as Provider;
        if (other == null)
            return false;

        return name == other.name;
    }

    public override Int32 GetHashCode()
    {
        return name.GetHashCode();
    }
}

Usage would look like this:

var provider = await GetOrCreateProvider("foo");
var template = await GetOrCreateTemplate(provider, "bar");

The primary problem with this (aside from the amount of boilerplate necessary to do such a simple operation) is that I have to make two full round trips to the server in the best case scenario (when both the provider and the template already exist). In the worst case scenario I have to make 6 round trips:

1. fail to get provider
2. constraint violation creating provider
3. get provider
4. fail to get template
5. constraint violation creating template
6. get template

I'm not terribly concerned about the worst case scenario but I am concerned about the best case scenario as the number of round trips goes up with the complexity of my object graph. If I have something else that references something in the Template table I am now up to 3 round trips.

Answer 1

A stored procedure would do all steps in a single call to the database, and in a single transaction. Have a look at the merge command, for example introduction or reference.

You can use this method to execute a stored procedure from EF:

private static int ExecuteSqlCount(string statement, SqlParameter[] paramsSql)
{
    using (Entities dbContext = new Entities())
    {
        var total = dbContext.Database.SqlQuery<int>(statement, paramsSql).First();
        return total;
    }
}

The return value is whatever the USP returns. The method is called like (for a USP that returns an int):

    var parameters = new List<SqlParameter>();
    string statement = "exec uspPersonAdd @personName = @name, @activeFlag = @active";
    parameters.Add(new SqlParameter("@name", person.PersonName));
    parameters.Add(new SqlParameter("@active", person.Active));
    int id = ExecuteSqlCount(statement, parameters.ToArray());

You need using System.Data.SqlClient;