Use ExpressionVisitor to Exclude Soft Deleted Records In Joins

Question

I have a framework which implements Soft Deletes in the Database (Nullable DateTime called DeletedDate). I am using a Repository to handle the main entity requests like so:

/// <summary>
/// Returns a Linq Queryable instance of the entity collection.
/// </summary>
public IQueryable<T> All
{
    get { return Context.Set<T>().Where(e => e.DeletedDate == null); }
}

This works great, but the issue I'm having is when you include navigational properties, and how to make sure only Active records are queried. The repository method in question starts like this:

/// <summary>
/// Returns a Linq Queryable instance of the entity collection, allowing connected objects to be loaded.
/// </summary>
/// <param name="includeProperties">Connected objects to be included in the result set.</param>
/// <returns>An IQueryable collection of entity.</returns>
public IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties)
{
    IQueryable<T> query = Context.Set<T>().Where(e => e.DeletedDate == null);

    foreach (var includeProperty in includeProperties)
    {
        query = query.Include(includeProperty);
    }

    return query;
}

So if the Repository is being used by an entity called Parent which has a navigational property called Children, the AllIncluding method would properly filter out the soft deleted Parent records, but the soft deleted Children records would still be included.

Looking at the query sent to the database, it seems all that needs to be done is add to the sql join clause " AND Children.DeletedDate IS NULL " and the query would return the correct results.

During my research, I found the this post which appears to be exactly what I need, however my implementation doesn't get the same results the poster had. Stepping through the code, nothing seems to happen to the Children part of the query.

Here is my current relevant code (Note: Using QueryInterceptor from nuget):

BaseClass:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DomainClasses
{
    /// <summary>
    /// Serves as the Base Class for All Data Model Classes
    /// </summary>
    public class BaseClass
    {
        /// <summary>
        /// Default constructor, sets EntityState to Unchanged.
        /// </summary>
        public BaseClass()
        {
            this.StateOfEntity = DomainClasses.StateOfEntity.Unchanged;
        }

        /// <summary>
        /// Indicates the current state of the entity. Not mapped to Database.
        /// </summary>
        [NotMapped]
        public StateOfEntity StateOfEntity { get; set; }

        /// <summary>
        /// The entity primary key.
        /// </summary>
        [Key, Column(Order = 0), ScaffoldColumn(false)]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// The date the entity record was created. Updated in InsightDb.SaveChanges() method
        /// </summary>
        [Column(Order = 1, TypeName = "datetime2"), ScaffoldColumn(false)]
        public DateTime AddDate { get; set; }

        /// <summary>
        /// The UserName of the User who created the entity record. Updated in InsightDb.SaveChanges() method
        /// </summary>
        [StringLength(56), Column(Order = 2), ScaffoldColumn(false)]
        public string AddUser { get; set; }

        /// <summary>
        /// The date the entity record was modified. Updated in InsightDb.SaveChanges() method
        /// </summary>
        [Column(Order = 3, TypeName = "datetime2"), ScaffoldColumn(false)]
        public DateTime ModDate { get; set; }

        /// <summary>
        /// The UserName of the User who modified the entity record.
        /// </summary>
        [StringLength(56), Column(Order = 4), ScaffoldColumn(false)]
        public string ModUser { get; set; }

        /// <summary>
        /// Allows for Soft Delete of records.
        /// </summary>
        [Column(Order = 5, TypeName = "datetime2"), ScaffoldColumn(false)]
        public DateTime? DeletedDate { get; set; }
    }
}

Parent Class:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace DomainClasses
{
    /// <summary>
    /// The Parent Entity.
    /// </summary>
    public class Parent : BaseClass
    {
        /// <summary>
        /// Instantiates a new instance of Parent, initializes the virtual sets.
        /// </summary>
        public Parent()
        {
            this.Children = new HashSet<Child>();
        }

        #region Properties

        /// <summary>
        /// The Parent's Name
        /// </summary>
        [StringLength(50), Required, Display(Name="Parent Name")]
        public string Name { get; set; }

        #endregion

        #region Relationships
        /// <summary>
        /// Relationship to Child, 1 Parent = Many Children.
        /// </summary>
        public virtual ICollection<Child> Children { get; set; }

        #endregion
    }
}

