Enhance scaffolding to prepend schema names to entity classes

[GeorgyRezchikov]

In the EF repository(https://github.com/dotnet/efcore), the question was raised about the presence in the database of two tables with the same names but in different schemas.

dotnet/efcore#3988
dotnet/efcore#12432

Is it possible to solve this problem with your tool?

EF Core Power Tools version: 2.4.157
Database engine: SQL Server
Visual Studio version: Visual Studio 2019 15.7

[ErikEJ]

I believe you may be able to solve this with the renaming feature - if you can share a simple repro (DDL scripts), I can have a look.

[GeorgyRezchikov]

`USE [master]
GO

CREATE DATABASE [TestDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestDb', FILENAME = N'E:\DATA\SQL\IPS2016\TestDb.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TestDb_log', FILENAME = N'F:\LOG\SQL\IPS2016\TestDb_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

ALTER DATABASE [TestDb] SET COMPATIBILITY_LEVEL = 130
GO

ALTER DATABASE [TestDb] SET MULTI_USER
GO

ALTER DATABASE [TestDb] SET QUERY_STORE = OFF
GO

USE [TestDb]
GO

ALTER DATABASE [TestDb] SET READ_WRITE
GO

CREATE SCHEMA [IR];
GO

CREATE TABLE [IR].[Attribute](
[ID] [int] NOT NULL,
[AttributeGroupId] [int] NOT NULL,
[DataTypeId] [int] NOT NULL,
[Code] varchar(50) NOT NULL,
[Name] varchar(250) NOT NULL,
[Description] varchar(500) NULL,
[IsMultiplyValues] [bit] NOT NULL,
[FormatString] varchar(250) NULL,
[IsMain] [bit] NOT NULL,
CONSTRAINT [PK_ATTRIBUTE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Attribute_Code_IR] UNIQUE NONCLUSTERED
(
[Code] ASC,
[AttributeGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Attribute_Name_IR] UNIQUE NONCLUSTERED
(
[Name] ASC,
[AttributeGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE SCHEMA [Com];
GO

CREATE TABLE [Com].[Log](
[ID] [int] NOT NULL,
[LogDate] [datetime] NOT NULL,
[IsSuccess] [bit] NOT NULL,
[Message] varchar(500) NOT NULL,
[Details] varchar NOT NULL,
[Server] varchar(250) NOT NULL,
[Workstation] varchar(250) NOT NULL,
[UserName] varchar(250) NOT NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE SCHEMA [TV];
GO

CREATE TABLE [TV].[Attribute](
[ID] [int] NOT NULL,
[AttributeGroupId] [int] NOT NULL,
[DataTypeId] [int] NOT NULL,
[Code] varchar(250) NOT NULL,
[Name] varchar(50) NOT NULL,
[Descripton] varchar(500) NULL,
[IsMultiplyValues] [bit] NOT NULL,
CONSTRAINT [PK_ATTRIBUTE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Attribute_Code_TV] UNIQUE NONCLUSTERED
(
[AttributeGroupId] ASC,
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Attribute_Name_TV] UNIQUE NONCLUSTERED
(
[AttributeGroupId] ASC,
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
`

[ErikEJ]

OK, I get this without doing anything custom:

 public partial class NorthwindContext : DbContext
 {
    public virtual DbSet<Attribute> Attributes { get; set; }
    public virtual DbSet<Attribute1> Attributes1 { get; set; }
    public virtual DbSet<Log> Logs { get; set; }

What is your desired outcome?

[GeorgyRezchikov]

Like this:

 public partial class NorthwindContext : DbContext
 {
    public virtual DbSet<IrAttribute> IrAttributes { get; set; }
    public virtual DbSet<TvAttribute> TvAttributes { get; set; }
    public virtual DbSet<Log> Logs { get; set; }
    ...
 }

OR

 public partial class NorthwindContext : DbContext
 {
    public virtual DbSet<Ir_Attribute> Ir_Attributes { get; set; }
    public virtual DbSet<Tv_Attribute> Tv_Attributes { get; set; }
    public virtual DbSet<Log> Logs { get; set; }
    ...
 }

i.e. prepend schema names to entity classes

for the dbo schema, you can leave only the table name, if possible

[ErikEJ]

    public partial class NorthwindContext : DbContext
    {
        public virtual DbSet<IrAttribute> IrAttributes { get; set; }
        public virtual DbSet<Log> Logs { get; set; }
        public virtual DbSet<TvAttribute> TvAttributes { get; set; }

efpt.renaming.json:

[
  {
    "UseSchemaName": false,
    "SchemaName": "IR",
    "Tables": [
      {
        "Name": "Attribute",
        "NewName": "IrAttribute"
      }
    ]
  },
  {
    "UseSchemaName": false,
    "SchemaName": "TV",
    "Tables": [
      {
        "Name": "Attribute",
        "NewName": "TvAttribute"
      }
    ]
  }
]

[GeorgyRezchikov]

But can this be applied to all cases (tables)? And not specifically for two tables? That is, for all tables prepend schema name.

[ErikEJ]

Yes, if you add it to the file (you could create a small tool to build the json file, see the wiki for a sample tool)

[GeorgyRezchikov]

Thank you!

[ErikEJ]

@GeorgyRezchikov latest daily also allows you to use regular expression for name replacements

[rasaconsulting-sandy]

This is an awesome tool thanks! I was wondering if it's possible to update the configuration file anyway to have multiple ModelNamespace and Output paths for a single dbcontext? What I'm trying to achieve is to save the entities (i.e. db tables) into an 'Entities' folder and the models (i.e. db views) into a 'Models' folder. Currently I'm doing the updates manually each time I refresh the dbcontext and associated objects.

[ErikEJ]

@rasaconsulting-sandy why are you posting this on a closed issue?

What are "db views" - there is no distinction between views and tables with EF Core, so if that is what you mean, the answer is probably no.

[rasaconsulting-sandy]

Hi Erik,
By "db views" I mean database views - the ones created by joining tables together and saving in the SQL Server database. I use these as my "Model" classes.
Sorry to post on this closed issue - it seemed relevant at the time (scaffolding and configuration of the code generated) and I didn't realise it was closed until after i posted.
Thanks again for your tool.
Sandy