Join tables and EF
EF automates some things for you. For this, it uses convention-over-configuration. If you stick to the convention, you can skip on a whole lot of common configuration.
For example, if your entity has a property named Id, EF will inherently assume that this is the PK.
Similarly, if two entity types have nav props that refer to each other (and only one direct link between the two entities exists), then EF will automatically assume that these nav props are the two sides to a single many-to-many relationship. EF will make a join table in the database, but it will keep this hidden from you, and let you deal with the two entity types themselves.
For some internal reasons the Join table has a specific format including 4 fields - Left Id (FK) - Right Id (FK) - StartDate (dateTime) - EndDate (datetime)
Your join table no longer conforms to what the content of a conventional and automatically generated EF join table is. You are expecting a level of custom configurability that EF cannot provide based on blind convention, which means you have to explicitly configure this.
Secondly, the fact that you have these additional columns implies that you wish to use this data at some point (presumably to show the historical relations between two entities. Therefore, it doesn't make sense to rely on EF's automatic join tables as the join table and it content would be hidden from the application/developer.
It's possible that the second consideration is invalid for you, if you don't need the application to ever fetch the ended entries. But the overall point still stands.
The solution here is to make the join record an explicit entity of its own. In essence, you are not dealing with a many-to-many here, you are dealing with a specific entity (the join element) with two one-to-many relationships (one for each of the two entity types).
This enables you to achieve exactly what you want. Your expectation of what EF can automate for you simply doesn't apply in this case.
Soft delete
Deleting a link is in fact setting the EndDate as not null but i don't now how to configure it in EF6.
In general, this is known as "soft delete" behavior, albeit maybe slightly differently here. In a regular soft delete pattern, when an entry is deleted, the database secretly retains the entry but the application doesn't know that and doesn't see the entry again.
It's unclear if you intend for ended entries to still show up in the application, e.g. the relational history. If this is not the case, then your situation is exactly soft delete behavior.
This isn't something you configure on the model level, but rather something you override in your database's SaveChanges behavior. A simple example of how I implement a soft delete:
public override int SaveChanges()
{
// Get all entries of the change trackes (of a given type)
var entries = ChangeTracker.Entries<IAuditedEntity>().ToList();
// Filter the entries that are being deleted
foreach (var entry in entries.Where(entry.State == EntityState.Deleted))
{
// Change the entry so it instead updates the entry and does not delete it
entry.Entity.DeletedOn = DateTime.Now;
entry.State = EntityState.Modified;
}
return base.SaveChanges();
}
This allows you to prevent deletions to the entities that you want this to apply to, which is the safest way to implement a soft delete as this serves as a catch-all for database deletes coming from whichever consumer uses this db context.
The solution to your question is pretty much the same. Assuming you named your join entity (see previous chapter) JoinEntity:
public override int SaveChanges()
{
var entries = ChangeTracker.Entries<JoinEntity>().ToList();
// Filter the entries that are being deleted
foreach (var entry in entries.Where(entry.State == EntityState.Deleted))
{
// Change the entry so it instead updates the entry and does not delete it
entry.Entity.Ended = DateTime.Now;
entry.State = EntityState.Modified;
}
return base.SaveChanges();
}
Word of warning
Soft deletes tend to be a catch-all for all entities (or at least a significant chuck of your database). Therefore, it makes sense to catch this at the db context level as I did here.
However, if this entity is unique in that it is soft deleted, then this is more of a business logic implementation than it is a DAL-architecture. If you start writing many custom rules for different types of entities, the db context logic is going to get clutterend and it's not going to be nice to work with because you need to account for multiple possible operations happening during the SaveChanges.
Take note to not push what is supposed to be a business logic decision to the DAL. I can't draw this line for you, it depends on your context. But evaluate whether the db context is the best place to implement this behavior.
