A difficult problem, naturally not supported by EF. One of the cases where DTO and projection provide the necessary control. There is still a clean EF solution, but it must be programmed very carefully. I will try to cover as many aspects as possible.
To begin with, what cannot be done.
This should be possible by setting up a one-to-one relationship and having the foreign key "Merchant ID" and "Account ID"
It's impossible. The physical (storage) relationship is one-to-many ( Vendor (one) to FavVendor (many)), although the logical relationship for a particular AccountId is one-to-one . But EF only supports physical relationships, so it is simply impossible to imagine a logical relationship that is additionally dynamic.
In short, the relationship should be one-to-many as in your original 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 get all the selected providers, since I can not write a subquery in EF, and then when I update the record, the deletion occurs.
Both of the above problems can be solved using special code.
Firstly, since full loading and loading do not support filtering, the only remaining option is to explicitly load (described in Applicable filters when explicitly loading related objects in the documentation) or projection and rely on fixing the context navigation property (which is based on explicit loading). To avoid side effects, lazy loading should be disabled for the objects involved (I already did this by removing the virtual from the navigation properties), and the data search should always consist of new short instances of DbContext to eliminate the unintended loading of related data caused by that the same navigation property correction function that we rely on to filter FavVendors .
With that said, here are some of the operations:
Retrieving providers with filtered FavVendors for a specific AccountId:
To obtain a separate provider by identifier:
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 in general, for supplier requests (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 provider and FavVendor for a specific AccountId from a disconnected object:
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 the asynchronous version, just use await for the appropriate Async methods)
To prevent the removal of unrelated FavVendors , you first load the Vendor with the filtered FavVendors from the database, and then depending on the contents of the FavVendors object FavVendors either add a new one, update or delete the existing FavVendor entry.
Recall that this is doable, but difficult to implement and maintain (especially if you need to enable Vendor and filter FavVendors in a query that returns some other object referencing Vendor , because you cannot use the typical Include ). You might want to try some third-party packages, such as Entity Framework Plus , which, with the help of the Query Filter and Include Query Filter, can greatly simplify the part of the query.