I have 2 tables and a many-to-many relationship between them. For this many-to-many relationship to work, I created a new table. This many-to-many table has only the ids of the other 2 tables: id_table1 and id_table2.
I store data in this many-to-many table, but I don’t see it in my wizard, but it exists because I have GetAll API and GetById API working.
How can I update this table?
public void Update(T_TPS_PARAMETERS model)
{
_context = new CostingAppDb();
try
{
T_TPS_PARAMETERS oldTpsParameters = _context.T_TPS_PARAMETERS.FirstOrDefault(c => c.TPS_ID == model.TPS_ID);
decimal oldTpsParametersId = oldTpsParameters.TPS_ID;
foreach (var company in model.T_COMPANY)
{
T_COMPANY oldCompany = _context.T_COMPANY.FirstOrDefault(c => c.ID_COMPANY == company.ID_COMPANY);
decimal oldCompanyId = oldCompany.ID_COMPANY;
UpdateRelationship(oldCompanyId, oldTpsParametersId, company.ID_COMPANY, model.TPS_ID);
}
_context.Entry(model).State = System.Data.Entity.EntityState.Modified;
_context.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
private void InsertWithData(decimal companyId, decimal tpsId)
{
T_COMPANY c = new T_COMPANY { ID_COMPANY = companyId };
_context.T_COMPANY.Add(c);
_context.T_COMPANY.Attach(c);
T_TPS_PARAMETERS psd = new T_TPS_PARAMETERS { TPS_ID = tpsId };
_context.T_TPS_PARAMETERS.Add(psd);
_context.T_TPS_PARAMETERS.Attach(psd);
c.T_TPS_PARAMETERS.Add(psd);
_context.SaveChanges();
}
public void DeleteRelationship(decimal companyId, decimal tpsId)
{
var company = _context.T_COMPANY.FirstOrDefault(c => c.ID_COMPANY == companyId);
var tpsParameter = _context.T_TPS_PARAMETERS.FirstOrDefault(p => p.TPS_ID == tpsId);
company.T_TPS_PARAMETERS.Remove(tpsParameter);
_context.SaveChanges();
}
public void UpdateRelationship(decimal oldCompanyId, decimal oldTpsId, decimal newCompanyId, decimal newTpsId)
{
DeleteRelationship(oldCompanyId, oldTpsId);
InsertWithData(newCompanyId, newTpsId);
}
This is the error I got:
System.Data.Entity.Infrastructure.DbUpdateException: ‘An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.’
InvalidOperationException: The table/view does not have a primary key defined. The Entity is read-only
2
With EF, the many-to-many joining table can be explicitly mapped to an entity, or implicitly mapped. In most cases if the joining table simply links the two, then it is simpler to use the implicit mapping. For example, implicit mapping:
public class A
{
// ...
public virtual ICollection<B> Bs { get; } = [];
}
public class B
{
// ...
public virtual ICollection<A> As { get; } = [];
}
vs explicit:
public class A
{
// ...
public virtual ICollection<AB> ABs { get; } = [];
}
public class B
{
// ...
public virtual ICollection<AB> ABs { get; } = [];
}
public class AB
{
public virtual A A { get; set; }
public virtual B B { get; set; }
}
When removing associations, in either case you do so through the associated navigation property. In your case the DeleteRelationship method should be something like:
public void DeleteRelationship(decimal companyId, decimal tpsId)
{
var company = _context.T_COMPANY
.Include(c => c.TPS_PARAMETERS)
.Single(c => c.ID_COMPANY == companyId);
var tpsParameter = company.TPS_PARAMETERS.FirstOrDefault(p => p.TPS_ID == tpsId);
if (tpsParameter != null)
{
company.T_TPS_PARAMETERS.Remove(tpsParameter);
_context.SaveChanges();
}
}
Your code would also have worked if you added the .Include(c => c.TPS_PARAMETERS) with the fetch, provided the parameter fetched from the DB was in the company’s parameter list.
Whenever adding or removing items from an association, one-to-many or many-to-many, you should always ensure that related entities are loaded. This way EF can utilize the change tracking proxies to work out what to do when you add or remove an association.