Child Class:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DomainClasses
{
    /// <summary>
    /// The Child entity. One Parent = Many Children
    /// </summary>
    public class Child : BaseClass
    {
        #region Properties

        /// <summary>
        /// Child Name.
        /// </summary>
        [Required, StringLength(50), Display(Name="Child Name")]
        public string Name { get; set; }

        #endregion

        #region Relationships
        /// <summary>
        /// Parent Relationship. 1 Parent = Many Children.
        /// </summary>
        public virtual Parent Parent { get; set; }

        #endregion
    }
}

Context Class:

using DomainClasses;
using System;
using System.Data;
using System.Data.Entity;
using System.Linq;

namespace DataLayer
{
    public class DemoContext : DbContext, IDemoContext
    {
        /// <summary>
        /// ActiveSession object of the user performing the action.
        /// </summary>
        public ActiveSession ActiveSession { get; private set; }

        public DemoContext(ActiveSession activeSession)
            : base("name=DemoDb")
        {
            ActiveSession = activeSession;
            this.Configuration.LazyLoadingEnabled = false;
        }

        #region Db Mappings

        public IDbSet<Child> Children { get; set; }
        public IDbSet<Parent> Parents { get; set; }

        #endregion

        public override int SaveChanges()
        {
            var changeSet = ChangeTracker.Entries<BaseClass>();

            if (changeSet != null)
            {
                foreach (var entry in changeSet.Where(c => c.State != EntityState.Unchanged))
                {
                    entry.Entity.ModDate = DateTime.UtcNow;
                    entry.Entity.ModUser = ActiveSession.UserName;

                    if (entry.State == EntityState.Added)
                    {
                        entry.Entity.AddDate = DateTime.UtcNow;
                        entry.Entity.AddUser = ActiveSession.UserName;
                    }
                    else if (entry.State == EntityState.Deleted)
                    {
                        entry.State = EntityState.Modified;
                        entry.Entity.DeletedDate = DateTime.UtcNow;
                    }
                }
            }

            return base.SaveChanges();
        }

        public new IDbSet<T> Set<T>() where T : BaseClass
        {
            return ((DbContext)this).Set<T>();
        }
    }
}

Repository Class:

using DomainClasses;
using QueryInterceptor;
using System;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

namespace DataLayer
{ 
    /// <summary>
    /// Entity Repository to be used in Business Layer.
    /// </summary>
    public class EntityRepository<T> : IEntityRepository<T> where T : BaseClass
    {
        public IDemoContext Context { get; private set; }

        /// <summary>
        /// Main Constructor for Repository. Creates an instance of DemoContext (derives from DbContext).
        /// </summary>
        /// <param name="activeSession">UserName of the User performing the action.</param>
        public EntityRepository(ActiveSession activeSession)
            : this(new DemoContext(activeSession))
        {
        }

        /// <summary>
        /// Constructor for Repository. Allows a context (i.e. FakeDemoContext) to be passed in for testing.
        /// </summary>
        /// <param name="context">IDemoContext to be used in the repository. I.e. FakeDemoContext.</param>
        public EntityRepository(IDemoContext context)
        {
            Context = context;
        }

        /// <summary>
        /// Returns a Linq Queryable instance of the entity collection.
        /// </summary>
        public IQueryable<T> All
        {
            get { return Context.Set<T>().Where(e => e.DeletedDate == null); }
        }

        /// <summary>
        /// Returns a Linq Queryable instance of the entity collection, allowing connected objects to be loaded.
        /// </summary>
        /// <param name="includeProperties">Connected objects to be included in the result set.</param>
        /// <returns>An IQueryable collection of entity.</returns>
        public IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties)
        {
            IQueryable<T> query = Context.Set<T>().Where(e => e.DeletedDate == null);

            InjectConditionVisitor icv = new InjectConditionVisitor();

            foreach (var includeProperty in includeProperties)
            {
                query = query.Include(includeProperty);
            }

            return query.InterceptWith(icv);
        }

