Single Model CRUD

This scenario covers the basic Create-Read-Update-Delete operations on a model that represents a single row in the database.

Scenario Prototype

public interface ISingleModelCrudScenario<TEmployeeClassification>
   where TEmployeeClassification : class, IEmployeeClassification, new()
{
    /// <summary>
    /// Create a new EmployeeClassification row, returning the new primary key.
    /// </summary>
    int Create(TEmployeeClassification classification);

    /// <summary>
    /// Delete a EmployeeClassification row using an object.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Delete(TEmployeeClassification classification);

    /// <summary>
    /// Delete a EmployeeClassification row using its primary key.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void DeleteByKey(int employeeClassificationKey);

    /// <summary>
    /// Gets an EmployeeClassification row by its name. Assume the name is unique.
    /// </summary>
    TEmployeeClassification? FindByName(string employeeClassificationName);

    /// <summary>
    /// Gets all EmployeeClassification rows.
    /// </summary>
    IList<TEmployeeClassification> GetAll();

    /// <summary>
    /// Gets an EmployeeClassification row by its primary key.
    /// </summary>
    TEmployeeClassification? GetByKey(int employeeClassificationKey);

    /// <summary>
    /// Update a EmployeeClassification row.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Update(TEmployeeClassification classification);
}

ADO.NET

With ADO.NET, the model does not actually participate in database operations so it needs no adornment.

public class EmployeeClassification : IEmployeeClassification
{
    public EmployeeClassification()
    {
    }

    public EmployeeClassification(IDataReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException(nameof(reader), $"{nameof(reader)} is null.");

        EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey"));
        EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"));
        IsExempt = reader.GetBoolean(reader.GetOrdinal("IsExempt"));
        IsEmployee = reader.GetBoolean(reader.GetOrdinal("IsEmployee"));
    }

    public int EmployeeClassificationKey { get; set; }
    public string? EmployeeClassificationName { get; set; }
    public bool IsEmployee { get; set; }
    public bool IsExempt { get; set; }
}

The repository methods use raw SQL strings. All other ORMs internally generate the same code.

SQL Server

To return a primary key from an INSERT statement, use OUTPUT Inserted.EmployeeClassificationKey.

public class SingleModelCrudScenario : SqlServerScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
    public SingleModelCrudScenario(string connectionString) : base(connectionString)
    { }

    public int Create(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 = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            return (int)cmd.ExecuteScalar();
        }
    }

    public void Delete(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 = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

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

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", employeeClassificationName);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

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

    public IList<EmployeeClassification> GetAll()
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";

        var result = new List<EmployeeClassification>();

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                result.Add(new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
                });
            }
            return result;
        }
    }

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

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

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

    public void Update(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 = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            cmd.ExecuteNonQuery();
        }
    }
}

PostgreSQL

To return a primary key from an INSERT statement, use RETURNING EmployeeClassificationKey.

public class SingleModelCrudPostgreSqlScenario : PostgreSqlScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
    public SingleModelCrudPostgreSqlScenario(string connectionString) : base(connectionString)
    { }

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

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

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            return (int)cmd.ExecuteScalar();
        }
    }

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

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

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        const string sql = @"DELETE FROM HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

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

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", employeeClassificationName);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

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

    public IList<EmployeeClassification> GetAll()
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";

        var result = new List<EmployeeClassification>();

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                result.Add(new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
                });
            }
            return result;
        }
    }

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

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

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

    public void Update(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 = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            cmd.ExecuteNonQuery();
        }
    }
}

Chain

Strictly speaking, Chain can use the same models as ADO.NET and Dapper so long as the column and property names match. However, it is more convenient to tag the class with what table it refers to.

[Table("HR.EmployeeClassification")]
public class EmployeeClassification : IEmployeeClassification
{
    public int EmployeeClassificationKey { get; set; }
    public string? EmployeeClassificationName { get; set; }
    public bool IsEmployee { get; set; }
    public bool IsExempt { get; set; }
}

Without the Table attribute, the table name will have to be specified in every call in the repository.

Other information such as primary keys are read from the database's metadata.

