SQL Server scaffolding fails because of columns with no database type #25729

0xced · 2021-08-26T16:27:51Z

Using Entity Framework Core .NET Command-line Tools version 6.0.0-preview.7.21378.4

dotnet ef --version                                                                                                                                                                             
Entity Framework Core .NET Command-line Tools
6.0.0-preview.7.21378.4

How to reproduce:
Create a project (efscaffold.csproj) with the following content:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0-preview.7.21378.4" PrivateAssets="all" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.0-preview.7.21378.4" />
  </ItemGroup>

</Project>

Run the following command:

dotnet ef dbcontext scaffold --no-onconfiguring "Server=sqlprosample.database.windows.net;Database=sqlprosample;User=sqlproro;Password=nh{Zd?*8ZU@Y}Bb#" Microsoft.EntityFrameworkCore.SqlServer

Expected: scaffolding works, a DbContext and entities are generated.

Actual: scaffolding crashes with the following error:

System.ArgumentNullException: Value cannot be null. (Parameter 'column')
   at Microsoft.EntityFrameworkCore.Utilities.Check.NotNull[T](T value, String parameterName) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000111+0x1a
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.GetPropertyName(DatabaseColumn column) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000221+0x0
   at System.Linq.Enumerable.SelectListIterator`2.ToArray() in System.Linq.dll:token 0x60001dc+0x20
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) in System.Linq.dll:token 0x600011a+0x1b
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600022d+0xa2
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndexes(EntityTypeBuilder builder, ICollection`1 indexes) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600022c+0x28
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000226+0xea
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000225+0x28
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000222+0x78
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, ModelReverseEngineerOptions options) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600021e+0xf6
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600023d+0xfe
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600043e+0x11c
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluarlize) in Microsoft.EntityFrameworkCore.Design.dll:token 0x60003ed+0x32
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0() in Microsoft.EntityFrameworkCore.Design.dll:token 0x60006ed+0x0
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0() in Microsoft.EntityFrameworkCore.Design.dll:token 0x60006f3+0x0
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600066a+0xc

The text was updated successfully, but these errors were encountered:

smitpatel · 2021-08-26T16:34:29Z

Can you share your database schema script?

0xced · 2021-08-26T17:14:43Z

Here it is.

IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'SalesLT')
	EXEC sp_executesql N'CREATE SCHEMA [SalesLT]';
GO

