Single Model CRUD Async

This scenario adds async support to the Single Model CRUD scenario.

Scenario Prototype

As a general rule, cancellation tokens are provided for read operations but not write operations. The reason is that users may wish to cancel loading a record or set of records, and this can be done safely. But if they try to cancel a write operation then it becomes a race condition between the database operation completing (including any open transactions) and the user's cancellation attempt. (There are exceptions, which will be handled in future scenarios.)

public interface ISingleModelCrudAsyncScenario<TEmployeeClassification>
   where TEmployeeClassification : class, IEmployeeClassification, new()
{
    Task<int> CreateAsync(TEmployeeClassification classification);

    Task DeleteAsync(TEmployeeClassification classification);

    Task DeleteByKeyAsync(int employeeClassificationKey);

    Task<TEmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default);

    Task<IList<TEmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default);

    Task<TEmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default);

    Task UpdateAsync(TEmployeeClassification classification);
}

ADO.NET

With ADO.NET, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

public class SingleModelCrudAsyncScenario : SqlServerScenarioBase, ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    public SingleModelCrudAsyncScenario(string connectionString) : base(connectionString)
    { }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        const string sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            return (int)(await cmd.ExecuteScalarAsync().ConfigureAwait(false))!;
        }
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
            await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
        }
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", employeeClassificationName);
            using (var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
            {
                if (!(await reader.ReadAsync(cancellationToken).ConfigureAwait(false)))
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
                };
            }
        }
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";

        var result = new List<EmployeeClassification>();

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        using (var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
        {
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                result.Add(new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
                });
            }
            return result;
        }
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            using (var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
            {
                if (!(await reader.ReadAsync(cancellationToken).ConfigureAwait(false)))
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
                };
            }
        }
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        const string sql = @"UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
        }
    }
}

Chain

With Tortuga Chain, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

public class SingleModelCrudAsyncScenario : ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    readonly SqlServerDataSource m_DataSource;

    public SingleModelCrudAsyncScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource ??
            throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");
    }

    public Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        return m_DataSource.Insert(classification).ToInt32().ExecuteAsync();
    }

    public Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        return m_DataSource.Delete(classification).ExecuteAsync();
    }

    public Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        return m_DataSource.DeleteByKey<EmployeeClassification>(employeeClassificationKey).ExecuteAsync();
    }

    public Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName,
        CancellationToken cancellationToken = default)
    {
        return m_DataSource.From<EmployeeClassification>(new { employeeClassificationName })
            .ToObjectOrNull().ExecuteAsync(cancellationToken);
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        return await m_DataSource.From<EmployeeClassification>().ToCollection().ExecuteAsync(cancellationToken)
            .ConfigureAwait(false);
    }

    public Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey,
        CancellationToken cancellationToken = default)
    {
        return m_DataSource.GetByKey<EmployeeClassification>(employeeClassificationKey).ToObjectOrNull()
            .ExecuteAsync(cancellationToken);
    }

    public Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        return m_DataSource.Update(classification).ExecuteAsync();
    }
}

Dapper

For non-cancellable operation, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

If cancellation is required, then you must wrap your parameters in a CommandDefinition. For example,

Original:

virtual public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
    var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                FROM HR.EmployeeClassification ec
                WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
        return con.QuerySingle<EmployeeClassification>(sql, new { employeeClassificationKey });
}

Async with cancellation:

virtual public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
{
    var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                FROM HR.EmployeeClassification ec
                WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = await OpenConnectionAsync().ConfigureAwait(false))
        return await con.QuerySingleAsync<EmployeeClassification>(
            new CommandDefinition(sql, parameters: new { employeeClassificationKey }, cancellationToken: cancellationToken)).ConfigureAwait(false);
}

Here is the full repository.

public class SingleModelCrudAsyncScenario : ScenarioBase, ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    public SingleModelCrudAsyncScenario(string connectionString) : base(connectionString)
    {
    }

    virtual public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return await con.ExecuteScalarAsync<int>(sql, classification).ConfigureAwait(false);
    }

    virtual public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            await con.ExecuteAsync(sql, classification).ConfigureAwait(false);
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            await con.ExecuteAsync(sql, new { employeeClassificationKey }).ConfigureAwait(false);
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        var sql = @"SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return await con.QuerySingleAsync<EmployeeClassification>(
                new CommandDefinition(sql, parameters: new { employeeClassificationName }, cancellationToken: cancellationToken)).ConfigureAwait(false);
    }

    virtual public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        var sql = @"SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return (await con.QueryAsync<EmployeeClassification>(new CommandDefinition(sql, cancellationToken: cancellationToken)).ConfigureAwait(false)).ToList();
    }

    virtual public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return await con.QuerySingleAsync<EmployeeClassification>(
                new CommandDefinition(sql, parameters: new { employeeClassificationKey }, cancellationToken: cancellationToken)).ConfigureAwait(false);
    }

    virtual public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            await con.ExecuteAsync(sql, classification).ConfigureAwait(false);
    }
}

Info

The repository methods are not normally virtual. This was done so that they could be overridden with alternate implementations as shown below.

Dapper.Contrib

The Dapper.Contrib library can elimiante the boilerplate for some common scenarios. However, it doesn't support a CancellationToken so it may not be appropriate for all use cases.

public class SingleModelCrudAsyncScenarioContrib : SingleModelCrudAsyncScenario
{
    public SingleModelCrudAsyncScenarioContrib(string connectionString) : base(connectionString)
    {
    }

    override public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return await con.InsertAsync(classification).ConfigureAwait(false);
    }

    override public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            await con.DeleteAsync(classification).ConfigureAwait(false);
    }

    override public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        //WARNING: Dapper.Contrib does not support CancellationToken
        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return (await con.GetAllAsync<EmployeeClassification>().ConfigureAwait(false)).ToList();
    }

    override public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        //WARNING: Dapper.Contrib does not support CancellationToken
        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            return await con.GetAsync<EmployeeClassification>(employeeClassificationKey).ConfigureAwait(false);
    }

    override public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var con = await OpenConnectionAsync().ConfigureAwait(false))
            await con.UpdateAsync(classification).ConfigureAwait(false);
    }
}

DbConnector

For non-cancellable operation, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

If cancellation is required, then set the CancellationToken when invoking Execute. For example,

Original:

public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
    const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                FROM HR.EmployeeClassification ec
                WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;";

    return DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationKey }).Execute();
}

Async with cancellation:

public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
{
    var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                FROM HR.EmployeeClassification ec
                WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;";

    return await DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationKey })
                            .ExecuteAsync(token: cancellationToken)
                            .ConfigureAwait(false);
}

Here is the full repository.

public class SingleModelCrudAsyncScenario : ScenarioBase, ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    public SingleModelCrudAsyncScenario(string connectionString) : base(connectionString)
    {
    }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        return await DbConnector.Scalar<int>(sql, classification)
                    .ExecuteAsync()
                    .ConfigureAwait(false);
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        await DbConnector.NonQuery(sql, classification)
                         .ExecuteAsync()
                         .ConfigureAwait(false);
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @employeeClassificationKey;";

        await DbConnector.NonQuery(sql, new { employeeClassificationKey })
                         .ExecuteAsync()
                         .ConfigureAwait(false);
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        var sql = @"SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @employeeClassificationName;";

        return await DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationName })
                                .ExecuteAsync(token: cancellationToken)
                                .ConfigureAwait(false);
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        var sql = @"SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";

        return await DbConnector.ReadToList<EmployeeClassification>(sql)
                    .ExecuteAsync(token: cancellationToken)
                    .ConfigureAwait(false);
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;";

        return await DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationKey })
                                .ExecuteAsync(token: cancellationToken)
                                .ConfigureAwait(false);
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        await DbConnector.NonQuery(sql, classification)
                         .ExecuteAsync()
                         .ConfigureAwait(false);
    }
}

Entity Framework 6

To make an Entity Framework repository asynchronous, you need to import the System.Data.Entity namespace. This exposes the async version of the LINQ extension methods needed.

For non-cancellable operation, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