public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
    readonly SqlServerDataSource m_DataSource;

    public SingleModelCrudScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

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

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

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

        m_DataSource.Delete(classification).Execute();
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        m_DataSource.DeleteByKey<EmployeeClassification>(employeeClassificationKey).Execute();
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        return m_DataSource.From<EmployeeClassification>(new { employeeClassificationName })
            .ToObject().Execute();
    }

    public IList<EmployeeClassification> GetAll()
    {
        return m_DataSource.From<EmployeeClassification>().ToCollection().Execute();
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        return m_DataSource.GetByKey<EmployeeClassification>(employeeClassificationKey)
            .ToObject().Execute();
    }

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

        m_DataSource.Update(classification).Execute();
    }
}

Dapper

Dapper is essentially just ADO.NET with some helper methods to reduce the amount of boilerplate code.

public class SingleModelCrudScenario : ScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
    public SingleModelCrudScenario(string connectionString) : base(connectionString)
    {
    }

    virtual public int Create(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 = OpenConnection())
            return con.ExecuteScalar<int>(sql, classification);
    }

    virtual public void Delete(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 = OpenConnection())
            con.Execute(sql, classification);
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = OpenConnection())
            con.Execute(sql, new { employeeClassificationKey });
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        var sql = @"SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;";

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

    virtual public IList<EmployeeClassification> GetAll()
    {
        var sql = @"SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";

        using (var con = OpenConnection())
            return con.Query<EmployeeClassification>(sql).ToList();
    }

    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 });
    }

    virtual public void Update(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 = OpenConnection())
            con.Execute(sql, classification);
    }
}
Info

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

Dapper.Contrib

The Dapper.Contrib library can elimiante the boilerplate for some common scenarios.

To enable it, models need to be decorated with Table and Key attributes.

[Table("HR.EmployeeClassification")]
public class EmployeeClassification : IEmployeeClassification
{
    //Table and Key attributes are only used by Dapper.Contrib.
    //They are not needed in the Dapper-only examples.

    [Key]
    public int EmployeeClassificationKey { get; set; }

    public string? EmployeeClassificationName { get; set; }
    public bool IsEmployee { get; set; }
    public bool IsExempt { get; set; }
}
public class SingleModelCrudScenarioContrib : SingleModelCrudScenario
{
    public SingleModelCrudScenarioContrib(string connectionString) : base(connectionString)
    {
    }

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

        using (var con = OpenConnection())
            return (int)con.Insert(classification);
    }

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

        using (var con = OpenConnection())
            con.Delete(classification);
    }

    override public IList<EmployeeClassification> GetAll()
    {
        using (var con = OpenConnection())
            return con.GetAll<EmployeeClassification>().ToList();
    }

    override public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
            return con.Get<EmployeeClassification>(employeeClassificationKey);
    }

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

        using (var con = OpenConnection())
            con.Update(classification);
    }
}

Entity Framework 6

public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

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

        using (var context = CreateDbContext())
        {
            context.EmployeeClassification.Add(classification);
            context.SaveChanges();
            return classification.EmployeeClassificationKey;
        }
    }

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

        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassification.Find(employeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList<EmployeeClassification> GetAll()
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.ToList();
        }
    }

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

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

        using (var context = CreateDbContext())
        {
            //Get a fresh copy of the row from the database
            var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                //Copy the changed fields
                temp.EmployeeClassificationName = classification.EmployeeClassificationName;
                context.SaveChanges();
            }
        }
    }
}
Info

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

Entity Framework 6 - Improved

The design of Entity Framework 6 requires extraneous database calls when performing an update or delete operation. This revised version eliminates the extra calls.

public class SingleModelCrudScenario2 : SingleModelCrudScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public SingleModelCrudScenario2(Func<OrmCookbookContext> dBContextFactory) : base(dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

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

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public override void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
            context.Entry(temp).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

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

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

Entity Framework Core

To use Entity Framework, one needs to create a DbContext class. Here is a minimal example:

public partial class OrmCookbook : DbContext
{
    public OrmCookbook()
        : base("name=OrmCookbook")
    {
    }

    public virtual DbSet<EmployeeClassification> EmployeeClassifications { get; set; }

}

Depending on how you setup the DbContext, the model requires some further annotations such as which table it applies to and what the primary key is.

[Table("HR.EmployeeClassification")]
public partial class EmployeeClassification
{
    [Key]
    public int EmployeeClassificationKey { get; set; }

    [StringLength(30)]
    public string EmployeeClassificationName { get; set; }
}

The context and model can be generated for you from the database using Entity Framework’s “Code First” tooling. (The name “code first” doesn’t literally mean the code has to be written before the database. Rather, it really means that you are not using EDMX style XML files.) For more information see https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx

Finally, there is the repository itself:

public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

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

        using (var context = CreateDbContext())
        {
            context.EmployeeClassification.Add(classification);
            context.SaveChanges();
            return classification.EmployeeClassificationKey;
        }
    }

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

        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassification.Find(employeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList<EmployeeClassification> GetAll()
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.ToList();
        }
    }

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

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

        using (var context = CreateDbContext())
        {
            //Get a fresh copy of the row from the database
            var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                //Copy the changed fields
                temp.EmployeeClassificationName = classification.EmployeeClassificationName;
                context.SaveChanges();
            }
        }
    }
}
Info

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