        /// <summary>
        /// Finds a single instance of the entity by the Id.
        /// </summary>
        /// <param name="id">The primary key for the entity.</param>
        /// <returns>An instance of the entity.</returns>
        public T Find(int id)
        {
            return Context.Set<T>().Where(e => e.DeletedDate == null).SingleOrDefault(e => e.Id == id);
        }

        /// <summary>
        /// Takes a single entity or entity graph and reads the explicit state, then applies the necessary State changes to Update or Add the entities.
        /// </summary>
        /// <param name="entity">The entity object.</param>
        public void InsertOrUpdate(T entity)
        {
            if (entity.StateOfEntity == StateOfEntity.Added)
            {
                Context.Set<T>().Add(entity);
            }
            else
            {
                Context.Set<T>().Add(entity);
                Context.ApplyStateChanges();
            }
        }

        /// <summary>
        /// Deletes the instance of the entity.
        /// </summary>
        /// <param name="id">The primary key of the entity.</param>
        public void Delete(int id)
        {
            var entity = Context.Set<T>().Where(e => e.DeletedDate == null).SingleOrDefault(e => e.Id == id);
            entity.StateOfEntity = StateOfEntity.Deleted;
            Context.Set<T>().Remove(entity);
        }

        /// <summary>
        /// Saves the transaction.
        /// </summary>
        public void Save()
        {
            Context.SaveChanges();
        }

        /// <summary>
        /// Disposes the Repository.
        /// </summary>
        public void Dispose() 
        {
            Context.Dispose();
        }
    }
}

InjectConditionVisitor Class:

using System;
using System.Linq;
using System.Linq.Expressions;

namespace DataLayer
{
    public class InjectConditionVisitor : ExpressionVisitor
    {
        private QueryConditional queryCondition;

        public InjectConditionVisitor(QueryConditional condition)
        {
            queryCondition = condition;
        }

        public InjectConditionVisitor()
        {
            queryCondition = new QueryConditional(x => x.DeletedDate == null);
        }

        protected override Expression VisitMember(MemberExpression ex)
        {
            // Only change generic types = Navigation Properties
            // else just execute the normal code.
            return !ex.Type.IsGenericType ? base.VisitMember(ex) : CreateWhereExpression(queryCondition, ex) ?? base.VisitMember(ex);
        }

        /// <summary>
        /// Create the where expression with the adapted QueryConditional
        /// </summary>
        /// <param name="condition">The condition to use</param>
        /// <param name="ex">The MemberExpression we're visiting</param>
        /// <returns></returns>
        private Expression CreateWhereExpression(QueryConditional condition, Expression ex)
        {
            var type = ex.Type;//.GetGenericArguments().First();
            var test = CreateExpression(condition, type);
            if (test == null)
                return null;
            var listType = typeof(IQueryable<>).MakeGenericType(type);
            return Expression.Convert(Expression.Call(typeof(Enumerable), "Where", new Type[] { type }, (Expression)ex, test), listType);
        }

        /// <summary>
        /// Adapt a QueryConditional to the member we're currently visiting.
        /// </summary>
        /// <param name="condition">The condition to adapt</param>
        /// <param name="type">The type of the current member (=Navigation property)</param>
        /// <returns>The adapted QueryConditional</returns>
        private LambdaExpression CreateExpression(QueryConditional condition, Type type)
        {
            var lambda = (LambdaExpression)condition.Conditional;
            var conditionType = condition.Conditional.GetType().GetGenericArguments().FirstOrDefault();
            // Only continue when the condition is applicable to the Type of the member
            if (conditionType == null)
                return null;
            if (!conditionType.IsAssignableFrom(type))
                return null;

            var newParams = new[] { Expression.Parameter(type, "bo") };
            var paramMap = lambda.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
            var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, lambda.Body);
            lambda = Expression.Lambda(fixedBody, newParams);

