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

[ajcvickers]

[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]

C# 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)
(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