I have looked around at (what I believe to be) every question involved with many-to-many relationships with EF Core and can not find one that is similar to my question.
My approach may be totally wrong so feel free to correct me as I am probably missing or don't understand something about EF Core and how tracking works.
Also, I am using A DBContextFactory because I am using Blazor Server as my front end, that's why I am passing the updatedInquiry to the AddAndUpdateInquiryAsync method.
Here is the set up:
Classes - I have excluded irrelevant properties
public class Inquiry
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public ICollection<PartWithPrice> PartsWithPrices { get; set; }
}
public class PartWithPrice
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
/// <summary>
/// The part involved
/// </summary>
public Part Part { get; set; }
public int PartId { get; set; }
public ICollection<Inquiry> Inquiries { get; set; }
public ICollection<ValveInternal> ValveInternals { get; set; }
}
public class ValveInternal
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public ICollection<PartWithPrice> PartWithPrices { get; set; }
}
As you can see, there is a many-to-many relationship between Inquiry and PartWithPrice, as well as PartWithPrice and ValveInternal.
Here is the method (not pretty, I know) in my service that I currently call from my front end to add or update an inquiry, I have left some code out that doesn't deal with the many-to-many relationships as i feel it isn't relevant to the problem , but will add it if deemed necessary.
public async Task<Inquiry> AddAndUpdateInquiryAsync(Inquiry updatedInquiry)
{
await using var ctx = Context.CreateDbContext();
var existingInquiry = await ctx.Inquiries
.Include(e => e.FileUploads)
.Include(e => e.Coating)
.Include(e => e.PartsWithPrices)
.ThenInclude(e => e.ValveInternals)
.Include(e => e.PartsWithPrices)
.ThenInclude(e => e.Part)
.ThenInclude(e => e.PartsCategory)
.Include(i => i.CompanyContact)
.ThenInclude(cc => cc.Emails)
.Include(i => i.CompanyContact)
.ThenInclude(cc => cc.PhoneNumbers)
.FirstOrDefaultAsync(i => i.Id == updatedInquiry.Id);
// Add it if it doesnt exist and return
if (existingInquiry == null)
{
// Becasue its a new context, Coatings, Parts, PartsCategories etc aren't being
// tracked so we have to track them manually like so. There is probably a better way
// to do this but I havent found one
updatedInquiry.CoatingId = ctx.Coatings.FirstOrDefault(x => x.Id == updatedInquiry.CoatingId).Id;
var partsWithPrices = updatedInquiry.PartsWithPrices;
foreach (var part in partsWithPrices)
{
part.Part = await ctx.Parts.FirstOrDefaultAsync(p => p.Id == part.Part.Id);
part.Part.PartsCategoryId = ctx.PartsCategories.FirstOrDefault(p => p.Id == part.Part.PartsCategoryId).Id;
}
ctx.Inquiries.Add(updatedInquiry);
await ctx.SaveChangesAsync();
return updatedInquiry;
}
ctx.Entry(existingInquiry).CurrentValues.SetValues(updatedInquiry);
// Handles Deletes
foreach (var part in existingInquiry.PartsWithPrices)
{
if (!updatedInquiry.PartsWithPrices.Any(x => x.Id == part.Id))
{
ctx.Remove(part);
}
}
foreach (var partWithPrice in updatedInquiry.PartsWithPrices)
{
var entity = existingInquiry.PartsWithPrices.FirstOrDefault(part => part.Id == partWithPrice.Id);
if (entity is null)
{
// If it doesn't exist in the table, add it
if (partWithPrice.ValveInternals is not null)
{
DealWithInternals(ctx, partWithPrice);
}
existingInquiry.PartsWithPrices.Add(partWithPrice);
}
else
{
// Update the record if it has changed
if (partWithPrice.ValveInternals is not null)
{
DealWithInternals(ctx, partWithPrice);
}
ctx.Entry(entity).CurrentValues.SetValues(partWithPrice);
}
}
ctx.SaveChanges();
return updatedInquiry;
}
private void DealWithInternals(DbContext ctx, PartWithPrice partWithPrice)
{
foreach (var valveInternal in partWithPrice.ValveInternals)
{
var valveInternalExists = partWithPrice.ValveInternals.FirstOrDefault(vi => vi.Id == valveInternal.Id);
if (valveInternalExists is null)
{
partWithPrice.ValveInternals.Add(valveInternal);
}
else
{
ctx.Entry(valveInternalExists).CurrentValues.SetValues(valveInternalExists);
}
}
}
Now, this works if I am just adding a single PartWithPrice to my inquiry, the bridge tables are updated and everything is fine. However, as soon as I try to add a second part, no records are added to the bridge table between the PartWithPrice and ValveInternal, but they are for the bridge table between Inquiry PartWithPrice. So, the PartWithPrice is still added, but the ValveInternals aren't.
Also, I am unable to delete a part and add a new one at the same time, I get an exception saying "The instance of entity type 'Coating' cannot be tracked because another instance with the key value '{Id: 211}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.", so obviously i am going about this the wrong way.
Here is the Debug View from the context change tracker when adding a single PartWithPrice incase this helps:
Coating {Id: 211} Unchanged
CompanyContact {Id: 1} Unchanged FK {CompanyId: 1}
CompanyContactPhoneNumber {Id: 1} Unchanged FK {CompanyContactId: 1}
Email {Id: 1} Unchanged FK {CompanyContactId: 1}
FileUpload {Id: 12} Unchanged FK {InquiryId: 31}
Inquiry {Id: 31} Unchanged FK {CoatingId: 211} FK {CompanyContactId: 1} FK {CompanyId: 1} FK {DRTVendorId: 15}
PartWithPrice {Id: -2147482647} Added FK {CoatingId: 211} FK {DRTVendorId: 15} FK {PartId: 32}
BallValve {Id: 32} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 32} FK {ValveManufacturerId: 115} FK {ValveTypeName: Ball Valve} FK {Id: 32}
CheckValve {Id: 35} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 35} FK {ValveManufacturerId: 117} FK {ValveTypeName: Check Valve} FK {Id: 35}
GateValve {Id: 36} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 36} FK {ValveManufacturerId: 118} FK {ValveTypeName: Gate Valve} FK {Id: 36}
Valve {Id: 34} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 34} FK {ValveManufacturerId: 116} FK {ValveTypeName: Butterfly Valve}
Valve {Id: 37} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 37} FK {ValveManufacturerId: 119} FK {ValveTypeName: Globe Or Control Valve}
Valve {Id: 38} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 38} FK {ValveManufacturerId: 122} FK {ValveTypeName: Screwed End Gate Valve}
Valve {Id: 39} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 39} FK {ValveManufacturerId: 123} FK {ValveTypeName: Threaded Check Valve}
Valve {Id: 40} Modified FK {DRTVendorId: <null>} FK {PartsCategoryId: 3} FK {Id: 40} FK {ValveManufacturerId: 130} FK {ValveTypeName: Wafer Check Valve}
PartsCategory {Id: 3} Modified
ValveInternal {Id: 9} Modified
ValveInternal {Id: 10} Modified
ValveInternal {Id: 11} Modified
InquiryPartWithPrice (Dictionary<string, object>) {InquiriesId: 31, PartsWithPricesId: -2147482647} Added FK {InquiriesId: 31} FK {PartsWithPricesId: -2147482647}
PartWithPriceValveInternal (Dictionary<string, object>) {PartWithPricesId: -2147482647, ValveInternalsId: 9} Added FK {PartWithPricesId: -2147482647} FK {ValveInternalsId: 9}
PartWithPriceValveInternal (Dictionary<string, object>) {PartWithPricesId: -2147482647, ValveInternalsId: 10} Added FK {PartWithPricesId: -2147482647} FK {ValveInternalsId: 10}
PartWithPriceValveInternal (Dictionary<string, object>) {PartWithPricesId: -2147482647, ValveInternalsId: 11} Added FK {PartWithPricesId: -2147482647} FK {ValveInternalsId: 11}
I am clearly having issues understanding how EF and EF tracking works and feel very lost.
I have been stuck on this for quite some time, so any help would be greatly appreciated.
Thanks, Taylor