            return lambda;
        }
    }
}

QueryConditional Class:

using DomainClasses;
using System;
using System.Linq.Expressions;

namespace DataLayer
{
    public class QueryConditional
    {
        public QueryConditional(Expression<Func<BaseClass, bool>> ex)
        {
            Conditional = ex;
        }

        public Expression<Func<BaseClass, bool>> Conditional { get; set; }
    }
}

ParameterRebinder Class:

using System.Collections.Generic;
using System.Linq.Expressions;

namespace DataLayer
{
    public class ParameterRebinder : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            ParameterExpression replacement;

            if (map.TryGetValue(node, out replacement))
                node = replacement;

            return base.VisitParameter(node);
        }
    }
}

IEntityRepository Interface:

using System;
using System.Linq;
using System.Linq.Expressions;

namespace DataLayer
{
    public interface IEntityRepository<T> : IDisposable
    {
        IQueryable<T> All { get; }
        IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties);
        T Find(int id);
        void InsertOrUpdate(T entity);
        void Delete(int id);
        void Save();
    }
}

IDemoContext Interface:

using DomainClasses;
using System;
using System.Data.Entity;

namespace DataLayer
{
    public interface IDemoContext : IDisposable
    {
        ActiveSession ActiveSession { get; }

        IDbSet<Child> Children { get; }
        IDbSet<Parent> Parents { get; }

        int SaveChanges();

        IDbSet<T> Set<T>() where T : BaseClass;
    }
}

Origan

I need to do the same in my project. Did you get a solution to this? – Doorbell

Not yet, I started to look into building the expression dynamically but got pulled off on another project. I feel like there has to be a way to do this, I'm just not that familiar with expressions and the ExpressionVisitor class yet. – Origan

Depending on your version of SQL server you may find it is easier to have EF interact with Views, which have the not deleted clause. Then you can just add some instead of triggers, and everything should work. – Killoran

That's an interesting thought, I'll have to do some research and think about it. – Origan

There is now a solution to soft delete here: #12699293 – Doorbell

Answer 1

I never was able to figure out the expression visitor, and had already spent enough time on it. So I ended up just handling this in a Table Trigger by deleting the record if the DeletedDate was not null.

The original purpose of the soft delete was to track who deleted the record in the application. I was setting the Mod User in the save changes context, but on a deletion this doesn't get updated, so there isn't an audit of who did the deletion.

I already had an "After Update" and "After Delete" trigger for each table I was auditing and an associated audit table for each table. The triggers basically insert the old record into the audit table anytime there is an update or delete. The Audit tables and triggers are created through a stored procedure:

CREATE PROCEDURE [dbo].[CreateAuditTable](
    @TableName NVARCHAR(100),
    @SchemaName NVARCHAR(50)
)
as
/*
-----------------------------------------------------------------------------------------------------
 * Procedure Name   : dbo.CreateAuditTable
 * Author           : Josh Jay
 * Date             : 03/15/2013
 * Description      : Creates an Audit table from an existing table.
-----------------------------------------------------------------------------------------------------
 Sl No      Date Modified       Modified By         Changes 
-------     -------------       -----------------   -------------------------------------------------
  1         07/01/2013          Josh Jay            Removed the table alias parameter and replaced usage with table name.
  2         08/28/2013          Josh Jay            Modified the Update Statement to Delete the Row if it is a Soft Delete.
-----------------------------------------------------------------------------------------------------

Ex:
EXEC dbo.CreateAuditTable
    @TableName = 'Product',
    @SchemaName = 'dbo'

*/
BEGIN
DECLARE @IssueCount INT = 0,
        @IssueList NVARCHAR(MAX) = NULL,
        @LineBreak NVARCHAR(50) = REPLICATE('-',50),
        @CreateTableScript NVARCHAR(MAX) = NULL,
        @CreateDeleteScript NVARCHAR(MAX) = NULL,
        @CreateUpdateScript NVARCHAR(MAX) = NULL,
        @ColumnNamesSection NVARCHAR(MAX) = NULL,
        @TableObjectId INT,
        @msg varchar(1024);

--1) Check if table exists
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName)
        BEGIN
            SET @IssueCount = @IssueCount + 1;
            SET @IssueList = ISNULL(@IssueList + CHAR(10),'') + CONVERT(VARCHAR,@IssueCount) + ') The table ' + @SchemaName + '.' + @Tablename + ' does not exist.';
        END;

--2) Check if audit table exists
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName + '_Audit')
        BEGIN
            SET @IssueCount = @IssueCount + 1;
            SET @IssueList = ISNULL(@IssueList + CHAR(10),'') + CONVERT(VARCHAR,@IssueCount) + ') The audit table ' + @SchemaName + '.' + @Tablename + '_Audit already exists. To recreate the audit table, please drop the existing audit table and try again.';
        END;

--3) Check for existing triggers
    IF EXISTS (SELECT 1 FROM sys.triggers tr INNER JOIN sys.tables t on tr.parent_id = t.object_id
                WHERE t.schema_id = SCHEMA_ID(@SchemaName) AND t.name = @TableName AND tr.name LIKE 'tg_%Audit_%')
        BEGIN
            SET @IssueCount = @IssueCount + 1;
            SET @IssueList = ISNULL(@IssueList + CHAR(10),'') + CONVERT(VARCHAR,@IssueCount) + ') At least one audit trigger exists on the ' + @SchemaName + '.' + @Tablename + ' table. To recreate the audit table, please drop the audit triggers.';
        END;

--4) Print errors if there are any
    IF @IssueCount > 0
        BEGIN
            PRINT('There were ' + CONVERT(VARCHAR,@IssueCount) + ' issues found when attempting to create the audit table. Please correct the issues below before trying again.');
            PRINT(@LineBreak);
            PRINT(@IssueList);
            RETURN;
        END;

