Optionally bring back join tables on scaffold db in EF Core 6

[janseris]

Hello, I have a project where I have Records and Photos.
Record can own a photo, if yes, the Photo gets RecordID.
Then there can be Records derived from a parent Record. These get assigned PhotoIDs via a join table as a "pointer" to the photo of the parent Record. Creating this "pointer" is inserting a tuple (RecordID, PhotoID) (which is also a primary key of the table) in a join table, let's call it RecordPhotoPointer table.

Now this scheme could be scaffolded well in EF Core 5 but in EF Core 6, there is an enhancement, where the M-N relationships are now fully supported and because of this, the join table is not generated at all.

Is there any way to generate this table back with EF Core 6? It is needed to associate the photos with the "child record".
What I do now is just generate the model with EF Core 5 in .NET 6 but I am not sure if it is ideal.

If EF Power Tools had this option, that would be nice (but if the scaffold-db command does, EF Power Tools just calls the command line, so it would include it just a while after it is available in the command line tool I believe.

Record
ID
ParentID (nullable)
Date
AuthorID
Text

Photo
ID
ImageData (FILESTREAM)
OwnerRecordID

RecordPhotoPointer
ReferencedPhotoID
RecordID

Thank you!

[ajcvickers]

@janseris Thanks for filing this. We're collecting feedback on this--see some discussion in #22475, starting here

A workaround is described in Breaking change: Many-to-many relationships without mapped join entities are now scaffolded

[janseris]

@janseris Thanks for filing this. We're collecting feedback on this--see some discussion in #22475, starting here

A workaround is described in Breaking change: Many-to-many relationships without mapped join entities are now scaffolded

Partial class DbContext with custom configuration is not overwritten on next db-scaffold, nice idea!

[ErikEJ]

But you also have to remove code manually from the generated code. Feel free to vote for the issue in the Power Tools backlog.

[ajcvickers]

@ErikEJ This will be investigated: #26555. But I think its unlikely to meet the patch bar.

[ErikEJ]

Power Tools issue: ErikEJ/EFCorePowerTools#1184

[proskilly]

As a work a round can't you just add a dummy field to the join table and then ef6 won't count it as a many to many and the join table will get modeled in it's own right?
I know it's not ideal though.

[freshe]

Is this something that is being considered for a upcoming .NET 6 patch? We cannot move from .NET 5 -> .NET 6 because of this. Thanks in advance.

[ErikEJ]

It has been implemented in EF Core Power Tools. @freshe

[freshe]

It has been implemented in EF Core Power Tools.

Thanks, Is there a way to use the tools in macOS or Linux?

[freshe]

Tested EF Core Power Tools to reverse engineer our database using the option 'Use many to many entity (EF Core 6)'
This seems to work as expected. The DbContext + models are now in EF Core 5x state again.
(All join tables back + sensible navigation property names)

Thanks!

The downside is I now have to open a Windows VM with Visual Studio to reverse engineer when we change our database.

A '--use-many-to-many' option (or something like that) for dotnet-ef would be great.

[ErikEJ]

@freshe Yes, there is a "way": ErikEJ/EFCorePowerTools#340 (comment)

[JanneHarju]

Because @ErikEJ's Power tool won't use IDesignTimeServices I cannot use it because I need to change data types which are generated. Our database has wrong datatypes in use and I need to change them from being decimal to long in Entities. This led to one problem. When I scaffold my database types are changed correclty in entities primary keys. But because of join tables are not generated they are not going through IDesignTimeServices's functions and cannot affect generated types in it. So therefore generated many to many relation configuration code contains wrong datatypes. Like this
j.IndexerProperty<decimal>("DluLock").HasColumnType("numeric(10, 0)").HasColumnName("dlu_lock");
So actually I don't need those entities I just way to affect how these configurations are made. But if those entities made it possible to handle this situation then I would want them.

[janseris]

I think that this is completed
There is a switch in EF Core Power Tools which enables this

[AlbertXiaoPeng]

#26820 (comment)

A '--use-many-to-many' option (or something like that) for dotnet-ef would be great.😁

[djswilly]

@janseris Thanks for filing this. We're collecting feedback on this--see some discussion in #22475, starting here

A workaround is described in Breaking change: Many-to-many relationships without mapped join entities are now scaffolded

I would like some guidance on why the preferred approach is to update code to use the many-to-many relationships directly. The article says that it's "very rare that the join entity type needs to be used directly when it contains only two foreign keys for the many-to-many relationships". In the projects I am working on it is very common to use the join entity types directly to add relationships between existing entities.

What is the preferred approach for doing this using the many-to-many relationship? Do you have to load all related entities from context and then add them to the many-to-many relationship of the other entity type? This does not seem like a "cleaner, more natural way" to create a relationship between existing entities compared to inserting ids into a join table using the join entity type. If there is a better approach it would be good to have examples in the breaking changes documentation.

[ajcvickers]

Do you have to load all related entities from context and then add them to the many-to-many relationship of the other entity type?

No. As long as the entities on both sides of the collection are tracked, then adding an entity to either of the collections will result in the join entity being created and inserted.

This does not seem like a "cleaner, more natural way" to create a relationship between existing entities compared to inserting ids into a join table using the join entity type.

The join table is an artifact of mapping to a relational database. It is meaningless in the object-oriented domain model. This document comes from this perspective. If you are instead thinking in terms of the relational database, then using the join table may me more natural, but that's not the typical way an O/RM is viewed.

[djswilly]

No. As long as the entities on both sides of the collection are tracked, then adding an entity to either of the collections will result in the join entity being created and inserted.

Thanks for the quick reply. This is specifically what I am having trouble with however - we have many use cases where we are updating one entity including relationships to another entity that we are not tracking and have no need to be tracking.

Using the Post and Tag relationship in the article, say we are updating a Post with data specific to Post as well as some TagIds. There is no reason to be tracking the Tags. If we have access to the join entity type we can create the relationship using TagIds and the join entity type only.

Is this a flawed approach? The alternative seems to be explicitly loading the Tags from context by TagId just to add them to the Post.Tag many-to-many relationship.

[ajcvickers]

If you want to add a Tag to a Post without creating an instance of the Tag object, then using the join entity directly is reasonable. However, I don't think this is intuitive at all.

[djswilly]

OK, so the recommended approach to the scenario above from an O/RM perspective is to create instances of the Tag objects and add them to the Post.Tag navigation collection? Thanks.

[markholst]

I think some more weight needs to be given to how the O/RM is being used, which I guess conflicts with the principles you're aiming to achieve.

The join table is an artifact of mapping to a relational database

I'm inclined to agree, yes this is usually the case. But then, it may also be a useful artefact of how we build and manage object hierarchies without having to fully inflate object instances.

I typically find many-to-many joins are used in situations where two disparate object hierarchies need to be associated with each other, so the joining 'entity' is usually our friend.

When considering a more holistic view around componentisation, using the Post and Tag example, a component designed to manage Posts will usually have no business managing Tags, but it may be required to associate Tags with a Post. Taking away the joining table means this component must load Tags to associate it, which would be beyond its remit.

I know in solutions I work with, we tend towards Contexts with very short lifetimes and limiting the number of objects tracked, meaning building associations now creates a significant overhead in terms of unnecessary database reads.
(tin foil hat time: maybe this is a way of boosting uptake of Azure SQL DTUs!)

Acknowledging some of the comments above, having the join tables realised through the code generation is useful, as adding attributes to these joining tables over time reduces the refactoring impact on the codebase.

I think introducing the switch --use-many-to-many is a very good idea as it will allow the default design needs of a project to be addressed at a global level.

Usually I instruct our teams to simply accept the new behaviours coming through from tooling, as we don't want to be going against the grain. In this situation though it does have a fairly significant design impact, especially on our larger and more complicated systems.

In terms of our practices:

• database first (we use dbup to manage changes)
• some co-owned databases (that is, between two solutions; we don't find this ideal, it is part of migrating a legacy app to modern)
• complex database model and data transformations occur often, so sql is more natural
• short lived contexts managed by the DI container
• .NET 6 solution, object oriented design applying SOLID principles
• agile development (lots of change and often)

[ajcvickers]

@markholst

Taking away the joining table means this component must load Tags to associate it,

Can you explain a bit more why you are making the assertion? Why must Tags be loaded just to add a new Tag to it?

[markholst]

For the Posts to hold a collection of Tags, I must first read/inflate a set of Tag entities and add these to the Posts.Tags collection.

I'm guessing you're leading towards the suggestion that we create new Tag instances, set the Id, and set them as 'existing' entities in the change tracker? A trick I have employed before, it is very clunky.

Another situation I came across yesterday after posting relates to an Entity that references many other Entities in the system. Previously we only wanted to get the Ids to which it references, so we'd .Include all of the joining tables. It was changed over to load the full entities - becoming significantly slower. The alternative was to make the implementation more complicated by projecting out the Ids.

[ajcvickers]

@markholst

For the Posts to hold a collection of Tags, I must first read/inflate a set of Tag entities and add these to the Posts.Tags collection.

If Post is a tracked entity (which is must be to do SaveChanges) and has an empty Tags collection, then adding a new Tag to that collection will create a join entity that will be inserted when SaveChanges is called. If the Tag already exists in the database, then marking it as Unchanged will mean that only the join entity is inserted. There is no need to load all other tags from the database.

[markholst]

If I'm in the situation where I am creating or editing a Post, and I have a set of Tag Ids (for existing tags) that I want to associate, how do I do this without loading the Tag entities?

[markholst]

Wow, okay.

I guess this speaks to the emphasis being placed on 'small scale getting started applications' over large enterprise systems.

[ErikEJ]

For anyone needing this, the EF Core Power Tools now also has a cross platform CLI edition.

[freshe]

For anyone needing this, the EF Core Power Tools now also has a cross platform CLI edition.

🎉