Soft Delete

These scenarios demonstrate how to use soft deletes (i.e. IsDeleted flag) rather than hard deletes.

Scenario Prototype

public interface ISoftDeleteScenario<TDepartment>
   where TDepartment : class, IDepartment, new()
{
    /// <summary>
    /// Creates the department.
    /// </summary>
    /// <param name="department">The department.</param>
    /// <param name="user">The user.</param>
    /// <returns>System.Int32.</returns>
    int CreateDepartment(TDepartment department);

    /// <summary>
    /// Deletes the department.
    /// </summary>
    /// <param name="department">The department.</param>
    void DeleteDepartment(TDepartment department);

    /// <summary>
    /// Updates the department.
    /// </summary>
    /// <param name="department">The department.</param>
    /// <param name="user">The user.</param>
    void UpdateDepartment(TDepartment department);

    /// <summary>
    /// Undeletes the department.
    /// </summary>
    /// <param name="departmentKey">The department key.</param>
    void UndeleteDepartment(int departmentKey);

    /// <summary>
    /// Gets the department.
    /// </summary>
    /// <param name="departmentKey">The department key.</param>
    /// <returns>TDepartment.</returns>
    TDepartment? GetDepartment(int departmentKey);

    /// <summary>
    /// Gets the department ignoring the IsDeleted flag.
    /// </summary>
    /// <param name="departmentKey">The department key.</param>
    /// <returns>System.Nullable&lt;TDepartment&gt;.</returns>
    TDepartment? GetDepartmentIgnoringIsDeleted(int departmentKey);
}

ADO.NET

TODO

Chain

Chain requires that soft delete be configured at the DataSource level.

public class SoftDeleteScenario : ISoftDeleteScenario<Department>
{
    private const string TableName = "HR.Department";
    readonly SqlServerDataSource m_DataSource;
    readonly SqlServerDataSource m_DataSourceBypassSoftDelete;

    public SoftDeleteScenario(SqlServerDataSource dataSource)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");

        //We need the original data source without the soft delete rule enabled for the by-pass scenario.
        m_DataSourceBypassSoftDelete = dataSource;

        //Normally this would be configured application-wide, not just for one repository instance.
        m_DataSource = dataSource.WithRules(
        new SoftDeleteRule("IsDeleted", true, OperationTypes.SelectOrDelete)
        );
    }

    public int CreateDepartment(Department department, User user)
    {
        return m_DataSource.WithUser(user).Insert(department).ToInt32().Execute();
    }

    public int CreateDepartment(Department department)
    {
        return m_DataSource.Insert(department).ToInt32().Execute();
    }

    public void DeleteDepartment(Department department)
    {
        m_DataSource.Delete(department).Execute();
    }

    public Department? GetDepartment(int departmentKey)
    {
        return m_DataSource.GetByKey<Department>(departmentKey).ToObjectOrNull().Execute();
    }

    public Department? GetDepartmentIgnoringIsDeleted(int departmentKey)
    {
        return m_DataSourceBypassSoftDelete.GetByKey<Department>(departmentKey).ToObjectOrNull().Execute();
    }

    public void UndeleteDepartment(int departmentKey)
    {
        //The SoftDeleteRule does not apply to Update operations
        m_DataSource.Update(TableName, new { departmentKey, IsDeleted = false }).Execute();
    }

    public void UpdateDepartment(Department department)
    {
        m_DataSource.Update(department).Execute();
    }
}

Dapper

TODO

DbConnector

public class SoftDeleteScenario : ScenarioBase, ISoftDeleteScenario<Department>
{
    public SoftDeleteScenario(string connectionString) : base(connectionString)
    {
    }

    public int CreateDepartment(Department department)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        string sql = $@"INSERT INTO {Department.TableName} 
                    (   
                        DepartmentName, 
                        DivisionKey, 
                        CreatedDate,
                        ModifiedDate,
                        CreatedByEmployeeKey, 
                        ModifiedByEmployeeKey, 
                        IsDeleted
                    )
                    OUTPUT Inserted.DepartmentKey
                    VALUES
                    (
                        @{nameof(Department.DepartmentName)},
                        @{nameof(Department.DivisionKey)},
                        @{nameof(Department.CreatedDate)},
                        @{nameof(Department.ModifiedDate)},
                        @{nameof(Department.CreatedByEmployeeKey)},
                        @{nameof(Department.ModifiedByEmployeeKey)},
                        @{nameof(Department.IsDeleted)}
                    )";

        return DbConnector.Scalar<int>(sql, department).Execute();
    }

    public void DeleteDepartment(Department department)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        string sql = $@"
                        UPDATE {Department.TableName} 
                        SET
                            IsDeleted = @isDeleted
                        WHERE DepartmentKey = @DepartmentKey;";

        DbConnector.NonQuery(sql, new { department.DepartmentKey, isDeleted = true }).Execute();
    }

    public Department? GetDepartment(int departmentKey)
    {
        string sql = $"Select * FROM {Department.TableName} WHERE DepartmentKey = @departmentKey AND IsDeleted = 0;";

        return DbConnector.ReadFirstOrDefault<Department>(sql, new { departmentKey }).Execute();
    }

    public Department? GetDepartmentIgnoringIsDeleted(int departmentKey)
    {
        string sql = $"Select * FROM {Department.TableName} WHERE DepartmentKey = @departmentKey;";

        return DbConnector.ReadFirstOrDefault<Department>(sql, new { departmentKey }).Execute();
    }

    public void UndeleteDepartment(int departmentKey)
    {
        string sql = $@"
                        UPDATE {Department.TableName} 
                        SET
                            IsDeleted = @isDeleted
                        WHERE DepartmentKey = @departmentKey;";

        DbConnector.NonQuery(sql, new { departmentKey, isDeleted = false }).Execute();
    }

    public void UpdateDepartment(Department department)
    {
        string sql = $@"
                        UPDATE {Department.TableName} 
                        SET 
                            DepartmentName = @{nameof(Department.DepartmentName)},
                            DivisionKey = @{nameof(Department.DivisionKey)},
                            CreatedDate = @{nameof(Department.CreatedDate)},
                            ModifiedDate = @{nameof(Department.ModifiedDate)},
                            CreatedByEmployeeKey = @{nameof(Department.CreatedByEmployeeKey)},
                            ModifiedByEmployeeKey = @{nameof(Department.ModifiedByEmployeeKey)},
                            IsDeleted = @{nameof(Department.IsDeleted)}
                        WHERE DepartmentKey = @{nameof(Department.DepartmentKey)};";

        DbConnector.NonQuery(sql, department).Execute();
    }
}

