How to filter DbContext in a multi-tenant application using Entity Framework with MVC4

asp.net asp.net-mvc-4 c# entity-framework multi-tenant

Question

Using MVC4 and EF5, I'm creating a web application with several tenants. Regarding filtering my DbContext, I previously posed the following query: Is using the repository pattern to filter by ID a bad idea?.

It seems that my strategy was sound, although it was suggested that I supply a already tenant level filtered context utilizing a 'a repository or class that would feed your [my] usual repository' rather than handling all filters in a single repository.

I began implementing this as best I could despite the fact that I am not an expert in MVC. However, after exploring filtering in EF for other multi-tenant applications, I came across a question for a comparable caseWeb application with several tenants and restricted dbContext but absolutely failed to understand the solution.

The CompanyID should be obtained straight from the authorized user because it is a property of the User class in my program. Eg:

int CompanyID = db.Users.Single(u => u.Email == User.Identity.Name).CompanyID;

My present strategy does seem to be working, but based on what I've read in other Questions about doing the same kind of thing, I'm quite confident I've approached it incorrectly and/or ineffectively. Zzz-55-Zzz reflection is mentioned in another query as a way to accomplish this, but I'm unable to determine whether it would apply in my situation or even understand how to utilize it.

If someone could describe the ideal approach to taking on this, as well as the benefits and drawbacks of other approaches, I would be incredibly grateful. Thanks:)

I currently use the following strategy:

DB

  • Many renters, one database.
  • Although not every table has a CompanyID column, every table in some manner links back to the Company table.

TestController.cs

public class TestController : Controller
{
    private BookingSystemEntities db = new BookingSystemEntities();
    public ActionResult Index()
    {
        var user = db.Users.Single(u => u.Email == User.Identity.Name);
        IBookingSystemRepository rep = new BookingSystemRepository(db, user);            
        return View(rep.GetAppointments(false));
    }

}

BookingSystemRepository.cs

public class BookingSystemRepository : IBookingSystemRepository
{
    private CompanyBookingSystemRepository db;

    public BookingSystemRepository(BookingSystemEntities context, User user)
    {
        this.db = new CompanyBookingSystemRepository(context, user);
    }

    public IEnumerable<Appointment> GetAppointments()
    { return GetAppointments(false); }

    public IEnumerable<Appointment> GetAppointments(bool includeDeleted)
    {
        return includeDeleted
            ? db.Appointments
            : db.Appointments.Where(a => a.Deleted.HasValue);
    }

    public IEnumerable<Client> GetClients()
    { return GetClients(false); }

    public IEnumerable<Client> GetClients(bool includeDeleted)
    {
        return includeDeleted
            ? db.Clients
            : db.Clients.Where(c => c.Deleted.HasValue);
    }

    public void Save()
    {
        db.SaveChanges();
    }

    public void Dispose()
    {
        if (db != null)
            db.Dispose();
    }
}

CompanyBookingSystemRepository.cs

public class CompanyBookingSystemRepository
{
    private BookingSystemEntities db;
    private User User;
    public IEnumerable<Appointment> Appointments { get { return db.Appointments.Where(a => a.User.CompanyID == User.CompanyID).AsEnumerable<Appointment>(); } }
    public IEnumerable<Client> Clients { get { return db.Clients.Where(a => a.CompanyID == User.CompanyID).AsEnumerable<Client>(); } }

    public CompanyBookingSystemRepository(BookingSystemEntities context, User user)
    {
        db = context;
        this.User = user;
    }

    public void SaveChanges()
    {
        db.SaveChanges();
    }

    public void Dispose()
    {
        if (db != null)
            db.Dispose();
    }
}
1
9
5/23/2017 11:59:30 AM

Popular Answer

Compared to some of the other instances you gave, I like your strategy. The most efficient way to ensure that you are filtering your data effectively, assuming each tenant has the same codebase and domain, should be to filter depending on the user who is currently logged in. (If not, you could filter using those too.)

You can deliberately denormalize your database to include that information in such tables if you're worried about database performance when filtering out tables without a CompanyID.

Although ingenious, the reflection method you cited looks unnecessarily complex and like a lot more overhead than simply specifying the CompanyID in your db query (especially since the db call is happening in both instances).

EDIT (after comment):

You appear to have created a lot of extra, unnecessary code for the remainder of it (at least not within the example cited above). Since it appears from your code that the former only exists to pass calls through to the latter, which simply filters results using the UserID (is there ever a situation where you wouldn't filter those results? ), I'm not sure why you make the distinction between a BookingSystemRepository and a CompanyBookingSystemRepository.

By just switching your approach to: you may completely eliminate both of those classes (as well as the problem you mention in your remark).

public class TestController : Controller
{
    private BookingSystemEntities db = new BookingSystemEntities();
    public ActionResult Index()
    {
        var user = db.Users.Single(u => u.Email == User.Identity.Name);
        var appointments = db.Appointments.Where(a => a.User.CompanyID == user.CompanyID).AsEnumerable();
        return View(appointments);
    }

    public override void Dispose(bool disposing)
    {
        db.Dispose();
        base.Dispose(disposing);
    }
}

Then, if performance is a concern, you should complete all of your filtering within the database and just call those operations to return your data.

1
6/26/2013 3:35:46 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow