Detect simple join tables in reverse engineering and create many-to-many relationships

[ajcvickers]

No description provided.

[ajcvickers]

Notes from triage:

• The pri-zero here is that a database created by EF6 with many-to-many relationships should reverse-engineer to EF Core with many-to-many relationships. Specifically, the join table must contain only columns for the two FKs and these columns must also form the primary key. If the table has a separate PK or any payload columns, then it should not be considered many-to-many.
• Meeting the EF6 scenario should also mean that simple, fully by-convention many-to-many relationships created by EF Core will round-trip.
• It's too late to do this for EF Core 5.0.

@ErikEJ Might you consider doing this in the EF Core Power Tools? That would make something available for 5.0. Also, in the future, if you want to do the PR for here, that would be awesome! 😁

[ErikEJ]

@ajcvickers I assume that would require changes to code generation, then?

[ajcvickers]

@ErikEJ Yes, it probably would. @bricelam?

[ErikEJ]

Ok - I have already reluctantly pulled in some Internal code anyway. Happy to take a PR for this!!

[smitpatel]

It would probably require some changes to code gen. Currently there is no processing of SkipNavigations in code gen at all. I think best place to identify and add skip navigation would be RelationalScaffoldingModelFactory (now we are deep in internal territory lol) to add skip navs to model and code gen will handle it as such.

[ErikEJ]

Hmmm.. where would I specify the table name (since it most likely will not match convention)

Assume in fluent config, but how, since there is no EntityType to refer to??

[smitpatel]

modelBuilder.Entity<One>().HasMany(e => e.TwoSkip).WithMany(e => e.OneSkip).UsingEntity(e => e.ToTable("tableName"));

EF will fill in actual entity type appropriately, whatever that type would be by convention. (Likely Dictionary<string, object>)
(Untested code)

[bmoteria]

@smitpatel The provided solution doesn't seem to work in my scenario as the table "FeatureVehicle" contains two columns which has underscore ("Feature_ID" and "Vehicle_VehicleID"). (cc @ajcvickers)

When query is executed it throws following exception:

Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type '<PRODUCT>.Data.Context.<PRODUCT>DbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'FeaturesID'.
Invalid column name 'VehiclesID'.
Invalid column name 'VehiclesID'.
Invalid column name 'VehiclesID'.
Invalid column name 'FeaturesID'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
ClientConnectionId:76b39b9f-06db-43a9-ae78-d7820dd60584

Here are my entities:

public partial class Vehicle
{
     ....
     public ICollection<Feature> Features { get; set; }
}

public partial class Feature
{
     ....
     public ICollection<Vehicle> Vehicles { get; set; }
}

[smitpatel]

@bmoteria - Your issue is not related to the issue you are posting on. Please avoid off-topic discussions. Your column names in the join entity does not match what is generated conventionally, so you will need to configure them manually using HasColumnName.

[cgountanis]

I am ready for this change, we use database first and it would be great if it automatically did the Many to Many so something like context.Posts.Include(e => e.Tags) would work automatically.

CREATE TABLE [Posts] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NULL,
    CONSTRAINT [PK_Posts] PRIMARY KEY ([Id])
);

CREATE TABLE [Tag] (
    [Id] int NOT NULL IDENTITY,
    [Text] nvarchar(max) NULL,
    CONSTRAINT [PK_Tag] PRIMARY KEY ([Id])
);

CREATE TABLE [PostTag] (
    [PostsId] int NOT NULL,
    [TagsId] int NOT NULL,
    CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostsId], [TagsId]),
    CONSTRAINT [FK_PostTag_Posts_PostsId] FOREIGN KEY ([PostsId]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Tag_TagsId] FOREIGN KEY ([TagsId]) REFERENCES [Tag] ([Id]) ON DELETE CASCADE
);

CREATE INDEX [IX_PostTag_TagsId] ON [PostTag] ([TagsId]);

@ajcvickers
e.g. Scaffold-DbContext "Server=localhost;Database=XXX;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context Context -ContextDir Models -Force

[ErikEJ]

Power Tools simply uses the default CSharpDbContextGenerator.cs, but it hooks into the DatabaseModel generation, and manipulates that in flight.

I was looking for a simple way to exclude a table from many-to-many, not include, to allow users to opt out on a per table basis.

[smitpatel]

Looking at power tools code, I see that you already have one derived class for RelationalScaffoldingModelFactory, if you are fine overriding it and copying some code then overriding VisitForeignKeys would get you that in 6.0. Basically you use the code without skip navs (how it was in 5.0) to skip M2M join table. Once we don't add skip navigations there, rest of the code should generate code for it like a normal entity.

[ErikEJ]

@smitpatel Perfect! I will consider that if customers ask for it!

[ajcvickers]

@ErikEJ So it looks like right now you don't need us to add/change things for you?

[ErikEJ]

@ajcvickers Correct!

[Zapnologica]

Not to sure how we can vote for this feature, however we have just migrated to .net 5 and have a very large database with a large number of many to many joins, and the current scaffolding has not mapped them correctly at all.

To go and manually do all the Join entities is gonna take ages.

[ErikEJ]

@Zapnologica This is implemented in EF Core 6!

[Zapnologica]

Oh awesome stuff I can't wait for that to be released.

…

On Fri, 29 Oct 2021, 07:34 Erik Ejlskov Jensen, ***@***.***> wrote: @Zapnologica <https://github.com/Zapnologica> This is implemented in EF Core 6! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#22475 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA34ZUJ3HV2UP7ETADZ76YDUJI56VANCNFSM4RFGVVXA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[ErikEJ]

