dotnet-ef 6.0.1 scaffold ignores table with many to may relation

[kobruleht]

To reproduce, create database with tables containing many-to-many relation:

CREATE TABLE toode (
    toode character(20) primary key,
    tootepuu integer ,
    CONSTRAINT toode_tootepuu_fkey FOREIGN KEY (tootepuu)
        REFERENCES artomlii (artomaliik) 
);

CREATE TABLE artomlii (
    artomaliik integer primary key,
    treeparent integer,
    CONSTRAINT artomlii_treeparent_fkey FOREIGN KEY (treeparent)
        REFERENCES artomlii (artomaliik) 
);

CREATE TABLE artomadu (
    toode character(20) NOT NULL,
    artomaliik integer NOT NULL,
    CONSTRAINT artomadu_pkey PRIMARY KEY (toode, artomaliik),
    CONSTRAINT artomadu_artomaliik_fkey FOREIGN KEY (artomaliik)
        REFERENCES artomlii (artomaliik) ,
    CONSTRAINT artomadu_toode_fkey FOREIGN KEY (toode)
        REFERENCES toode (toode) 
);

Scaffold database using 6.0.1 version of dotnet-ef :

dotnet ef dbcontext scaffold "Host=localhost;Database=eeva;UserName=postgres" Npgsql.EntityFrameworkCore.PostgreSQL --no-onconfiguring --no-build --context EevaScaffoldContext --force

Observed:

Artomadu.cs file is not created.

public virtual DbSet<Artomadu> Artomadus { get; set; }
statement is missing in generated code.

Artomadu table is referenced from Toode table configuring code:

                entity.HasMany(d => d.Artomaliiks)
                    .WithMany(p => p.Toodes)
                    .UsingEntity<Dictionary<string, object>>(
                        "Artomadu",
                        l => l.HasOne<Artomlii>().WithMany().HasForeignKey("Artomaliik").HasConstraintName("artomadu_artomaliik_fkey"),
                        r => r.HasOne<Toode>().WithMany().HasForeignKey("Toode").HasConstraintName("artomadu_toode_fkey"),
                        j =>
                        {
                            j.HasKey("Toode", "Artomaliik").HasName("artomadu_pkey");
                            j.IndexerProperty<int>("Artomaliik").HasColumnName("artomaliik");
                        });

In 6.0.0 version of dotnet-ef those tables caused InvalidOperationException as described in #2118

[roji]

This is by-design: EF Core 6.0 detects simple many-to-many join tables in the database, and does not scaffold an entity for them. If you create a database from that scaffolded model, you'll see the Artomadu being created. If you need to include it explicitly in a query, you can also do that; see the EF docs for more information.

[kobruleht]

I need update, insert and delete Artomadu table like other tables.
Since Artomadu poco is not created, it is impossible.
Replacing primary key with surrogate id using

ALTER table artomadu drop constraint artomadu_pkey;
alter table artomadu add id serial primary key;

causes Artomadu class to be generated like for other tables.

[roji]

Yes - this is once again by design - see dotnet/efcore#22475. When EF Core detects a simple join pattern - with no extra columns, just associations between the two sides - it doesn't scaffold an explicit entity for it, since in most cases that's not needed. Once you change the table and it's no longer identified as "simple", the entity gets scaffolded.

You can always add the explicit entity type yourself after scaffolding if you wish. See dotnet/efcore#22475 (comment) for a discussion, dotnet/efcore#4038 also tracks allowing users to opt out of this behavior and always scaffold the join table.

[roji]

Duplicate of dotnet/efcore#4038

[JanneHarju]

My database has been using always numeric(10, 0) for some historic reasons. Scaffolding set datatype to decimal as it should where our code before EF is using long in C# side. So I have been using IDesignTimeServices to change them to be long in entities. My problem is related to this issue because many to many relation table is not going though IDesignTimeServices so it generates j.IndexerProperty("Lock").HasColumnType("numeric(10, 0)").HasColumnName("lock"); Where I would like to set data type to long instead of decimal because related table ID fields are long. Is there any way to affect how this part of code is generated?