Entity Framework Core - Improved

The design of Entity Framework Core requires extraneous database calls when performing an update or delete operation. This revised version eliminates the extra calls.

public class SingleModelCrudScenario2 : SingleModelCrudScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public SingleModelCrudScenario2(Func<OrmCookbookContext> dBContextFactory) : base(dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

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

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public override void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
            context.Entry(temp).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

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

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

LINQ to DB

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

LLBLGen Pro offers multiple ways to perform CRUD operations: via entity instances or directly on the data in the database, to avoid a fetch of entities first. The code below illustrates this and multiple query systems. Entity types are derived from a common base class.

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

        using (var adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(classification, true, recurse: false);
            return classification.EmployeeClassificationKey;
        }
    }

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

        using (var adapter = new DataAccessAdapter())
        {
            // flag the entity as not-new, so we can delete it without fetching it first if the PK is set.
            classification.IsNew = false;
            adapter.DeleteEntity(classification);
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            // delete directly, so we don't have to fetch the entity first.
            adapter.DeleteEntitiesDirectly(typeof(EmployeeClassificationEntity),
                                           new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                                                                       .Equal(employeeClassificationKey)));
        }
    }

    public EmployeeClassificationEntity FindByName(string employeeClassificationName)
    {
        using (var adapter = new DataAccessAdapter())
        {
            // let's use QuerySpec
            return adapter.FetchFirst(new QueryFactory().EmployeeClassification
                                            .Where(EmployeeClassificationFields.EmployeeClassificationName
                                                                               .Equal(employeeClassificationName)));
        }
    }

    public IList<EmployeeClassificationEntity> GetAll()
    {
        using (var adapter = new DataAccessAdapter())
        {
            // you know what, let's use the low level API for a change.
            var toReturn = new EntityCollection<EmployeeClassificationEntity>();
            adapter.FetchEntityCollection(toReturn, null);
            return toReturn;
        }
    }

    public EmployeeClassificationEntity GetByKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeClassification
                                .FirstOrDefault(ec => ec.EmployeeClassificationKey == employeeClassificationKey);
        }
    }

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

        using (var adapter = new DataAccessAdapter())
        {
            // re-use existing entity (as it tracks changes internally) otherwise fetch the instance from the DB
            EmployeeClassificationEntity toPersist = classification;
            if (classification.IsNew)
            {
                toPersist = adapter.FetchNewEntity<EmployeeClassificationEntity>(
                            new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                                                                    .Equal(classification.EmployeeClassificationKey)));
                toPersist.EmployeeClassificationName = classification.EmployeeClassificationName;
            }
            if (!toPersist.IsNew)
            {
                adapter.SaveEntity(toPersist, refetchAfterSave: false, recurse: false);
            }
        }
    }
}

NHibernate

public partial class EmployeeClassification : IEmployeeClassification
{
    public virtual int EmployeeClassificationKey { get; set; }

    public virtual string? EmployeeClassificationName { get; set; }
    public virtual bool IsEmployee { get; set; }
    public virtual bool IsExempt { get; set; }
}

Instead of attributes, a mapping file is used to associate the model with a database table. There is one file per table and each is set to Build Action: Embedded resource.

<hibernate-mapping
  assembly="Recipes.NHibernate"
  namespace="Recipes.NHibernate.Entities">
  <!-- more mapping info here -->
  <class
    name="EmployeeClassification"
    table="EmployeeClassification"
    schema="HR">
    <id
      name="EmployeeClassificationKey">
      <generator
        class="native" />
    </id>
    <property
      name="EmployeeClassificationName" />
    <property
      name="IsExempt" />
    <property
      name="IsEmployee" />
  </class>