Entity Framework 6

To generalize soft delete support in EF 6, create an interface with the soft delete column(s). Then overide the SaveChanges method to provide the values.

For the select operations, you'll also need to manually filter out deleted columns.

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
}

public override int SaveChanges()
{
    // Get deleted entries
    var deletedEntryCollection = ChangeTracker.Entries<ISoftDeletable>()
        .Where(p => p.State == EntityState.Deleted);

    // Set flags on deleted entries
    foreach (var entry in deletedEntryCollection)
    {
        entry.State = EntityState.Modified;
        entry.Entity.IsDeleted = true;
    }

    return base.SaveChanges();
}

public class SoftDeleteScenario : ISoftDeleteScenario<Department>
{
    private Func<OrmCookbookContextWithSoftDelete> CreateDbContext;
    private Func<OrmCookbookContext> CreateBypassDbContext;

    public SoftDeleteScenario(Func<OrmCookbookContextWithSoftDelete> dBContextFactory, Func<OrmCookbookContext> bypassContextFactory)
    {
        CreateDbContext = dBContextFactory;
        CreateBypassDbContext = bypassContextFactory;
    }

    public int CreateDepartment(Department department)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        using (var context = CreateDbContext())
        {
            context.Department.Add(department);
            context.SaveChanges();
            return department.DepartmentKey;
        }
    }

    public void DeleteDepartment(Department department)
    {
        using (var context = CreateDbContext())
        {
            context.Entry(department).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public Department? GetDepartment(int departmentKey)
    {
        using (var context = CreateDbContext())
            return context.Department
                .Where(d => !d.IsDeleted) //Removed deleted rows
                .Where(d => d.DepartmentKey == departmentKey) //Any additional filtering
                .SingleOrDefault();
    }

    public Department? GetDepartmentIgnoringIsDeleted(int departmentKey)
    {
        using (var context = CreateBypassDbContext())
            return context.Department.Find(departmentKey);
    }

    public void UndeleteDepartment(int departmentKey)
    {
        using (var context = CreateDbContext())
            context.Database.ExecuteSqlCommand("UPDATE HR.Department SET IsDeleted = 0 WHERE DepartmentKey = @p0", departmentKey);
    }

    public void UpdateDepartment(Department department)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

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

Entity Framework Core

To generalize soft delete support in EF Core, create an interface with the soft delete column(s). Then overide the SaveChanges method to provide the values.

For the select operations, you'll also need to add a HasQueryFilter for each entity that supported soft deletes.

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
}

public override int SaveChanges()
{
    // Get deleted entries
    var deletedEntryCollection = ChangeTracker.Entries<ISoftDeletable>()
        .Where(p => p.State == EntityState.Deleted);

    // Set flags on deleted entries
    foreach (var entry in deletedEntryCollection)
    {
        entry.State = EntityState.Modified;
        entry.Entity.IsDeleted = true;
    }

    return base.SaveChanges();
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    if (modelBuilder == null)
        throw new ArgumentNullException(nameof(modelBuilder), $"{nameof(modelBuilder)} is null.");

    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Department>().HasQueryFilter(m => EF.Property<bool>(m, "IsDeleted") == false);
}

public class SoftDeleteScenario : ISoftDeleteScenario<Department>
{
    private Func<OrmCookbookContextWithSoftDelete> CreateDbContext;
    private Func<OrmCookbookContext> CreateBypassDbContext;

    public SoftDeleteScenario(Func<OrmCookbookContextWithSoftDelete> dBContextFactory, Func<OrmCookbookContext> bypassContextFactory)
    {
        CreateDbContext = dBContextFactory;
        CreateBypassDbContext = bypassContextFactory;
    }

    public int CreateDepartment(Department department)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        using (var context = CreateDbContext())
        {
            context.Departments.Add(department);
            context.SaveChanges();
            return department.DepartmentKey;
        }
    }

    public void DeleteDepartment(Department department)
    {
        using (var context = CreateDbContext())
        {
            context.Entry(department).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public Department? GetDepartment(int departmentKey)
    {
        using (var context = CreateDbContext())
            return context.Departments.Find(departmentKey);
    }

    public Department? GetDepartmentIgnoringIsDeleted(int departmentKey)
    {
        using (var context = CreateBypassDbContext())
            return context.Departments.Find(departmentKey);
    }

    public void UndeleteDepartment(int departmentKey)
    {
        using (var context = CreateDbContext())
            context.Database.ExecuteSqlInterpolated($"UPDATE HR.Department SET IsDeleted = 0 WHERE DepartmentKey = {departmentKey}");
    }

    public void UpdateDepartment(Department department)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

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

LINQ to DB

TODO

LLBLGen Pro

TODO

NHibernate

TODO

RepoDb

TODO

ServiceStack

TODO