Implementation of the Global Query Filters, Multitenant and Soft Delete, in EF Core.
If Global Query Filters is a new thing for you check this out and then come back to this page : https://docs.microsoft.com/en-us/ef/core/querying/filters
There are various ways in which you can implement filtering in .NET Core 3.1 app and I tell you why we haven’t chosen neither of them.
1. Filtering in the OnModelCreating method
It surely works, but in DB First projects, this approach is very time consuming because you will have to modify it every time you are doing a scaffold, so we said pass.
2. Azure SQL DB RLS ( Row-Level Security )
This one works also, and it is highly recommend in a DB First approach, but we had a scenario in which the cardinality between users and tenants is N:N and this might cause some issues. If you don’t have a N:N cardinality, check it out, it might be what you need ( find more about this here : https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15 )
3. Filtering in every controller
You might consider this if you have a really small app with just a few controllers, but it might come a time in which you will regret this decision ?.
So, we’ve ended up with the Global Query Filters which, from our perspective, does the job app wise and performance wise, filtering the based on the tenant field and also based on a soft delete field.
Following the scenario in which we are ( if you have landed on this page, check out the rest of the story just to have a little bit of a context ), we have extended the DBContext, again, so we can run away from the scaffold, again.
The snippet below checks if there are models which have the fields TenantId and IsDeleted and filters the objects based on that.
This is a dynamically approach so, from the filtering point of view, only this section takes cares of the magic:
public partial class MasterContext : DbContext
{
public Guid TenantId { get; set; }
public List<Guid> _tenantIds;
public bool isAllActive; // this is for the scenario in which the tenant wants all the data , softdeleted rows and active rows
public int hashCode;
partial void OnModelCreatingFilters(ModelBuilder modelBuilder)
{
// this is a global filter filter to check if has IsDeleted and TenantIds columns
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
var isDeletedProperty = entityType.FindProperty("IsDeleted");
var tenantIdProperty = entityType.FindProperty("TenantId");
// if we have both IsDeleted and TenantId columns ( with IsDeleted not being supressed )
if (((isDeletedProperty != null) && (tenantIdProperty != null))
&& isDeletedProperty.ClrType == typeof(bool) && !isAllActive)
{
var parameter = Expression.Parameter(entityType.ClrType, "p");
//is deleted
var filterIsDeleted = Expression.Equal(Expression.Property(parameter, isDeletedProperty.PropertyInfo)
, Expression.Constant(false, typeof(bool)));
// check if there are any tenants
if (_tenantIds != null)
{
// we are parsing the first element from the list just pe sure we have some
var tenantid = _tenantIds.First();
var filterTenantId = Expression.Equal(Expression.Property(parameter, tenantIdProperty.PropertyInfo)
, Expression.Constant(tenantid, typeof(Guid)));
//move to a different List so we dont affect the main list
//beware of the shallow copy
List<Guid> localtenantIds = new List<Guid>(_tenantIds);
localtenantIds.Remove(tenantid);
//loop interation for all the remaining tenantids
foreach (var tenantidloop in localtenantIds)
{
filterTenantId = Expression.Or(filterTenantId, Expression.Equal(Expression.Property(parameter, tenantIdProperty.PropertyInfo)
, Expression.Constant(tenantidloop, typeof(Guid))));
}
//combine both filters
MutableEntityTypeExtensions.SetQueryFilter(entityType, Expression.Lambda(Expression.And(filterIsDeleted, filterTenantId), parameter));
}
//dummy filtering so we exclude all other tenants
else
{
var tenantid = new Guid("00000000-0000-0000-0000-000000000000");
var filterTenantId = Expression.Equal(Expression.Property(parameter, tenantIdProperty.PropertyInfo)
, Expression.Constant(tenantid, typeof(Guid)));
//combine both filters
MutableEntityTypeExtensions.SetQueryFilter(entityType, Expression.Lambda(Expression.And(filterIsDeleted, filterTenantId), parameter));
}
}
else
{
// this is a global filter to check if the object has IsDeleted column
if (((isDeletedProperty != null) && (tenantIdProperty == null)) // delete without tenant
&& isDeletedProperty.ClrType == typeof(bool))
{
var parameter = Expression.Parameter(entityType.ClrType, "p");
//is deleted only
var filterIsDeleted = Expression.Equal(Expression.Property(parameter, isDeletedProperty.PropertyInfo)
, Expression.Constant(false, typeof(bool)));
MutableEntityTypeExtensions.SetQueryFilter(entityType, Expression.Lambda(filterIsDeleted, parameter));
}
else
{
// this is a global filter to check if the object has TenantId column
if ((((isDeletedProperty == null) && (tenantIdProperty != null)) // tenant without delete
&& isDeletedProperty.ClrType == typeof(bool)) ||
(((tenantIdProperty != null)) && isAllActive)) // tenant with supressed delete
{
var parameter = Expression.Parameter(entityType.ClrType, "p");
// check if there are any tenants
if (_tenantIds.Any())
{
// we are parsing the first element from the list just pe sure we have some
var tenantid = _tenantIds.First();
var filterTenantId = Expression.Equal(Expression.Property(parameter, tenantIdProperty.PropertyInfo)
, Expression.Constant(tenantid, typeof(Guid)));
_tenantIds.Remove(tenantid);
//loop interation for all the remaining tenantids
foreach (var tenantidloop in _tenantIds)
{
filterTenantId = Expression.Or(filterTenantId, Expression.Equal(Expression.Property(parameter, tenantIdProperty.PropertyInfo)
, Expression.Constant(tenantidloop, typeof(Guid))));
}
//combine both filters
MutableEntityTypeExtensions.SetQueryFilter(entityType, Expression.Lambda(filterTenantId, parameter));
}
}
}
}
}
}
}
Also, this kind implementation can accommodate different kind of scenarios like:
- Multiple tenants, soft delete
- Multiple tenants, no soft delete (getting all the rows even if they are soft deleted)
- No tenant, soft delete
- No tenant, no soft delete (getting all the rows)
Chapter 6 : Modifications of the controllers to embed EF Core Global Query Filters functionality
