ErikEJ
commented
11 months ago 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
ajcvickers
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.
1) Change 'Properties' to 'Property' -> Fail
2) Change 'Properties' to 'ListingProp' -> Fail
3) Change 'Properties' to 'ABCProp' -> Succeeds
In order to recreate this bug, please complete the following steps: 1) Create a new database in SQL Server (I have tried this with SQL Express Version 16.0.1000, SQL Server Enterprise Version 14.0.3421.10, and Azure SQL Server Version 12.0.2000.8 and get the same error on all versions of SQL Server) 2) Create the required tables in this order, to prevent issues with FK constraints. As mentioned above, I know that many of the FKs still have their old names. I am also aware that the Unique Non-Clustered Index on the "Properties" table looks weird since it is on IdA and IdC, but the primary Key is on IdA, IdB, and IdC. This is by design and is part of the larger DB structure that is required. Only leaving it in here as this is the simplest version of the DB that I have been able to reproduce the error with. I stopped short of check if the constraints or FK were causing the issue once I discovered that renaming the "Properties" table fixed the issue.
2.1) TableC
2.2) TableB
2.3) TableAB
2.4) AttributesByCategory
2.5) Properties
3) Open up Visual Studio. 4) Select "Create a New Project" 5) Select the "ASP.NET Core Web API" template 6) Set the framework to .NET 8.0 -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 7) Install the following NuGet packages: -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:
8) Set up your appsettings.json file with the connection to your database:
9) Run the PMC Command
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