EF Core Global Query Filter for SaveChanges (Update, Delete)

user_3110834 0
Viewed 1159

TL;DR;

Is there any way to apply a query filter to WHERE clause of generated UPDATE and DELETE statement using EF Core when you call SaveChanges? (Quite similar to Global query filters which work just for SELECT scenarios.)

Global query filters work well for SELECT scenarios by adding additional criteria to WHERE clause of generated SELECT statement, but they don't do the same for UPDATE and DELETE statements when you call SaveChanges method of the DbContext and I wonder if it's possible to apply a query filter to WHERE clause of generated UPDATE and DELETE statement using EF Core when you call SaveChanges? The same way that it works by HasQueryFilter for SELECT statements.

I'm going to use such feature for a multi-tenancy scenario in a small application and I'm able to handle this requirement in other layers or by overriding SaveChanges and checking if the deleted or modified entity belongs to the right tenant; however, because of performance and security considerations, I prefer query filter approach.

More context

One of the common scenarios of query filters is Multi-tenancy. Data of a tenant should be protected against reading and modification by other tenants:

  • Reading by another tenant: Registering a global query filter using HasQueryFilter works well.
  • Modification by another tenant: How to do it in EF DbContext?

You can take a look at query filters on Microsoft Docs or their sample code on GitHub.

Why am I looking for query filters for UPDATE and DELETE?

The main reasons are:

  • Performance considerations
  • Security considerations

After all, we have query filter for SELECT statement, why shouldn't we have for UPDATE and DELETE?

Performance considerations

I'm able to handle this requirement in other layers, like API, DAL, BLL or by overriding SaveChanges and checking if the deleted or modified entity belongs to the right tenant; The main reason that I prefer to not do it in BLL or SaveChanges, is because of performance. A WHERE clause will be evaluated at database server side, it's secure and also high performance, quite similar to global query filters.

For example, for a Delete scenario, an obvious answer could be something like this:

var entity = db.BlogPosts.Find(id);
if(entity!=null)
    db.Entry(entity).State = EntityState.Deleted;
db.SaveChanges();

Which is basically a SELECT and then a DELETE.

But, I prefer to delete like this, which is just a DELETE statement:

db.Entry(new BlogPost() { Id = id }).State = EntityState.Deleted;
db.SaveChanges();

Security Considerations

The other thing is, first of all, these entities doesn't necessary have a TenantId in DbContext.Entries. On the other hand, even if they have a value for their TenantId, the value is not reliable, because it may have been changed. BlogPost.Id = 2 may belong to tenant 2, while tenant 1 is trying to modify or delete it.

global-query-filter entity-framework-core entity-framework .net c#
2 Answers

Update: There is no built-in support for this at the moment, however I've booked an issue/feature suggestion in EF Core repository. It may be implemented in future as part of batch operation scenarios or as an improvement to SaveChanges. You may want to share your idea about the feature there.

I couldn't find any built-in feature which works like global query filter for update and delete statements.

An option, to apply a global query filter for update and delete statements, you can consider Command Interceptors. They allow you to modify the command before execution and for example add a query filter to the command text.

As an example, the following code shows how you can modify the UPDATE or DETELE statement and add a clause like WHERE TenantId = @__TenantId__ AND . This way, you can be sure the updating or deleting data belongs the the tenant and a tenant cannot modify or delete data of another tenant.

To do so, create a DbCommandInterceptor to intercept update and delete command:

public class BlogPostsCommandInterceptor : DbCommandInterceptor
{
    ITenantProvider tenantProvider;
    public BlogPostsCommandInterceptor(ITenantProvider tenantProvider)
    {
        this.tenantProvider = tenantProvider;
    }
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, 
        InterceptionResult<DbDataReader> result) {
        ModifyCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default) {
        ModifyCommand(command);
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
    private void ModifyCommand(DbCommand command) {
        if (command.CommandText.StartsWith("UPDATE \"BlogPosts\"") ||
            command.CommandText.StartsWith("DELETE FROM \"BlogPosts\""))
        {
            var parameter = command.CreateParameter();
            parameter.ParameterName = "@__TenantId__";
            parameter.Value = tenantProvider.GetTenantId();
            command.Parameters.Add(parameter);
            command.CommandText = command.CommandText.Replace("WHERE",
                $"WHERE (\"TenantId\" = {parameter.ParameterName}) AND ");
        }
    }
}

Register the Interceptor:

protected override void OnConfiguring(
    DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(
        new BlogPostsCommandInterceptor(tenantProvider));
}

This is not exactly what I was looking for; but anyways, it may be inspiring for others; And I'd be happy to receive better options, which answer the original question.

user_31108340

Modification by another tenant: How to do it in EF DbContext?

You can override SaveChanges() and verify that all the entities belong to the correct tenant.

user_72977000
Related