DbSet, ModelBuilder, and EF Navigation Properties

I’m attempting to implement a multi-tenant application where I query the db via the tenant object, instead of directly off the context. Before I had this:

public User GetUserByEmail(string email)
    {
        using (var db = CreateContext())
        {
            return db.Users.FirstOrDefault(u => u.Email.Equals(email, StringComparison.OrdinalIgnoreCase));
        }
    }

Now I have this:

public User GetUserByEmail(string email)
    {
        using (var db = CreateContext())
        {
            return _tenant.Users.FirstOrDefault(u => u.Email.Equals(email, StringComparison.OrdinalIgnoreCase));
        }
    }

Where Tenant is the following:

public class Tenant
{
    public Tenant()
    {
    }

    [Key]
    [Required]
    public int TenantId { get; set; }

    public virtual DbSet<User> Users { get; set; }
    // etc
}

Where my User model has the following:

public virtual List<Tenant> Tenants { get; set; }

And in my Context configuration, I have the following:

modelBuilder.Entity<Tenant>()
        .HasMany(e => e.Users)
        .WithMany()
        .Map(m =>
        {
            m.ToTable("UserTenantJoin");
            m.MapLeftKey("TenantId");
            m.MapRightKey("UserId");
        });

But I’m running into a problem with the fact that DbSet is incompatible with the ModelBuilder above – it chokes on HasMany saying that the use of DbSet cannot be inferred from usage.

I played with using ICollection instead, but then in my service layer all calls to _tenant.Users.Include(stuff), or Find(), and other db queries break.

Example of a service method that breaks if I use ICollection:

   public User GetUserWithInterestsAndAptitudes(string username)
    {
        using (var db = CreateContext())
        {
            return _tenant.Users.  // can't use .Include on ICollection
                Include(u => u.Relationships).
                Include(u => u.Interests).
                Include(u => u.Interests.Select(s => s.Subject)).
                Include(u => u.Interests.Select(s => s.Aptitude)).
                FirstOrDefault(s => s.Username.Equals(username, StringComparison.OrdinalIgnoreCase));
        }
    }

I’m hoping there’s a solution that will allow me to keep the navigation properties queryable without re-architecting my service layer.

One option is that I revert everything back to using the context via db.Users, and then add another condition to every single query .Where(u => u.TenantId == _tenant.TenantId) – but I’m trying to avoid this.

Any help here would be much appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I have a solution similar to what you are trying to avoid.

I have a real DbContext that is only accessed via a TenantContext.

public class RealContext
{
     public DbSet<User> Users { get; set; }
     [...]
}

public class TenantContext 
{
    private RealContext realContext;
    private int tenantId;
    public TenantContext(int tenantId)
    {
        realContext = new RealContext();
        this.tenantId= tenantId;
    }
    public IQueryable<User> Users { get { FilterTenant(realContext.Users); }     }

    private IQueryable<T> FilterTenant<T>(IQueryable<T> values) where T : class, ITenantData
    {
         return values.Where(x => x.TenantId == tenantId);
    }
    public int SaveChanges()
    {
        ApplyTenantIds();
        return realContext.SaveChanges();
    }
}

Using this method I’m sure that there is no was a query can be sent without getting the correct tenants. For adding and removing items from the context I’ using those two generic methods.

public void Remove<T>(params T[] items) where T : class, ITenantData
{
    var set = realContext.Set<T>();
    foreach(var item in items)
        set.Remove(item);
}

public void Add<T>(params T[] items) where T : class, ITenantData
{
    var set = realContext.Set<T>();
    foreach (var item in items)
        set.Add(item);
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0