--5) Build Scripts
    select
        @CreateTableScript = 
            'CREATE TABLE [' + SS.name + '].[' + ST.name + '_Audit]' + CHAR(10) +
            '(' + CHAR(10) +
            CHAR(9) + '[AuditId] INT IDENTITY(1,1) NOT NULL CONSTRAINT [pk_' + @SchemaName + '.' + @Tablename + '_Audit_AuditId] PRIMARY KEY,' + CHAR(10) +
            CHAR(9) + '[AuditDate] DATETIME NOT NULL CONSTRAINT [df_' + @SchemaName + '.' + @Tablename + '_Audit_AuditDate] DEFAULT (getutcdate()),' + CHAR(10) +
            CHAR(9) + '[AuditIsDelete] BIT NOT NULL CONSTRAINT [df_' + @SchemaName + '.' + @Tablename + '_Audit_AuditIsDelete] DEFAULT ((0))',
        @CreateDeleteScript = 
            'CREATE TRIGGER [dbo].[tg_' + @SchemaName + '.' + @Tablename + '_Audit_Delete]' + CHAR(10) +
            'ON [' + SS.name + '].[' + ST.name + ']' + CHAR(10) +
            'After Delete' + CHAR(10) +
            'As Begin' + CHAR(10) +
            CHAR(9) + 'IF TRIGGER_NESTLEVEL() > 1' + CHAR(10) +
            CHAR(9) + CHAR(9) + 'Return' + CHAR(10) +
            CHAR(10) +
            CHAR(9) + 'INSERT INTO' + CHAR(10) +
            CHAR(9) + CHAR(9) + '[' + SS.name + '].[' + ST.name + '_Audit] (' + CHAR(10) +
            CHAR(9) + CHAR(9) + CHAR(9) + '[AuditIsDelete]',
        @CreateUpdateScript = 
            'CREATE TRIGGER [dbo].[tg_' + @SchemaName + '.' + @Tablename + '_Audit_Update]' + CHAR(10) +
            'ON [' + SS.name + '].[' + ST.name + ']' + CHAR(10) +
            'After Update' + CHAR(10) +
            'As Begin' + CHAR(10) +
            CHAR(9) + 'IF TRIGGER_NESTLEVEL() > 1' + CHAR(10) +
            CHAR(9) + CHAR(9) + 'Return' + CHAR(10) +
            CHAR(10) +
            CHAR(9) + 'INSERT INTO' + CHAR(10) +
            CHAR(9) + CHAR(9) + '[' + SS.name + '].[' + ST.name + '_Audit] (' + CHAR(10) +
            CHAR(9) + CHAR(9) + CHAR(9) + '[AuditIsDelete]'
    from
        sys.tables ST
        INNER JOIN
        sys.schemas SS ON ST.schema_id = SS.schema_id
    WHERE
        ST.name = @TableName AND
        ST.type = 'U' AND
        SS.name = @SchemaName

    SELECT
        @CreateTableScript = @CreateTableScript + ',' + CHAR(10) + CHAR(9) + '[' + ISC.COLUMN_NAME + '] ' + ISC.DATA_TYPE + CASE WHEN ISC.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND ISC.DATA_TYPE <> 'xml' THEN '(' + CASE WHEN ISC.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,ISC.CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END + ' NULL',
        @ColumnNamesSection = ISNULL(@ColumnNamesSection,'') + ',' + CHAR(10) + CHAR(9) + CHAR(9) + CHAR(9) + '[' + ISC.COLUMN_NAME + ']'
    FROM
        INFORMATION_SCHEMA.COLUMNS ISC
    WHERE
        ISC.TABLE_NAME = @TableName AND
        ISC.TABLE_SCHEMA = @SchemaName
    ORDER BY
        ISC.ORDINAL_POSITION ASC

    SET @CreateTableScript = @CreateTableScript + CHAR(10) + ');'

    SET @CreateDeleteScript = @CreateDeleteScript + @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + ')' + CHAR(10) +
        CHAR(9) + CHAR(9) + 'SELECT' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + '1 as [AuditIsDelete]' +
        @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + 'FROM' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + 'deleted' + CHAR(10) +
        'End;'

    SET @CreateUpdateScript = @CreateUpdateScript + @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + ')' + CHAR(10) +
        CHAR(9) + CHAR(9) + 'SELECT' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + '0 as [AuditIsDelete]' +
        @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + 'FROM' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + 'deleted' + CHAR(10) +
        'declare @SoftDelete bit,
            @Id int

    select
        @SoftDelete = case when i.DeletedDate is not null then 1 else 0 end,
        @Id = i.Id
    from
        inserted i;

    if @SoftDelete = 1
        begin
            INSERT INTO
                [' + @SchemaName + '].[' + @TableName + '_Audit] (
                    [AuditIsDelete]
                    ' + @ColumnNamesSection + '
                )
                SELECT
                    1 as [AuditIsDelete]
                    ' + @ColumnNamesSection + '
                FROM
                    inserted

            delete from ' + @SchemaName + '.' + @TableName + ' where Id = @Id
        end;' + CHAR(10) +

        'End;'

