Entity Framework Multi Tenant Customize Shared Table

Question

I am writing a multi tenant application. Almost all tables have "AccountId" to specify which tenant owns the record. I have one table that holds a list of "Vendors" that all tenants have access to, it does not have AccountId.

Some tenants want to add custom fields onto a Vendor record.

How do I set this up in Code First Entity Framework? This is my solution so far but I have to fetch all favorite vendors since I can't write a sub-query in EF and then when I update the record, deletes are happening.

public class Vendor
{
    public int Id { get;set;} 
    public string Name { get; set; }
}

public class TenantVendor
{
    public int AccountId { get;set;} 
    public int VendorId{ get;set;} 
    public string NickName { get; set; }
}


// query
// how do I only get single vendor for tenant?
var vendor = await DbContext.Vendors
                            .Include(x => x.TenantVendors) 
                            .SingleAsync(x => x.Id == vendorId);

// now filter tenant's favorite vendor
// problem: if I update this record later, it deletes all records != account.Id
vendor.TenantVendors= vendor.FavoriteVendors
                            .Where(x => x.AccountId == _account.Id)
                            .ToList();

I know I need to use a multi-column foreign key, but I'm having trouble setting this up.

Schema should look like the following..

Vendor
 Id

FavVendor
 VendorId
 AccountId
 CustomField1

Then I can query the vendor, get the FavVendor for the logged in account and go on my merry way.

My current solution, which gives me an extra "Vendor_Id" foreign key, but doesn't set it properly

This should be possible by setting up a "one to one" relationship and having the foreign key be "Vendor Id" and "Account Id"

Trying to get this setup in entity framework now...

public class Vendor
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual FavVendor FavVendor { get; set; }
}

public class FavVendor
{
    public string NickName { get; set; }

    [Key, Column(Order = 0)]
    public int VendorId { get; set; }
    public Vendor Vendor { get; set; }

    [Key, Column(Order = 1)]
    public int AccountId { get; set; }
    public Account Account { get; set; }
}


 // query to get data
  var dbVendorQuery = dbContext.Vendors
         .Include(x => x.FavVendor)
         .Where(x => x.FavVendor == null || x.FavVendor.AccountId == _account.Id) ;

 // insert record
 if (dbVendor.FavVendor == null)
 {
     dbVendor.FavVendor = new FavVendor()
     { 
        Account = _account,
     };
  } 
  dbVendor.FavVendor.NickName = nickName;

  dbContext.SaveChanges();

Also receiving the following error when I try and set foreign key on FavVendor.Vendor

FavVendor_Vendor_Source: : Multiplicity is not valid in Role 'FavVendor_Vendor_Source' in relationship 'FavVendor_Vendor'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.

Answer 1

Tricky issue not naturally supported by EF. One of the cases where DTOs and projection provides you the required control. Still pure EF solution exists, but must be programmed very carefully. I'll try to cover as much aspects as I can.

Let start with what can't be done.

This should be possible by setting up a "one to one" relationship and having the foreign key be "Vendor Id" and "Account Id"

This is not possible. The physical (store) relationship is one-to-many (Vendor (one) to FavVendor (many)), although the logical relationship for a specific AccountId is one-to-one. But EF supports only physical relationships, so there is simply no way to represent the logical relationship, which additionally is dynamic.

Shortly, the relationship has to be one-to-many as in your initial design. Here is the final model:

public class Vendor
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<FavVendor> FavVendors { get; set; }
}

public class FavVendor
{
    public string NickName { get; set; }

    [Key, Column(Order = 0)]
    public int VendorId { get; set; }
    public Vendor Vendor { get; set; }

    [Key, Column(Order = 1)]
    public int AccountId { get; set; }
}

This is my solution so far but I have to fetch all favorite vendors since I can't write a sub-query in EF and then when I update the record, deletes are happening.

Both aforementioned issues can be solved by wring a code in a special way.

First, since nether lazy nor eager loading supports filtering, the only remaining option is explicit loading (described in the Applying filters when explicitly loading related entities section of the documentation) or projection and rely on context navigation property fixup (which in fact explicit loading is based on). To avoid side effects, the lazy loading must be turned off for the involved entities (I already did that by removing virtual keyword from navigation properties) and also the data retrieval should always be through new short lived DbContext instances in order to eliminate the unintentional loading of related data caused by the same navigation property fixup feature which we rely on to do the filtering of FavVendors.

With that being said, here are some of the operations:

Retrieving Vendors with filtered FavVendors for specific AccountId:

For retrieving single vendor by Id:

public static partial class VendorUtils
{
    public static Vendor GetVendor(this DbContext db, int vendorId, int accountId)
    {
        var vendor = db.Set<Vendor>().Single(x => x.Id == vendorId);
        db.Entry(vendor).Collection(e => e.FavVendors).Query()
            .Where(e => e.AccountId == accountId)
            .Load();
        return vendor;
    }

    public static async Task<Vendor> GetVendorAsync(this DbContext db, int vendorId, int accountId)
    {
        var vendor = await db.Set<Vendor>().SingleAsync(x => x.Id == vendorId);
        await db.Entry(vendor).Collection(e => e.FavVendors).Query()
            .Where(e => e.AccountId == accountId)
            .LoadAsync();
        return vendor;
    }
}

or more generically, for vendors query (with filtering, ordering, paging etc. already applied):

public static partial class VendorUtils
{
    public static IEnumerable<Vendor> WithFavVendor(this IQueryable<Vendor> vendorQuery, int accountId)
    {
        var vendors = vendorQuery.ToList();
        vendorQuery.SelectMany(v => v.FavVendors)
            .Where(fv => fv.AccountId == accountId)
            .Load();
        return vendors;
    }

    public static async Task<IEnumerable<Vendor>> WithFavVendorAsync(this IQueryable<Vendor> vendorQuery, int accountId)
    {
        var vendors = await vendorQuery.ToListAsync();
        await vendorQuery.SelectMany(v => v.FavVendors)
            .Where(fv => fv.AccountId == accountId)
            .LoadAsync();
        return vendors;
    }
}

Updating a Vendor and FavVendor for a specific AccountId from disconnected entity:

public static partial class VendorUtils
{
    public static void UpdateVendor(this DbContext db, Vendor vendor, int accountId)
    {
        var dbVendor = db.GetVendor(vendor.Id, accountId);
        db.Entry(dbVendor).CurrentValues.SetValues(vendor);

        var favVendor = vendor.FavVendors.FirstOrDefault(e => e.AccountId == accountId);
        var dbFavVendor = dbVendor.FavVendors.FirstOrDefault(e => e.AccountId == accountId);
        if (favVendor != null)
        {
            if (dbFavVendor != null)
                db.Entry(dbFavVendor).CurrentValues.SetValues(favVendor);
            else
                dbVendor.FavVendors.Add(favVendor);
        }
        else if (dbFavVendor != null)
            dbVendor.FavVendors.Remove(dbFavVendor);

        db.SaveChanges();
    }
}

(For async version just use await on corresponding Async methods)

In order to prevent deleting unrelated FavVendors, you first load the Vendor with filtered FavVendors from database, then depending of the passed object FavVendors content either add new, update or delete the existing FavVendor record.

To recap, it's doable, but hard to implement and maintain (especially if you need to include Vendor and filtered FavVendors in a query returning some other entity referencing Vendor, because you cannot use the typical Include methods). You might consider trying some 3rd party packages like Entity Framework Plus which with its Query Filter and Include Query Filter features could significantly simplify the querying part.