For cancellable operations, you may need to explicitly create object arrays for the parameters. Otherwise it may think that the cancellation token is another query parameter.

Original:

public EmployeeClassification GetByKey(int employeeClassificationKey)
{
    using (var context = CreateDbContext())
    {
        return context.EmployeeClassification.Find(employeeClassificationKey);
    }
}

Async:

public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
{
    using (var context = CreateDbContext())
    {
        //Note the cancellation token is before the parameters
        return await context.EmployeeClassification.FindAsync(cancellationToken, employeeClassificationKey).ConfigureAwait(false);
    }
}

public class SingleModelCrudAsyncScenario : ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public SingleModelCrudAsyncScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            context.EmployeeClassification.Add(classification);
            await context.SaveChangesAsync().ConfigureAwait(false);
            return classification.EmployeeClassificationKey;
        }
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            var temp = await context.EmployeeClassification.FindAsync(classification.EmployeeClassificationKey).ConfigureAwait(false);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                await context.SaveChangesAsync().ConfigureAwait(false);
            }
        }
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = await context.EmployeeClassification.FindAsync(employeeClassificationKey).ConfigureAwait(false);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                await context.SaveChangesAsync().ConfigureAwait(false);
            }
        }
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        using (var context = CreateDbContext())
        {
            return await context.EmployeeClassification.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefaultAsync(cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        using (var context = CreateDbContext())
        {
            return await context.EmployeeClassification.ToListAsync(cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        using (var context = CreateDbContext())
        {
            //Note the cancellation token is before the parameters
            return await context.EmployeeClassification.FindAsync(cancellationToken, employeeClassificationKey).ConfigureAwait(false);
        }
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            var temp = await context.EmployeeClassification.FindAsync(classification.EmployeeClassificationKey).ConfigureAwait(false);
            if (temp != null)
            {
                temp.EmployeeClassificationName = classification.EmployeeClassificationName;
                await context.SaveChangesAsync().ConfigureAwait(false);
            }
        }
    }
}

Entity Framework Core

To make an Entity Framework repository asynchronous, you need to import the System.Data.Entity namespace. This exposes the async version of the LINQ extension methods needed.

For non-cancellable operation, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

For cancellable operations, you may need to explicitly create object arrays for the parameters. Otherwise it may think that the cancellation token is another query parameter.

Original:

public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
    using (var context = CreateDbContext())
    {
        return context.EmployeeClassifications.Find(employeeClassificationKey);
    }
}

Async:

public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
{
    using (var context = CreateDbContext())
    {
        return await context.EmployeeClassifications.FindAsync(new object[] { employeeClassificationKey }, cancellationToken).ConfigureAwait(false);
    }
}

public class SingleModelCrudAsyncScenario : ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public SingleModelCrudAsyncScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            context.EmployeeClassifications.Add(classification);
            await context.SaveChangesAsync().ConfigureAwait(false);
            return classification.EmployeeClassificationKey;
        }
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            var temp = await context.EmployeeClassifications.FindAsync(classification.EmployeeClassificationKey).ConfigureAwait(false);
            if (temp != null)
            {
                context.EmployeeClassifications.Remove(temp);
                await context.SaveChangesAsync().ConfigureAwait(false);
            }
        }
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = await context.EmployeeClassifications.FindAsync(employeeClassificationKey).ConfigureAwait(false);
            if (temp != null)
            {
                context.EmployeeClassifications.Remove(temp);
                await context.SaveChangesAsync().ConfigureAwait(false);
            }
        }
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        using (var context = CreateDbContext())
        {
            return await context.EmployeeClassifications.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefaultAsync(cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        using (var context = CreateDbContext())
        {
            return await context.EmployeeClassifications.ToListAsync(cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        using (var context = CreateDbContext())
        {
            return await context.EmployeeClassifications.FindAsync(new object[] { employeeClassificationKey }, cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            var temp = await context.EmployeeClassifications.FindAsync(classification.EmployeeClassificationKey).ConfigureAwait(false);
            if (temp != null)
            {
                temp.EmployeeClassificationName = classification.EmployeeClassificationName;
                await context.SaveChangesAsync().ConfigureAwait(false);
            }
        }
    }
}

LINQ to DB

With LINQ to DB, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = new OrmCookbook())
        {
            return db.InsertWithInt32Identity(classification);
        }
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = new OrmCookbook())
        {
            db.EmployeeClassification
                .Where(d => d.EmployeeClassificationKey == classification.EmployeeClassificationKey)
                .Delete();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            db.EmployeeClassification
                .Where(d => d.EmployeeClassificationKey == employeeClassificationKey)
                .Delete();
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var db = new OrmCookbook())
        {
            var query = from ec in db.EmployeeClassification
                        where ec.EmployeeClassificationName == employeeClassificationName
                        select ec;
            return query.Single();
        }
    }

    public IList<EmployeeClassification> GetAll()
    {
        using (var db = new OrmCookbook())
        {
            return db.EmployeeClassification.ToList();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            return db.EmployeeClassification.Where(d => d.EmployeeClassificationKey == employeeClassificationKey).Single();
        }
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = new OrmCookbook())
        {
            db.Update(classification);
        }
    }
}

LLBLGen Pro

With LLBLGen Pro, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

 public class SingleModelCrudAsyncScenario : ISingleModelCrudAsyncScenario<EmployeeClassificationEntity>
 {
     public async Task<int> CreateAsync(EmployeeClassificationEntity classification)
     {
         if (classification == null)
             throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

         using (var adapter = new DataAccessAdapter())
         {
             await adapter.SaveEntityAsync(classification).ConfigureAwait(false);
             return classification.EmployeeClassificationKey;
         }
     }

     public async Task DeleteAsync(EmployeeClassificationEntity classification)
     {
         if (classification == null)
             throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

         using (var adapter = new DataAccessAdapter())
         {
             classification.IsNew = false;
             await adapter.DeleteEntityAsync(classification).ConfigureAwait(false);
         }
     }

     public async Task DeleteByKeyAsync(int employeeClassificationKey)
     {
         using (var adapter = new DataAccessAdapter())
         {
             await adapter.DeleteEntitiesDirectlyAsync(typeof(EmployeeClassificationEntity),
                                                       new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                                                                                      .Equal(employeeClassificationKey)))
                 .ConfigureAwait(false);
         }
     }

     public async Task<EmployeeClassificationEntity?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
     {
         using (var adapter = new DataAccessAdapter())
{
    return await new LinqMetaData(adapter).EmployeeClassification
                                          .Where(ec => ec.EmployeeClassificationName == employeeClassificationName)
                                          .SingleOrDefaultAsync(cancellationToken).ConfigureAwait(false);
}
     }

     public async Task<IList<EmployeeClassificationEntity>> GetAllAsync(CancellationToken cancellationToken = default)
     {
         using (var adapter = new DataAccessAdapter())
         {
             return await new LinqMetaData(adapter).EmployeeClassification.ToListAsync(cancellationToken).ConfigureAwait(false);
         }
     }

     public async Task<EmployeeClassificationEntity?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
     {
         using (var adapter = new DataAccessAdapter())
         {
             return await new LinqMetaData(adapter).EmployeeClassification
                                          .FirstOrDefaultAsync(ec => ec.EmployeeClassificationKey == employeeClassificationKey, cancellationToken)
                                          .ConfigureAwait(false);
         }
     }

     public async Task UpdateAsync(EmployeeClassificationEntity classification)
     {
         if (classification == null)
             throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

         using (var adapter = new DataAccessAdapter())
         {
             EmployeeClassificationEntity toPersist = classification;
             if (classification.IsNew)
             {
                 toPersist = await new LinqMetaData(adapter).EmployeeClassification
                                             .FirstOrDefaultAsync(ec => ec.EmployeeClassificationKey == classification.EmployeeClassificationKey)
                                             .ConfigureAwait(false);
                 if (toPersist != null)
                 {
                     toPersist.EmployeeClassificationName = classification.EmployeeClassificationName;
                 }
             }
             if (toPersist != null && !toPersist.IsNew)
             {
                 await adapter.SaveEntityAsync(toPersist, refetchAfterSave: false, recurse: false).ConfigureAwait(false);
             }
         }
     }
 }

NHibernate

With NHibernate, the only changes are to add await, async, and .ConfigureAwait(false) to the appropriate places.

public class SingleModelCrudAsyncScenario : ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    readonly ISessionFactory m_SessionFactory;

    public SingleModelCrudAsyncScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        {
            await session.SaveAsync(classification).ConfigureAwait(false);
            await session.FlushAsync().ConfigureAwait(false);
            return classification.EmployeeClassificationKey;
        }
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        {
            await session.DeleteAsync(classification).ConfigureAwait(false);
            await session.FlushAsync().ConfigureAwait(false);
        }
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            await session.DeleteAsync(new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey }).ConfigureAwait(false);
            await session.FlushAsync().ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return (await session.QueryOver<EmployeeClassification>()
                .Where(ec => ec.EmployeeClassificationName == employeeClassificationName)
                .ListAsync(cancellationToken).ConfigureAwait(false))
                .SingleOrDefault();
        }
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return await session
                .QueryOver<EmployeeClassification>()
                .ListAsync(cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return await session.GetAsync<EmployeeClassification>(employeeClassificationKey, cancellationToken).ConfigureAwait(false);
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        {
            await session.UpdateAsync(classification).ConfigureAwait(false);
            await session.FlushAsync().ConfigureAwait(false);
        }
    }
}

RepoDb

With RepoDb, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

public class SingleModelCrudAsyncScenario : BaseRepository<EmployeeClassification, SqlConnection>,
    ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    public SingleModelCrudAsyncScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        return await InsertAsync<int>(classification).ConfigureAwait(false);
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        await base.DeleteAsync(classification).ConfigureAwait(false);
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        await DeleteAsync(employeeClassificationKey).ConfigureAwait(false);
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName, CancellationToken cancellationToken = default)
    {
        return (await QueryAsync(e => e.EmployeeClassificationName == employeeClassificationName)
            .ConfigureAwait(false)).FirstOrDefault();
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        return (await QueryAllAsync().ConfigureAwait(false)).AsList();
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey, CancellationToken cancellationToken = default)
    {
        return (await QueryAsync(employeeClassificationKey).ConfigureAwait(false)).FirstOrDefault();
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        await base.UpdateAsync(classification).ConfigureAwait(false);
    }
}

ServiceStack

With ServiceStack, the only changes are to add await, Async, and .ConfigureAwait(false) to the appropriate places.

public class SingleModelCrudAsyncScenario : ISingleModelCrudAsyncScenario<EmployeeClassification>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public SingleModelCrudAsyncScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public async Task<int> CreateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return (int)await db.InsertAsync(classification, true).ConfigureAwait(false);
        }
    }

    public async Task DeleteAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            await db.DeleteAsync(classification).ConfigureAwait(false);
        }
    }

    public async Task DeleteByKeyAsync(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            await db.DeleteByIdAsync<EmployeeClassification>(employeeClassificationKey).ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> FindByNameAsync(string employeeClassificationName,
        CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(employeeClassificationName))
            throw new ArgumentNullException(nameof(employeeClassificationName), $"{nameof(employeeClassificationName)} is null or whitespace.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return await db.SingleAsync<EmployeeClassification>(
                    r => r.EmployeeClassificationName == employeeClassificationName, cancellationToken)
                .ConfigureAwait(false);
        }
    }

    public async Task<IList<EmployeeClassification>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return await db.SelectAsync<EmployeeClassification>(cancellationToken).ConfigureAwait(false);
        }
    }

    public async Task<EmployeeClassification?> GetByKeyAsync(int employeeClassificationKey,
                CancellationToken cancellationToken = default)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return await db.SingleByIdAsync<EmployeeClassification>(employeeClassificationKey, cancellationToken)
                .ConfigureAwait(false);
        }
    }

    public async Task UpdateAsync(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            await db.UpdateAsync(classification).ConfigureAwait(false);
        }
    }
}