--6) Print and Run Scripts
    BEGIN TRY
        BEGIN TRANSACTION;

        EXEC(@CreateTableScript);

        EXEC(@CreateDeleteScript);

        EXEC(@CreateUpdateScript);

        --Test Try Catch:
        --SELECT 1/0

        COMMIT TRANSACTION;

        PRINT('The audit table was successfully created.')
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        set @msg = 
            'db_name()=' + isnull( db_name(), 'NULL' ) + '; ERROR_MESSAGE()=' +
            isnull( ERROR_MESSAGE(), 'NULL' ) +
            '; ERROR_PROCEDURE()=' + isnull( ERROR_PROCEDURE(), 'NULL' ) +
            '; ERROR_LINE()=' + isnull( CONVERT( varchar(10), ERROR_LINE() ), 'NULL' ) +    
            '; ERROR_NUMBER()=' + isnull( CONVERT( varchar(10), ERROR_NUMBER() ), 'NULL' ) +
            '; ERROR_SEVERITY()=' + isnull( CONVERT( varchar(10), ERROR_SEVERITY() ), 'NULL' ) +
            '; ERROR_STATE()=' + isnull( CONVERT( varchar(10), ERROR_STATE() ), 'NULL' );

        PRINT(CHAR(10) + 'Create Audit Table Script:');
        PRINT(@LineBreak);
        PRINT(@CreateTableScript);
        PRINT(@LineBreak);

        PRINT(CHAR(10) + 'Create Audit Delete Trigger Script:');
        PRINT(@LineBreak);
        PRINT(@CreateDeleteScript);
        PRINT(@LineBreak);

        PRINT(CHAR(10) + 'Create Audit Update Trigger Script:');
        PRINT(@LineBreak);
        PRINT(@CreateUpdateScript);
        PRINT(@LineBreak);

        raiserror ( @msg, 18, 1 );
    END CATCH
END;

While the Triggers are not ideal, they accomplish the goals of auditing the user who deleted and I no longer need to worry about the soft deleted records.

Origan

Answer 2

The problem is that you want to add the conditition using the Include() statement in your AllIncluding method. The queryinterceptor package doesn't support Include() methodes. Only solution to get this working is not using the Include statement.

Everything works when you do something like the following :

Articles.Select(x => new {
Vat = x.VatTypes
})
.InterceptWith(Visitor);

So when the above is translated to sql you will see that a Where VatTypes.IsDeleted = 0 is added to the query.

Is it really necessary to have an includeAll method, this looks to me like a HUGE overhead from a performance perspective because you're loading everything from the database.

EDIT: After reading some older posts again, it looks like it should actually be possible to use the InterceptWith method with the Include() statement. Perhaps it is the ExpressionVisitor which has issues with the Include(). If I find some time then I'll try this out and get back to you.

Epispastic

Answer 3

Personally I hate the design pattern whereby one adds a "IsDeleted" column to a table. The reasons are numerous.

1. The pattern produces an inner platform, you have a database inside a database.
2. Custom API required to access the inner database (select * from table where IsDeleted = 0) and (delete from table becomes update table set IsDeleted = 1 )
3. Extra data in table reduces performance
4. Extra data is not useful for audit purposes, if you want auditing, do it properly.

The pain-point you have met is 2. Custom API. Entity Framework was created to work against SQL databases, not some weird data-store that exists inside the SQL database.

The solution I have discovered to this problem is to employ SQL Server Views. MS SQL Server supports Views, which you can filter on rows with your soft delete on. I would then add a TRIGGER INSTEAD OF INSERT,UPDATE, DELETE on the view to map your inserts/update/deletes to the correct actions on your database.

However when using any form of abstraction you will find performance to decrease. In this case the major trade off is SELECT. With SQL Server Enterprise Edition it is feasible to add an index on your view (and have SQL Server automagically use the index) to speed up all your selects, at the expense of write access. That takes care of point 3.

As for point 4. I prefer instead of a IsDeleted column to use the following schema...

• ValidFrom DateTime NOT NULL
• ValidTo DateTime NULL
• EditedBy VARCHAR NOT NULL

When you create a new row, you set ValidFrom to UTCNOW() and EditedBy to CURRENTUSER(). When you update a row you set the old row's ValidTo to UTCNOW() and create a new row with the correct values. When you delete you set the old row's ValidTo to UTCNOW().

This schema allows you to have a full historic view of your table at ANY POINT IN TIME. Full Auditing. :)

Killoran

I also dislike the Soft Delete implementation, but for the way auditing is implemented it is necessary to track who deleted the record. Auditing is setup with triggers inserting the old record into an audit table. The editing/deleting user is set in the C# code so it wouldn't work to use the SQL CURRENTUSER() as this would be the user the application impersonates and would be the same for all actions. I could potentially use views, or I could have the delete trigger delete the record once the necessary data is logged in the audit table. Thanks for your suggestions, I'll have to investigate. – Origan