2

I'm currently working on a column-based multitenant app. I added tenantId on each entity with a base class, and used query filters which fits perfectly my needs. I also overrided savechanges method to populate tenantid property on added entities using the changetracker.

But now i'm stuck with update and delete. As far as i know, ef generates a where clause with only primary key, which is very unsafe for me, because i work on an api environment with a lot of attach/detach stuff, and wrote a lot of entity.state = modified instead of querying first, in order to improve performance.

My question is : how to add a predicate to an ef core update/delete statement ?

I know that i can : 1. Query first (and i'm pretty sure you'll recommand me this) 2. Make tenantid as a part of a compositekey, but this would really be painfull to refactor all my code as the add/find/etc. Methods need full primary keys.

I would be really gratefull to anyone who knows how to trigger this !

David LECERF
  • 52
  • 8
  • Have you tried with global query filters in EF Core? – H. Herzl Apr 29 '19 at 19:02
  • For sure ! But the filter only executes on select queries.. – David LECERF Apr 29 '19 at 19:11
  • Try your suggested solution #1, retrieve your entity by tenant and other columns, then update entity – H. Herzl Apr 29 '19 at 19:20
  • Obviously, solution #1 is fully functional, but performance is affected, you sure that is no pre built solution for this ? – David LECERF Apr 29 '19 at 19:22
  • What about raw query? – H. Herzl Apr 29 '19 at 19:29
  • 1
    As a general comment I'd say that you should never mark an entire entity as modified, but only selected properties. This alone may be enough to prevent TenantIds from ever being marked as modified. To make it more secure you can use [this](https://stackoverflow.com/a/49926351/861716). – Gert Arnold Apr 29 '19 at 19:56
  • That's very relevant, and i think it could be usefull. But that's not enough... As the property can't be changed, does not significate that's in the where clause. Because the where clause will be constituted only with primary key, requests will be able to update/delete other tenants entities... – David LECERF Apr 30 '19 at 06:51
  • No, but if you fetch it with a `Where` containing `TenantID` *and* ensure that `TenantID` never changes, then that's enough. You have an entity identified by its key, having an immutable TenantID. – Gert Arnold May 01 '19 at 07:56

1 Answers1

3

Option 3:

Although not designed for that, you can use EF Core Optimistic concurrency control to perform that safety checks for you:

On relational databases EF Core includes a check for the value of the concurrency token in the WHERE clause of any UPDATE or DELETE statements. After executing the statements, EF Core reads the number of rows that were affected. If no rows are affected, a concurrency conflict is detected, and EF Core throws DbUpdateConcurrencyException.

The idea is (1) to mark the TenantId as concurrency token by either [ConcurrencyCheck] attribute or IsConcurrencyToken() fluent API, and (2) inside your SaveChanges override, set the TenantId property value with the current tenant Id not only for added, but also for modified and deleted entities.

By doing that, EF Core will add something like AND TenantId == @currentTenantId to UPDATE and DELETE commands, which will succeed only if the current tenant matches the one used for entity creation.

The drawback is the exception type/message, and mixing with the existing concurrency checks if used.

Update: The code for modified and deleted entities inside SaveChanges should set the OriginalValue of the TenantId property entry and reset the IsModified flag:

var tenantIdEntry = entityEntry.Property("TenantId");
tenantIdEntry.OriginalValue = CurrentTenantId;
tenantIdEntry.IsModified = false;

The last is not needed if the TenantId property is configured with AfterSaveBehavior = PropertySaveBehavior.Ignore as explained in What is the best way to perform partial updates in EF core and never update certain properties?.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • 1
    I knew that there should be something for me, thank you very much for your knowledge, that works perfectly as intended ! Event if i has to update property by setting currentValue (update entry.Entity didn't worked), for those you could see this. – David LECERF Apr 30 '19 at 10:53
  • You are welcome. I've updated the answer with more info about handling inside `SaveChanges` . The value used in `WHERE` is actually the `OriginalValue`, which normally contains the value from database, but in disconnected scenario is the property value at the time the object is attached to the context (`Update` / `Remove` method). – Ivan Stoev Apr 30 '19 at 12:07
  • 1
    *Although not designed for that* -- that's a devil :) – Gert Arnold May 01 '19 at 07:57
  • 1
    @GertArnold Here I am :) In Bulgaria we say something like (not sure about translation) "Poor man - alive devil". In this context - use whatever is given to us by MS :) Good to see you around in a good mood, hope you are doing well. – Ivan Stoev May 01 '19 at 13:53