1

I have a SQL surrogate table defined as follows:

CREATE TABLE [dbo].[Blah] 
(
    [FooID] INT NOT NULL,
    [ClientID] INT NOT NULL,

    CONSTRAINT [PK_Blah] 
        PRIMARY KEY CLUSTERED ([FooID] ASC, [ClientID] ASC),
    CONSTRAINT [FK_Blah_Foo] 
        FOREIGN KEY ([FooID]) REFERENCES [dbo].[Foo] ([FooID]) 
                ON DELETE CASCADE,
    CONSTRAINT [FK_Blah_Client] 
        FOREIGN KEY ([ClientID]) REFERENCES [dbo].[Client] ([ClientID]) 
                ON DELETE CASCADE
);

I have noticed that after upgrading from .NET Core version 3 to 6, the db-first scaffolding tool changed its behavior and stopped generating the surrogate tables. I have done some research and checked several options on how to force EF Core to generate the Blah table.

1) One of the options is to use the -Tables Blah parameter in the Scaffold-DbContext command. However, this only generates the Blah table, and all the others are gone, which means that I would need to list all the existing SQL tables in this command, which is not generic at all and has many disadvantages.

Scaffold-DbContext "..." Microsoft.EntityFrameworkCore.SqlServer 
    -OutputDir Entities -ContextDir . -DataAnnotations 
    -UseDatabaseNames -Context AuthorisationContext -Force 
    -NoOnConfiguring -NoPluralize -Tables Blah

2) The other solution I found is that I can just run the above scaffolding command to generate the Blah entity class, revert all the changes on the DbContext, and add the Blah definition into the MyDbContext partial class that I created manually for such needs where you need to extend the dbContext manually

public partial class MyDbContext : DbContext
{
    public virtual DbSet<Blah> Blah { get; set; }
    
    //...
}

To be honest, for now, I see option 2) as the only solution for me. However, I wanted to check if there is any other nice way to achieve what I want purely by modifying the scaffolding command and it's params? Cheers.

Edit I also played with .NET 7 and I'm getting similar results.

GoldenAge
  • 2,918
  • 5
  • 25
  • 63
  • What you call "surrogate table" is actually a [join table table](https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many) and is only really needed in M:N relations. Whether they're generated or not depends on the relations between *entities* and yes, mapping and table generation *has* changed. What are the actual entities and DbContext configuration? Is this a 1:1, 1:N, M:N relation? – Panagiotis Kanavos May 12 '23 at 13:33
  • 1
    `the db-first scaffolding tool ... stopped generating the surrogate tables` don't you mean that it stopped generating the *join entity*? Db-first means the database already exists. And yes, when the table is just a bridge table EF Core no longer needs to generate join types explicitly. That's [explained in the Many-to-Many docs](https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many#understanding-many-to-many-relationships). In the doc example, `PostTag` is no longer needed. There's `Post.Tags`, `Tag.Posts` which contain `Tag` and `Post` objects. – Panagiotis Kanavos May 12 '23 at 13:40
  • @PanagiotisKanavos yes you are correct I meant the join table thing. It's M:N relationship. – GoldenAge May 12 '23 at 14:04
  • @PanagiotisKanavos if you post the answer I can accept it, as I followed your advice and that resolved my issue. Thanks – GoldenAge May 14 '23 at 06:53

1 Answers1

1

When we have only foreign key constraints, EF DB scaffolding will ignore those tables. To fix this issue, we can add an Identity column to the existing table and then run the scaffolding command.

Boosa Sandeep
  • 21
  • 4
  • Or just use the generated types without modification. There's no issue here. There's no longer any need for bridge/join types. Even if such a type is desired, there's no need for an extra PK. `.UsingEntity();` will configure the Post-Tag relation to use `PostTag` as a bridge type – Panagiotis Kanavos May 12 '23 at 13:40