@Zapnologica it will happen within two weeks!

[hgsenger]

Hi, although this is a nice feature we have the following scenario, where we have some trouble with it:

• Table 'A'
• Table 'B'
• MappingTable1 A <-> B (m:n)
• MappingTable2 A <-> B (m:n)

Scaffolding the database context fails in this case with the error message that the navigation property Table 'B' cannot be added twice to Table 'A'.

It would be nice if the navigation property names could be based on the intersection table name(s) to circumvent this issue.

[AndriySvyryd]

@hgsenger This will be fixed in 6.0.1

[glucklichman]

Hi, I take a simple many-to-many relationship. After scaffolding I will take one entity with two Reference navigation and two entities without collection navigation. As I read the documentation, the two entities mave to include the collection navigation.

public partial class PersonTrainingCourse
{
        public int PersonId { get; set; }
        public int CourseId { get; set; }

        public virtual TrainingCourse Course { get; set; }
        public virtual Person Person { get; set; }
}

And dbContext includes only one-to-many relationships

modelBuilder.Entity<PersonTrainingCourse>(entity =>
            {
                ...

                entity.HasOne(d => d.Course)
                    .WithMany()
                    .HasForeignKey(d => d.CourseId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Person<>TrainingCourse_TrainingCourse");

                entity.HasOne(d => d.Person)
                    .WithMany()
                    .HasForeignKey(d => d.PersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Person<>TrainingCourse_Person");
            });

[moritzinio]

Hi, I take a simple many-to-many relationship. After scaffolding I will take one entity with two Reference navigation and two entities without collection navigation. As I read the documentation, the two entities mave to include the collection navigation.

public partial class PersonTrainingCourse
{
        public int PersonId { get; set; }
        public int CourseId { get; set; }

        public virtual TrainingCourse Course { get; set; }
        public virtual Person Person { get; set; }
}

And dbContext includes only one-to-many relationships

modelBuilder.Entity<PersonTrainingCourse>(entity =>
            {
                ...

                entity.HasOne(d => d.Course)
                    .WithMany()
                    .HasForeignKey(d => d.CourseId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Person<>TrainingCourse_TrainingCourse");

                entity.HasOne(d => d.Person)
                    .WithMany()
                    .HasForeignKey(d => d.PersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Person<>TrainingCourse_Person");
            });

I have the same Problem. DBContext only hast one-to-many relationships even thought the database has many-to-many relations.

[ErikEJ]

@moritzinio please open a new issue, and share your database schema.

[moritzinio]

@ErikEJ I will tomorrow thank you.

[sommmen]

@ajcvickers hiya this docs page https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many

Points to this workitem which seems to be closed. Is this now fixed or not? perhaps the docs should be updated?

[smitpatel]

Filed dotnet/EntityFramework.Docs#3760

[Scionn]

@ajcvickers hiya this docs page https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many

Points to this workitem which seems to be closed. Is this now fixed or not? perhaps the docs should be updated?

I don't think its SOLVED, we have the same problem today with the latest version of EF core 6.0.6 (at the moment) scaffolding a Postgresql database with two m-2-m tables...

[ajcvickers]

@Scionn Please open a new issue and post the schema and command that results in the issue so that we can investigate.

[RicardOlivo]

Maybe missing something here, but I'm baffled at how or why would Microsoft arbitrarily force a set of rules and decide which and when a table will be reverse engineered and when to skip it, without even a flag to supress it. Is there a way to full scaffold using net 6 without this nonsense, other than doing it by hand? thanks

[ErikEJ]

@RicardOlivo Yes, just use EF Core Power Tools!

[tuyendam00]

I still want ef core gen class mapping many-many in net 6.0, how to do it
I want:
modelBuilder.Entity(
entity =>
{
entity.HasKey(e => new { e.GenerateProjectId, e.ProgrammingLanguageId });

            entity.ToTable("GenerateProjectProgrammingLanguageMapping", "MDM");

            entity.HasOne(d => d.GenerateProject)
                .WithMany(p => p.GenerateProjectProgrammingLanguageMapping)
                .HasForeignKey(d => d.GenerateProjectId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_GenerateProjectProgrammingLanguageMapping_GenerateProject");

            entity.HasOne(d => d.ProgrammingLanguage)
                .WithMany(p => p.GenerateProjectProgrammingLanguageMapping)
                .HasForeignKey(d => d.ProgrammingLanguageId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_GenerateProjectProgrammingLanguageMapping_ProgrammingLanguage");
        });

Ef gen:
entity.HasMany(d => d.ProgrammingLanguages)
.WithMany(p => p.GenerateProjects)
.UsingEntity<Dictionary<string, object>>(
"GenerateProjectProgrammingLanguageMapping",
l => l.HasOne().WithMany().HasForeignKey("ProgrammingLanguageId").OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_GenerateProjectProgrammingLanguageMapping_ProgrammingLanguage"),
r => r.HasOne().WithMany().HasForeignKey("GenerateProjectId").OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_GenerateProjectProgrammingLanguageMapping_GenerateProject"),
j =>
{
j.HasKey("GenerateProjectId", "ProgrammingLanguageId");

                        j.ToTable("GenerateProjectProgrammingLanguageMapping");
                    });

[RicardOlivo]

@tuyendam00 I did it using net core 3.1, then copy/pasted into 6. Sad I know.

[ErikEJ]

@tuyendam00 @RicardOlivo Just use EF Core Power Tools, it has an option to generate the actual entities