-- BEGIN TABLE dbo.BuildVersion
CREATE TABLE dbo.BuildVersion (
	SystemInformationID tinyint NOT NULL IDENTITY(1,1),
	[Database Version] nvarchar(25) NOT NULL,
	VersionDate datetime NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE dbo.BuildVersion ADD CONSTRAINT PK__BuildVer__35E58ECAB54F98BE PRIMARY KEY (SystemInformationID);
GO

-- END TABLE dbo.BuildVersion

-- BEGIN TABLE dbo.ErrorLog
CREATE TABLE dbo.ErrorLog (
	ErrorLogID int NOT NULL IDENTITY(1,1),
	ErrorTime datetime NOT NULL,
	UserName nvarchar(128) NOT NULL,
	ErrorNumber int NOT NULL,
	ErrorSeverity int NULL,
	ErrorState int NULL,
	ErrorProcedure nvarchar(126) NULL,
	ErrorLine int NULL,
	ErrorMessage nvarchar(4000) NOT NULL
);
GO

ALTER TABLE dbo.ErrorLog ADD CONSTRAINT PK_ErrorLog_ErrorLogID PRIMARY KEY (ErrorLogID);
GO

-- END TABLE dbo.ErrorLog

-- BEGIN TABLE dbo.towns
CREATE TABLE dbo.towns (
	id int NOT NULL IDENTITY(1,1),
	name varchar(40) NOT NULL,
	state varchar(3) NOT NULL,
	postcode char(4) NOT NULL
);
GO

ALTER TABLE dbo.towns ADD CONSTRAINT PK__towns__3213E83FA8B197B7 PRIMARY KEY (id);
GO

-- END TABLE dbo.towns

-- BEGIN TABLE SalesLT.Address
CREATE TABLE SalesLT.Address (
	AddressID int NOT NULL IDENTITY(1,1),
	AddressLine1 nvarchar(60) NOT NULL,
	AddressLine2 nvarchar(60) NULL,
	City nvarchar(30) NOT NULL,
	StateProvince nvarchar(50) NOT NULL,
	CountryRegion nvarchar(50) NOT NULL,
	PostalCode nvarchar(15) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.Address ADD CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID);
GO

-- END TABLE SalesLT.Address

-- BEGIN TABLE SalesLT.Customer
CREATE TABLE SalesLT.Customer (
	CustomerID int NOT NULL IDENTITY(1,1),
	NameStyle bit NOT NULL,
	Title nvarchar(8) NULL,
	FirstName nvarchar(50) NOT NULL,
	MiddleName nvarchar(50) NULL,
	LastName nvarchar(50) NOT NULL,
	Suffix nvarchar(10) NULL,
	CompanyName nvarchar(128) NULL,
	SalesPerson nvarchar(256) NULL,
	EmailAddress nvarchar(50) NULL,
	Phone nvarchar(25) NULL,
	PasswordHash varchar(128) NOT NULL,
	PasswordSalt varchar(10) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.Customer ADD CONSTRAINT PK_Customer_CustomerID PRIMARY KEY (CustomerID);
GO

-- END TABLE SalesLT.Customer

-- BEGIN TABLE SalesLT.CustomerAddress
CREATE TABLE SalesLT.CustomerAddress (
	CustomerID int NOT NULL,
	AddressID int NOT NULL,
	AddressType nvarchar(50) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.CustomerAddress ADD CONSTRAINT PK_CustomerAddress_CustomerID_AddressID PRIMARY KEY (CustomerID, AddressID);
GO

-- END TABLE SalesLT.CustomerAddress

-- BEGIN TABLE SalesLT.Product
CREATE TABLE SalesLT.Product (
	ProductID int NOT NULL IDENTITY(1,1),
	Name nvarchar(50) NOT NULL,
	ProductNumber nvarchar(25) NOT NULL,
	Color nvarchar(15) NULL,
	StandardCost money NOT NULL,
	ListPrice money NOT NULL,
	[Size] nvarchar(5) NULL,
	Weight decimal(8,2) NULL,
	ProductCategoryID int NULL,
	ProductModelID int NULL,
	SellStartDate datetime NOT NULL,
	SellEndDate datetime NULL,
	DiscontinuedDate datetime NULL,
	ThumbNailPhoto varbinary(max) NULL,
	ThumbnailPhotoFileName nvarchar(50) NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.Product ADD CONSTRAINT PK_Product_ProductID PRIMARY KEY (ProductID);
GO

-- END TABLE SalesLT.Product

-- BEGIN TABLE SalesLT.ProductCategory
CREATE TABLE SalesLT.ProductCategory (
	ProductCategoryID int NOT NULL IDENTITY(1,1),
	ParentProductCategoryID int NULL,
	Name nvarchar(50) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.ProductCategory ADD CONSTRAINT PK_ProductCategory_ProductCategoryID PRIMARY KEY (ProductCategoryID);
GO

-- END TABLE SalesLT.ProductCategory

-- BEGIN TABLE SalesLT.ProductDescription
CREATE TABLE SalesLT.ProductDescription (
	ProductDescriptionID int NOT NULL IDENTITY(1,1),
	Description nvarchar(400) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.ProductDescription ADD CONSTRAINT PK_ProductDescription_ProductDescriptionID PRIMARY KEY (ProductDescriptionID);
GO

-- END TABLE SalesLT.ProductDescription

-- BEGIN TABLE SalesLT.ProductModel
CREATE TABLE SalesLT.ProductModel (
	ProductModelID int NOT NULL IDENTITY(1,1),
	Name nvarchar(50) NOT NULL,
	CatalogDescription xml NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.ProductModel ADD CONSTRAINT PK_ProductModel_ProductModelID PRIMARY KEY (ProductModelID);
GO

-- END TABLE SalesLT.ProductModel

-- BEGIN TABLE SalesLT.ProductModelProductDescription
CREATE TABLE SalesLT.ProductModelProductDescription (
	ProductModelID int NOT NULL,
	ProductDescriptionID int NOT NULL,
	Culture nchar(6) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.ProductModelProductDescription ADD CONSTRAINT PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture PRIMARY KEY (ProductModelID, ProductDescriptionID, Culture);
GO

-- END TABLE SalesLT.ProductModelProductDescription

-- BEGIN TABLE SalesLT.SalesOrderDetail
CREATE TABLE SalesLT.SalesOrderDetail (
	SalesOrderID int NOT NULL,
	SalesOrderDetailID int NOT NULL IDENTITY(1,1),
	OrderQty smallint NOT NULL,
	ProductID int NOT NULL,
	UnitPrice money NOT NULL,
	UnitPriceDiscount money NOT NULL,
	LineTotal numeric(38,6) NOT NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.SalesOrderDetail ADD CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY (SalesOrderID, SalesOrderDetailID);
GO

-- END TABLE SalesLT.SalesOrderDetail

-- BEGIN TABLE SalesLT.SalesOrderHeader
CREATE TABLE SalesLT.SalesOrderHeader (
	SalesOrderID int NOT NULL,
	RevisionNumber tinyint NOT NULL,
	OrderDate datetime NOT NULL,
	DueDate datetime NOT NULL,
	ShipDate datetime NULL,
	Status tinyint NOT NULL,
	OnlineOrderFlag bit NOT NULL,
	SalesOrderNumber nvarchar(25) NOT NULL,
	PurchaseOrderNumber nvarchar(25) NULL,
	AccountNumber nvarchar(15) NULL,
	CustomerID int NOT NULL,
	ShipToAddressID int NULL,
	BillToAddressID int NULL,
	ShipMethod nvarchar(50) NOT NULL,
	CreditCardApprovalCode varchar(15) NULL,
	SubTotal money NOT NULL,
	TaxAmt money NOT NULL,
	Freight money NOT NULL,
	TotalDue money NOT NULL,
	Comment nvarchar(max) NULL,
	rowguid uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL
);
GO

ALTER TABLE SalesLT.SalesOrderHeader ADD CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY (SalesOrderID);
GO

-- END TABLE SalesLT.SalesOrderHeader

IF OBJECT_ID('SalesLT.vGetAllCategories', 'V') IS NOT NULL
	DROP VIEW SalesLT.vGetAllCategories;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('SalesLT.vProductAndDescription', 'V') IS NOT NULL
	DROP VIEW SalesLT.vProductAndDescription;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('SalesLT.vProductModelCatalogDescription', 'V') IS NOT NULL
	DROP VIEW SalesLT.vProductModelCatalogDescription;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.ufnGetAllCategories') AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
	DROP FUNCTION dbo.ufnGetAllCategories
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
<null>
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.ufnGetCustomerInformation') AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
	DROP FUNCTION dbo.ufnGetCustomerInformation
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
<null>
GO

IF OBJECT_ID('SalesLT.Customer', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.Customer', 'U') IS NOT NULL
	ALTER TABLE SalesLT.CustomerAddress
	ADD CONSTRAINT FK_CustomerAddress_Customer_CustomerID
	FOREIGN KEY (CustomerID)
	REFERENCES SalesLT.Customer (CustomerID);

IF OBJECT_ID('SalesLT.Address', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.Address', 'U') IS NOT NULL
	ALTER TABLE SalesLT.CustomerAddress
	ADD CONSTRAINT FK_CustomerAddress_Address_AddressID
	FOREIGN KEY (AddressID)
	REFERENCES SalesLT.Address (AddressID);

IF OBJECT_ID('SalesLT.ProductCategory', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.ProductCategory', 'U') IS NOT NULL
	ALTER TABLE SalesLT.Product
	ADD CONSTRAINT FK_Product_ProductCategory_ProductCategoryID
	FOREIGN KEY (ProductCategoryID)
	REFERENCES SalesLT.ProductCategory (ProductCategoryID);

IF OBJECT_ID('SalesLT.ProductModel', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.ProductModel', 'U') IS NOT NULL
	ALTER TABLE SalesLT.Product
	ADD CONSTRAINT FK_Product_ProductModel_ProductModelID
	FOREIGN KEY (ProductModelID)
	REFERENCES SalesLT.ProductModel (ProductModelID);

IF OBJECT_ID('SalesLT.ProductCategory', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.ProductCategory', 'U') IS NOT NULL
	ALTER TABLE SalesLT.ProductCategory
	ADD CONSTRAINT FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID
	FOREIGN KEY (ParentProductCategoryID)
	REFERENCES SalesLT.ProductCategory (ProductCategoryID);

IF OBJECT_ID('SalesLT.ProductDescription', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.ProductDescription', 'U') IS NOT NULL
	ALTER TABLE SalesLT.ProductModelProductDescription
	ADD CONSTRAINT FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID
	FOREIGN KEY (ProductDescriptionID)
	REFERENCES SalesLT.ProductDescription (ProductDescriptionID);

IF OBJECT_ID('SalesLT.ProductModel', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.ProductModel', 'U') IS NOT NULL
	ALTER TABLE SalesLT.ProductModelProductDescription
	ADD CONSTRAINT FK_ProductModelProductDescription_ProductModel_ProductModelID
	FOREIGN KEY (ProductModelID)
	REFERENCES SalesLT.ProductModel (ProductModelID);

IF OBJECT_ID('SalesLT.SalesOrderHeader', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.SalesOrderHeader', 'U') IS NOT NULL
	ALTER TABLE SalesLT.SalesOrderDetail
	ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
	FOREIGN KEY (SalesOrderID)
	REFERENCES SalesLT.SalesOrderHeader (SalesOrderID)
	ON DELETE Cascade;

IF OBJECT_ID('SalesLT.Product', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.Product', 'U') IS NOT NULL
	ALTER TABLE SalesLT.SalesOrderDetail
	ADD CONSTRAINT FK_SalesOrderDetail_Product_ProductID
	FOREIGN KEY (ProductID)
	REFERENCES SalesLT.Product (ProductID);

IF OBJECT_ID('SalesLT.Address', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.Address', 'U') IS NOT NULL
	ALTER TABLE SalesLT.SalesOrderHeader
	ADD CONSTRAINT FK_SalesOrderHeader_Address_BillTo_AddressID
	FOREIGN KEY (BillToAddressID)
	REFERENCES SalesLT.Address (AddressID);

IF OBJECT_ID('SalesLT.Address', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.Address', 'U') IS NOT NULL
	ALTER TABLE SalesLT.SalesOrderHeader
	ADD CONSTRAINT FK_SalesOrderHeader_Address_ShipTo_AddressID
	FOREIGN KEY (ShipToAddressID)
	REFERENCES SalesLT.Address (AddressID);

IF OBJECT_ID('SalesLT.Customer', 'U') IS NOT NULL AND OBJECT_ID('SalesLT.Customer', 'U') IS NOT NULL
	ALTER TABLE SalesLT.SalesOrderHeader
	ADD CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID
	FOREIGN KEY (CustomerID)
	REFERENCES SalesLT.Customer (CustomerID);

0xced · 2021-08-27T10:12:24Z

I figured out what's happening. The script I provided will be of no help, the issue is within the actual database.

Here is the slightly simplified query that is executed to retrieve the columns (and with a filter on the first problematic table with object_id = 1893581784):

SELECT
    SCHEMA_NAME([o].[schema_id]) AS [table_schema],
    [o].[name] AS [table_name],
    [c].[name] AS [column_name],
    [c].[column_id] AS [ordinal],
    SCHEMA_NAME([tp].[schema_id]) AS [type_schema],
    [tp].[name] AS [type_name],
    [c].[user_type_id] AS [type_id],
    CAST([c].[max_length] AS int) AS [max_length]
FROM
    (
        SELECT [t].[name], [t].[object_id], [t].[schema_id]
        FROM [sys].[tables] t WHERE [t].[is_ms_shipped] = 0 AND object_id = 1893581784
    ) o
JOIN [sys].[columns] AS [c] ON [o].[object_id] = [c].[object_id]
JOIN [sys].[types] AS [tp] ON [c].[user_type_id] = [tp].[user_type_id]
ORDER BY [table_schema], [table_name], [c].[column_id]

And here are the results when executing the query on the problematic database (notice ordinal 5 and 6 are missing).

table_schema	table_name	column_name	ordinal	type_schema	type_name	type_id	max_length
SalesLT	Address	AddressID	1	sys	int	56	4
SalesLT	Address	AddressLine1	2	sys	nvarchar	231	120
SalesLT	Address	AddressLine2	3	sys	nvarchar	231	120
SalesLT	Address	City	4	sys	nvarchar	231	60
SalesLT	Address	PostalCode	7	sys	nvarchar	231	30
SalesLT	Address	rowguid	8	sys	uniqueidentifier	36	16
SalesLT	Address	ModifiedDate	9	sys	datetime	61	8

Now, if we perform a LEFT JOIN instead of a JOIN on [sys].[types] we get two more columns that don't have type information!

table_schema	table_name	column_name	ordinal	type_schema	type_name	type_id	max_length
SalesLT	Address	AddressID	1	sys	int	56	4
SalesLT	Address	AddressLine1	2	sys	nvarchar	231	120
SalesLT	Address	AddressLine2	3	sys	nvarchar	231	120
SalesLT	Address	City	4	sys	nvarchar	231	60
SalesLT	Address	StateProvince	5	NULL	NULL	259	100
SalesLT	Address	CountryRegion	6	NULL	NULL	259	100
SalesLT	Address	PostalCode	7	sys	nvarchar	231	30
SalesLT	Address	rowguid	8	sys	uniqueidentifier	36	16
SalesLT	Address	ModifiedDate	9	sys	datetime	61	8

Those columns that don't have type information are the root cause of System.ArgumentNullException: Value cannot be null. (Parameter 'column') described in the original post.

I will submit a pull request that addresses this issue.

Before this commit, scaffolding could crash with an `ArgumentNullException`. ``` System.ArgumentNullException: Value cannot be null. (Parameter 'column') at Microsoft.EntityFrameworkCore.Utilities.Check.NotNull[T](T value, String parameterName) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000111+0x1a at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.GetPropertyName(DatabaseColumn column) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000221+0x0 at System.Linq.Enumerable.SelectListIterator`2.ToArray() in System.Linq.dll:token 0x60001dc+0x20 at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) in System.Linq.dll:token 0x600011a+0x1b at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600022d+0xa2 at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndexes(EntityTypeBuilder builder, ICollection`1 indexes) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600022c+0x28 at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000226+0xea at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000225+0x28 at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel) in Microsoft.EntityFrameworkCore.Design.dll:token 0x6000222+0x78 at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, ModelReverseEngineerOptions options) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600021e+0xf6 at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600023d+0xfe at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600043e+0x11c at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluarlize) in Microsoft.EntityFrameworkCore.Design.dll:token 0x60003ed+0x32 at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0() in Microsoft.EntityFrameworkCore.Design.dll:token 0x60006ed+0x0 at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0() in Microsoft.EntityFrameworkCore.Design.dll:token 0x60006f3+0x0 at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) in Microsoft.EntityFrameworkCore.Design.dll:token 0x600066a+0xc ``` After this commit, scaffolding produces warnings for columns with missing type information instead of crashing. ``` Could not find type mapping for column 'SalesLT.Address.StateProvince' with data type '???'. Skipping column. Could not find type mapping for column 'SalesLT.Address.CountryRegion' with data type '???'. Skipping column. Unable to scaffold the index 'IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion'. The following columns could not be scaffolded: StateProvince,CountryRegion. Unable to scaffold the index 'IX_Address_StateProvince'. The following columns could not be scaffolded: StateProvince. ``` Fixes dotnet#25729

The `dotnet-ef` and `ef` projects go through many hoops (`dotnet exec`) to run the scaffolder (`dotnet ef dbcontext scaffold`) This makes it almost impossible to set a breakpoint and debug the code. This tools has the minimum code required to run the scaffolder and is easily debuggable with Visual Studio or Rider. This tool was created so that I could investigate dotnet#25729

* Load columns even if they have no type, warn and skip. * Skip indexes/constraints which reference unknown columns. Fixes #25729

…25748) * Load columns even if they have no type, warn and skip. * Skip indexes/constraints which reference unknown columns. Fixes #25729

0xced added the customer-reported label Aug 26, 2021

0xced mentioned this issue Aug 27, 2021

Fix potential crash in SQL Server column retrieval code #25745

Closed

roji self-assigned this Aug 27, 2021

roji added a commit that referenced this issue Aug 27, 2021

Resiliency to columns with missing types in SQL Server scaffolding

1585e7f

* Load columns even if they have no type, warn and skip. * Skip indexes/constraints which reference unknown columns. Fixes #25729

roji mentioned this issue Aug 27, 2021

Resiliency to columns with missing types in SQL Server scaffolding #25748

Merged

roji added area-sqlserver area-scaffolding type-bug closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. labels Aug 27, 2021

roji changed the title ~~Scaffolding leads to System.ArgumentNullException: Value cannot be null. (Parameter 'column')~~ SQL Server scaffolding fails because of columns with no database type Aug 27, 2021

ajcvickers added this to the 6.0.0 milestone Aug 27, 2021

roji closed this as completed in #25748 Aug 28, 2021

roji added a commit that referenced this issue Aug 28, 2021

Resiliency to columns with missing types in SQL Server scaffolding (#…

667d016

…25748) * Load columns even if they have no type, warn and skip. * Skip indexes/constraints which reference unknown columns. Fixes #25729

ajcvickers modified the milestones: 6.0.0, 6.0.0-rc2 Sep 1, 2021

0xced mentioned this issue Oct 17, 2021

The dotnet ef dbcontext scaffold command does not print all expected logs #26384

Closed

ajcvickers modified the milestones: 6.0.0-rc2, 6.0.0 Nov 8, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL Server scaffolding fails because of columns with no database type #25729

SQL Server scaffolding fails because of columns with no database type #25729

0xced commented Aug 26, 2021

smitpatel commented Aug 26, 2021

0xced commented Aug 26, 2021

0xced commented Aug 27, 2021

SQL Server scaffolding fails because of columns with no database type #25729

SQL Server scaffolding fails because of columns with no database type #25729

Comments

0xced commented Aug 26, 2021

smitpatel commented Aug 26, 2021

0xced commented Aug 26, 2021

0xced commented Aug 27, 2021