</hibernate-mapping>

A SessionFactory is needed to stitch the various configuration files together.

private static void ConfigureSessionFactory()
{
    var jsonConfiguration = new ConfigurationBuilder().SetBasePath(AppContext.BaseDirectory).AddJsonFile("appsettings.json").Build();
    var sqlServerConnectionString = jsonConfiguration.GetSection("ConnectionStrings")["SqlServerTestDatabase"];

    var configuration = new Configuration();
    configuration.Configure();

    configuration.SetProperty(NHibernateCfg.Environment.ConnectionString, sqlServerConnectionString);

    configuration.AddAssembly(typeof(Setup).Assembly);
    SessionFactory = configuration.BuildSessionFactory();

    try
    {
        (new Setup()).Warmup();
    }
    catch { }
}

Finally there is the repository itself.

public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
    readonly ISessionFactory m_SessionFactory;

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

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

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(classification);
            session.Flush();
            return classification.EmployeeClassificationKey;
        }
    }

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

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(classification);
            session.Flush();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey });
            session.Flush();
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver<EmployeeClassification>().Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList<EmployeeClassification> GetAll()
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver<EmployeeClassification>().List();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return session.Get<EmployeeClassification>(employeeClassificationKey);
    }

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

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Update(classification);
            session.Flush();
        }
    }
}

The rules on when you need to call Flush are complex. In some cases it will be called for you implicitly, but as a general rule you need to invoke it before leaving a block that includes modifications.

RepoDb

When calling the raw-SQL operations, just like Dapper, RepoDB requires annotations on the classes. These are specific to RepoDb, you cannot use the standard Table, Column, and Key attributes from .NET.

[Map("[HR].[EmployeeClassification]")]
public class EmployeeClassification : IEmployeeClassification
{
    public EmployeeClassification()
    {
    }

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

        EmployeeClassificationKey = classification.EmployeeClassificationKey;
        EmployeeClassificationName = classification.EmployeeClassificationName;
        IsExempt = classification.IsExempt;
        IsEmployee = classification.IsEmployee;
    }

    public int EmployeeClassificationKey { get; set; }

    public string? EmployeeClassificationName { get; set; }

    public bool IsEmployee { get; set; }

    public bool IsExempt { get; set; }

    internal ReadOnlyEmployeeClassification ToImmutable()
    {
        return new ReadOnlyEmployeeClassification(this);
    }
}

The repository resembles Dapper, but with far less SQL.

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

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

        return Insert<int>(classification);
    }

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

        base.Delete(classification);
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        Delete(employeeClassificationKey);
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        return Query(e => e.EmployeeClassificationName == employeeClassificationName).FirstOrDefault();
    }

    public IList<EmployeeClassification> GetAll()
    {
        return QueryAll().AsList();
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        return Query(employeeClassificationKey).FirstOrDefault();
    }

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

        base.Update(classification);
    }
}

ServiceStack

ServiceStack requires the use of annotations on its models. These are specific to ServiceStack, you cannot use the standard Table, Column, and Key attributes from .NET.

[Alias("EmployeeClassification"), Schema("HR")]
public partial class EmployeeClassification
{
    [PrimaryKey, AutoIncrement, Alias("EmployeeClassificationKey")]
    public int Id { get; set; }

    [Required, StringLength(30)]
    public string? EmployeeClassificationName { get; set; }

    public bool IsExempt { get; set; }

    public bool IsEmployee { get; set; }

    [Reference]
    [SuppressMessage("Usage", "CA2227:Collection properties should be read only", Justification = "Required by ServiceStack")]
    public List<Employee> Employees { get; set; } = new List<Employee>();
}

The repository resemebles Dapper, but with far less SQL.

public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

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

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

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return (int)db.Insert(classification, true);
        }
    }

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

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Delete(classification);
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.DeleteById<EmployeeClassification>(employeeClassificationKey);
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.Single<EmployeeClassification>(
                r => r.EmployeeClassificationName == employeeClassificationName);
        }
    }

    public IList<EmployeeClassification> GetAll()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.Select<EmployeeClassification>();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.SingleById<EmployeeClassification>(employeeClassificationKey);
        }
    }

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

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Update(classification);
        }
    }
}