MultiTenancy with DbContext and TenantId - Interceptors, Filters, EF Code-First

The question is about EF, but I think it is worth mentioning EF Core here. In EF Core you can use Global Query Filters

Such filters are automatically applied to any LINQ queries involving those Entity Types, including Entity Types referenced indirectly, such as through the use of Include or direct navigation property references

An example:

public class Blog
{
    private string _tenantId;

    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public bool IsDeleted { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");

    // Configure entity filters
    modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
    modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}

I think the biggest question there is 4 - modifying DbContext.

Don't modify the Context...

You shouldn't have to mix tenant-filtering code with your business code.

I think all you need is a repository the returns filtered data
This repository will return filtered data based on an Id you'll get from a TenantIdProvider.
Then, your Service doesn't have to know anything about tenants

using System;
using System.Data.Entity;
using System.Linq;

namespace SqlServerDatabaseBackup
{
    public class Table
    {
        public int TenantId { get; set; }
        public int TableId { get; set; }
    }

    public interface ITentantIdProvider
    {
        int TenantId();
    }

    public class TenantRepository : ITenantRepositoty
    {
        private int tenantId;
        private ITentantIdProvider _tentantIdProvider;
        private TenantContext context = new TenantContext(); //You can abstract this if you want
        private DbSet<Table> filteredTables;

        public IQueryable<Table> Tables
        {
            get
            {
                return filteredTables.Where(t => t.TenantId == tenantId);
            }
        }

        public TenantRepository(ITentantIdProvider tentantIdProvider)
        {
            _tentantIdProvider = tentantIdProvider;
            tenantId = _tentantIdProvider.TenantId();
            filteredTables = context.Tables;
        }

        public Table Find(int id)
        {
            return filteredTables.Find(id);
        }
    }

    public interface ITenantRepositoty
    {
        IQueryable<Table> Tables { get; }
        Table Find(int id);
    }

    public class TenantContext : DbContext
    {
        public DbSet<Table> Tables { get; set; }
    }

    public interface IService
    {
        void DoWork();
    }

    public class Service : IService
    {
        private ITenantRepositoty _tenantRepositoty;

        public Service(ITenantRepositoty tenantRepositoty)
        {
            _tenantRepositoty = tenantRepositoty;
        }

        public void DoWork()
        {
            _tenantRepositoty.Tables.ToList();//These are filtered records
        }
    }  
}

I would like to suggest the following approach, 1. Create a column with the name tenant ID for each of the table that contains core business data this is not required for any mapping table.

2. Use the approach B, by creating an extension method that returns an IQueryable. This method can be an extension of the dbset so that anyone writing a filter clause, can just call this extension method followed by the predicate. This would make the task easier for developers to write code without bothering about tenant ID filter. This particular method will have the code to apply the filter condition for the tenant ID column based on the tenant context in which this query is being executed.

Sample ctx.TenantFilter().Where(....)

3. Instead of relying upon the http context you can have tenant ID passed in all of your service methods so that it will be easy for handling the tenant contacts in both the web and the web job applications. This makes a call free from contacts and more easily testable. The multi tenant entity interface approach looks good and we do have a similar limitation in our application which works fine so far.

4. Regarding adding index you would be required to add an index for tenant ID column in the tables that have tenant ID and that should take care of the DB side query indexing part.

5. Regarding the authentication part, I would recommend to use asp.net identity 2.0 with the owin pipeline. The system is very extensible customisable and easy to integrate with any external identity providers if need be in future.

6. Please do take a look at the repository pattern for entity framework which enables you to write lesser code in a generic fashion. This would help us get rid of code duplication and redundancy and very easy to test from unit test cases