Unable to Scaffold DBContext if tables in SQL Server have certain names #32571
Comments
|
I have reduced the repro to this (removing a table): CREATE TABLE [dbo].[TableC](
[IdC] [BIGINT] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_IdC] PRIMARY KEY CLUSTERED
(
[IdC] 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 TABLE [dbo].[TableB](
[IdB] [BIGINT] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_IdB] PRIMARY KEY CLUSTERED
(
[IdB] 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 TABLE [dbo].[AttributesByCategory](
[IdB] [BIGINT] NOT NULL,
[IdC] [BIGINT] NOT NULL,
CONSTRAINT [PK_IdB_IdC] PRIMARY KEY CLUSTERED
(
[IdB] ASC,
[IdC] 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
ALTER TABLE [dbo].[AttributesByCategory] WITH CHECK ADD CONSTRAINT [FK_AttributesByCategory_Attributes] FOREIGN KEY([IdC])
REFERENCES [dbo].[TableC] ([IdC])
GO
ALTER TABLE [dbo].[AttributesByCategory] WITH CHECK ADD CONSTRAINT [FK_AttributesByCategory_Category] FOREIGN KEY([IdB])
REFERENCES [dbo].[TableB] ([IdB])
GO
CREATE TABLE [dbo].[Properties](
[IdA] [BIGINT] NOT NULL,
[IdB] [BIGINT] NOT NULL,
[IdC] [BIGINT] NOT NULL,
CONSTRAINT [PK_IdA_IdB_IdC] PRIMARY KEY CLUSTERED
(
[IdA] ASC,
[IdB] ASC,
[IdC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UC_IdA_IdC] UNIQUE NONCLUSTERED
(
[IdA] ASC,
[IdC] 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
ALTER TABLE [dbo].[Properties] WITH CHECK ADD CONSTRAINT [FK_Properties_AttributesByCategory] FOREIGN KEY([IdB], [IdC])
REFERENCES [dbo].[AttributesByCategory] ([IdB], [IdC])
GODoing this fixes the NRE: ALTER TABLE [dbo].[Properties] DROP CONSTRAINT [FK_Properties_AttributesByCategory]
GO |
|
@michaelbyrdmba I am not sure it is related to naming! |
|
@ErikEJ Just to confirm, this is indeed an issue with EF Core 7/8 not being able to handle this constraint since it works in EF Core 6. Is that correct? Otherwise, why does that constraint throw an NRE? From the documentation I've been able to find there should be no reason for the NRE. As I mentioned in my initial post, that constraint is required in the overall larger schema of the database but does not make a lot of sense in this reduced version. |
|
I talked with another developer today and mentioned the issue with the table name. He mentioned that I should check to see if they were for some reason being scaffolded alphabetically, instead of by dependency. We validated this theory and this does seem to be the case, which aligns with what I mentioned above:
This statement is partially true, but it will only scaffold successfully if you name it something in the correct alphabetical order. For instance:
As a side note, I'm happy to try and help debug if I can. It would be great to further understand how to debug EF Core directly when something throws an NRE. I haven't been able to find much documentation on how to do this other than these two articles, both of which seem to be targeting an earlier version of .NET and no longer work (or the code/instructions are not clear on how to successfully debug a scaffold command): |
|
Note for triage: this is a regression from 6 to 7 related to the T4 template introduction. |
|
@ajcvickers Is this something that I can help triage? |
|
@michaelbyrdmba No, thanks. This is something the team needs to do. It will likely happen in the new year, since a lot of the team will be out the rest of the year. |
|
@michaelbyrdmba Just realised you can work around this by preserving the many to many entity with EF Core Power Tools! https://github.com/ErikEJ/EFCorePowerTools/wiki/Reverse-Engineering#code-generation |
ajcvickers
added
the
closed-fixed
… will then be used for scaffolding Covers #32571
|
@ajcvickers Just to be clear, this will be fixed in EF9 (only)? |
|
@ErikEJ Despite what I said yesterday, we will discuss. |
|
@ajcvickers @ErikEJ Just to clarify, is this going to be fixed in EF8 (if so, which version number) or in EF9? |
|
It is being considered |
|
@ajcvickers I saw that this issue got closed and is going to be released in EF 8.0.2. Which NuGet package(s) will need to be updated for this to work and when will 8.0.2 be released? This was the list of NuGet packages that I had previously installed: |
|
@michaelbyrdmba This still needs to approved by management. 8.0.2 is currently scheduled for February. You should always update all EF packages you are using to the same version. |
I've been working on switching a ASP.NET Core Web API from using .NET 6 to .NET 8. This project is database first and uses Entity Framework (6 and 8 respectively) to scaffold the DBContext and models into the API.
I have tried several different methods of upgrading the project to see if I can get .NET 8 and EF Core 8 to properly scaffold my DB. I have tried both creating a brand new ASP.Net Core Web API from the Visual Studio template and scaffolding in the brand new project, as well as trying to use the .NET upgrade assistant on the existing .NET 6 API.
If I use the .NET upgrade assistant, the models and DBContext get moved over to the .NET 8 project, however, if you try and run the Scaffold-DBContext command in PMC after the upgrade it will still fail, even if you have the -Force flag in your command to overwrite your existing models.
The error message that you get back is as follows:
However, through lots of trial and error, I've discovered that it seems to be throwing this exception if you have tables with certain names. For instance, I can change the name of either the 'Properties' or 'AttributesByCategory' table and it will scaffold successfully.
However, again there seem to be certain strings that cause it to throw the NRE that I was getting up above.
Here are some examples. One thing to note, I change the name of the table directly from the database diagram in the properties window and then make sure to save the database diagram to ensure that changes take affect and the all existing relationship and references are maintained. Also, I know that some of the FKs still have their old names. I was trying to recreate the issue in the smallest form possible. I renamed many of the tables and the PKs during this process and then scaffolded after each change to see what would happen. This is what I am able to get to either succeed or fail consistently.
In order to recreate this bug, please complete the following steps:
2.1) TableC
2.2) TableB
2.3) TableAB
2.4) AttributesByCategory
2.5) Properties
-Leave the Authentication type as "None"
-Keep "Configure for HTTPS" checked
-Leave "Enable Docker" unchecked
-Keep "Enable for OpenAPI support" checked
-Leave "Do not use top-level statements" unchecked
-Keep "Use Controllers" checked
-Microsoft.EntityFrameworkCore 8.0.0
-Microsoft.EntityFrameworkCore.Design 8.0.0
-Microsoft.EntityFrameworkCore.SqlServer 8.0.0
-Microsoft.EntityFrameworkCore.Tools 8.0.0
This is what my .csproj file looks like after installing all of the packages.
As a side note, you will likely need to override the "Invariant Globalization" setting in the .csproj file to "false", as seen above, otherwise you may get the following error when you try and run the scaffold command:
{ "UseInMemoryDatabase": false, "ConnectionStrings": { "DefaultConnection": "Server=SERVERNAME;Initial Catalog=DATABASENAME;Trusted_Connection=True;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;" }, "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "AllowedHosts": "*" }This should reproduce the error that throws the NRE error. If you then go into the database diagram and change the name of the "Properties" table to "ABCProp", save the database and then try to run the PMC command again, the scaffold should succeed.
One final thing, if you follow the exact same instructions as above for the ASP.NET Core Web API, but set it up for .NET 6.0 and install all of the same NuGet packages as above in version 6.0.0, the scaffold command will succeed without changing the name of the tables in the DB. This seems to be a breaking change with EF Core 7 and 8.
If this can't be fixed to allow all tables names, it would at least be nice to have a list of table names that will break EF Core and/or instead of throwing an NRE, at least display the affected table names that need to be changed in the stack trace.
Please let me know if you have any further questions or if I can provide any other assistance on this error. I tried to be as thorough as possible when explaining what cause the issue.
EF Core version: 8.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows 10
IDE: Tried in both Visual Studio Community 2022 64-Bit Version 17.8.3 and Version 17.7.6
The text was updated successfully, but these errors were encountered: