Virtual Navigation Properties and Multi-Tenancy

Question

I have a standard DbContext with code like the following:

 public DbSet<Interest> Interests { get; set; }
 public DbSet<User> Users { get; set; }

I've recently implemented multi-tenancy by creating a TenantContext that contains the following:

  private readonly DbContext _dbContext;
  private readonly Tenant _tenant;

  public TenantContext(Tenant tenant)
        : base("name=DefaultConnection") {
        this._tenant = tenant;
        this._dbContext = new DbContext();
    }

   public IQueryable<User> Users { get { return FilterTenant(_dbContext.Users); } }
   public IQueryable<Interest> Interests { get { return FilterTenant(_dbContext.Interests); } }


   private IQueryable<T> FilterTenant<T>(IQueryable<T> values) where T : class, ITenantData
    {
        return values.Where(x => x.TenantId == _tenant.TenantId);
    }

So far, this has been working great. Whenever any of my services creates a new TenantContext, all queries directly off of that context are filtered through this FilterTenant method that guarantees I'm only returning tenant-relevant entities.

The problem that I'm encountering is my usage of navigation properties that do not take this into account:

  using (var db = CreateContext())  // new TenantContext
        {
            return db.Users.
                Include(u => u.Interests).FirstOrDefault(s => s.UserId == userId);
        }

This query pulls up the tenant-specific Users, but then the Include() statement pulls in Interests for that user only - but across all tenants. So if a user has Interests across multiple Tenants, I get all of the user's Interests with the above query.

My User model has the following:

 public int UserId { get; set; }
 public int TenantId { get; set; }
 public virtual ICollection<Interest> Interests { get; set; }

Is there any way that I can somehow modify these navigation properties to perform tenant-specific queries? Or should I go and tear out all navigation properties in favor of handwritten code?

The second option scares me because a lot of queries have nested Includes. Any input here would be fantastic.

Answer 1

As far as I know, there's no other way than to either use reflection or query the properties by hand.

So in your IQueryable<T> FilterTenant<T>(IQueryable<T> values) method, you'll have to inspect your type T for properties that implement your ITenantData interface.

Then you're still not there, as the properties of your root entity (User in this case) may be entities themselves, or lists of entities (think Invoice.InvoiceLines[].Item.Categories[]).

For each of the properties you found by doing this, you'll have to write a Where() clause that filters those properties.

Or you can hand-code it per property.

These checks should at least happen when creating and editing entities. You'll want to check that navigation properties referenced by an ID property (e.g. ContactModel.AddressID) that get posted to your repository (for example from an MVC site) are accessible for the currently logged on tenant. This is your mass assignment protection, which ensures a malicious user can't craft a request that would otherwise link an entity to which he has permissions (a Contact he is creating or editing) to one Address of another tenant, simply by posting a random or known AddressID.

If you trust this system, you only have to check the TenantID of the root entity when reading, because given the checks when creating and updating, all child entities are accessible for the tenant if the root entity is accessible.

Because of your description you do need to filter child entities. An example for hand-coding your example, using the technique explained found here:

public class UserRepository
{
    // ctor injects _dbContext and _tenantId

    public IQueryable<User> GetUsers()
    { 
        var user = _dbContext.Users.Where(u => u.TenantId == _tenantId)
                                   .Select(u => new User
                                   {
                                       Interests = u.Interests.Where(u => 
                                                     u.TenantId == _tenantId),
                                       Other = u.Other,
                                   };                               
        }
    }
}

But as you see, you'll have to map every property of User like that.

Answer 2

Just wanted to offer an alternative approach to implementing multi-tenancy, which is working really well in a current project, using EF5 and SQL 2012. Basic design is (bear with me here...):

1. Every table in the database has a column (ClientSid binary, default constraint = SUSER_SID()) and is never queried directly, only ever via a dedicated view
2. Each view is a direct select over the table with WHERE (ClientSid = SUSER_SID()) but doesn't select the ClientSid (effectively exposing the interface of the table)
3. EF5 model is mapped to the VIEW, not the TABLE
4. The connection string is varied based on the context of the tenant (user / client whatever multi-tenant partition requirement may be)

That's pretty much it - though it might be useful to share. I know it's not a direct answer to your question, but this has resulted in basically zero custom code in the C# area.