Activate SoftDelete data filter when including collections
The EFCore version of ABP does not automatically filter anything but the root entity of a query. If you look at the implementation within the AbpRepositoryBase, ApplyFilters only looks at the entity that the query is based on, not anything Included.
if (typeof(ISoftDelete).GetTypeInfo().IsAssignableFrom(typeof(TEntity)))
{
if (UnitOfWorkManager?.Current == null || UnitOfWorkManager.Current.IsFilterEnabled(AbpDataFilters.SoftDelete))
{
query = query.Where(e => !((ISoftDelete)e).IsDeleted);
}
}
In the regular implementation of EF(using EF v6.x), they are using the DynamicFilters nuget package that handles this for them, but that plugin doesn't exist for EF Core. This is really a limitation of EF Core, more so than it is with ABP. EF Core doesn't have the hooks available to modify the query generated from the Include, at least that's what I am reading.
So, all that means is you will need to do your own query to solve this problem. You can see how to filter includes through the use of projections in the following link:
Filtering include items in LINQ and Entity Framework
Entity Framework 6.1 - how can you query soft deleted records?
In the end, the only way to easily resolve this was to implement EntityFramework.DynamicFilters from https://github.com/zzzprojects/EntityFramework.DynamicFilters. This is a great solution and provides the flexibility to switch off a filter dynamically.
protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
modelBuilder.Filter( "IsDeleted", ( ISoftDelete d ) => d.IsDeleted, false ));
}
You then add an interface:
internal interface ISoftDelete
{
bool IsDeleted { get; set; }
}
Then, switch off (as required) the IsDeleted filter:
ctx.DisableFilter( "IsDeleted" );
Much easier!
Use ExpressionVisitor to Exclude Soft Deleted Records In Joins
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.
Exclude entities based on property IsDeleted value when loading from DbSet TEntity
If I understand you correctly you need to add something like this in DbContext
public DbSet<MyEntity> ExistingMyEntities {
get { return MyEntities.Where(x => !x.IsDeleted); }
}
So then as you want you can use context object to retrieve not deleted entities.
UPDATE
Also you can filter by IsDeleted with model builder (if there is used soft delete How can I automatically filter out soft deleted entities with Entity Framework?)
public class MyContext : DbContext
{
public virtual IDbSet<MyEntity> MyEntities { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<MyEntities>()
.Map(m => m.Requires("IsDeleted")).HasValue(false).Ignore(m => m.IsDeleted);
}
}
I was misinformed with Ignore, but as Jan Muncinsky noticed there can be used HasValue method for filtering.
Filter all queries (trying to achieve soft delete)
Can't test the exact API, but the general approach would be to create a constrained generic method and call it via reflection:
public static class EFFilterExtensions
{
public static void SetSoftDeleteFilter(this ModelBuilder modelBuilder, Type entityType)
{
SetSoftDeleteFilterMethod.MakeGenericMethod(entityType)
.Invoke(null, new object[] { modelBuilder });
}
static readonly MethodInfo SetSoftDeleteFilterMethod = typeof(EFFilterExtensions)
.GetMethods(BindingFlags.Public | BindingFlags.Static)
.Single(t => t.IsGenericMethod && t.Name == "SetSoftDeleteFilter");
public static void SetSoftDeleteFilter<TEntity>(this ModelBuilder modelBuilder)
where TEntity : class, ISoftDeleteModel
{
modelBuilder.Entity<TEntity>().HasQueryFilter(x => !x.IsDeleted);
}
}
Now you can use something like this inside your OnModelCreating:
foreach (var type in modelBuilder.Model.GetEntityTypes())
{
if (typeof(ISoftDeleteModel).IsAssignableFrom(type.ClrType))
modelBuilder.SetSoftDeleteFilter(type.ClrType);
}
Entity Framework: Soft Delete and Table per Type
It turns out the combining TPT and the Soft Delete pattern using the discriminator is quite tricky.
So, in order to make it work, I made Entity abstract and moved IsDeleted column to to the ConcreteEntity table. Then I changed mappings as follows:
modelBuilder.Entity<Entity>().Map(m => m.ToTable("Entity"));
modelBuilder.Entity<ConcreteEntity>().Map(m =>
{
m.ToTable("ConcreteEntity");
m.Requires("IsDeleted").HasValue(false);
});
I'm not convinced that it is the best way it can be done, but at least it works.
Related Topics
Check If Number Is Prime Number
Wait Until File Is Completely Written
Serializing Private Member Data
How to Hide Close Button in Wpf Window
How to Verify If a Windows Service Is Running
Get SQL Code from an Entity Framework Core Iqueryable<T>
C# - How to Get Program Files (X86) on Windows 64 Bit
How to Pass the Smart Screen on Win8 When Install a Signed Application
How to Create Bitmap from Byte Array
I Want Await to Throw Aggregateexception, Not Just the First Exception
Impossible to Use Ref and Out for First ("This") Parameter in Extension Methods
Order of Regular Expression Operator (..|.. ... ..|..)
JSON.Net: Serialize/Deserialize Property as a Value, Not as an Object