Scaffolding broken for nvarchar(4000)

[mfields3]

When using SQL Server and a table has a column type of nvarchar(4000) (which is the maximum), the scaffolding outputs .HasColumnType("nvarchar"). This causes an exception when accessing the context for the model.

Believe it's a bug in the scaffolding code.

Exception message: System.ArgumentException: Data type 'nvarchar' is not supported in this form. Either specify the length explicitly in the type name, for example as 'nvarchar(16)', or remove the data type and use APIs such as HasMaxLength to allow EF choose the data type. 
Stack trace: at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerTypeMapper.ValidateTypeName(String storeType)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateDataTypes(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.<>c__DisplayClass5_0.<GetModel>b__0(Object k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()

Thrown on line 236 at https://github.com/aspnet/EntityFramework/blob/dev/src/EFCore.SqlServer/Storage/Internal/SqlServerTypeMapper.cs
This is because the HasColumnType is set to nvarchar which is in the _disallowedMappings HashSet.

Steps to reproduce

Create a new database containing a single table. Ensure there is a nvarchar(4000) column.
Scaffold the dbContext and create a controller/view and try and load the page.
The following should be scaffolded:

entity.Property(e => e.Notes)
                    .HasColumnName("notes")
                    .HasColumnType("nvarchar");

The issue appears to be GetStoreType in EFCore.SqlServer/Scaffolding/Internal/SqlServerDatabaseModelFactory.cs where it is not returning "nvarchar(max)" because the maxLength parameter isn't set to -1.

Further technical details

EF Core version: 2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: W10
IDE:Visual Studio 2017 Preview 2

[smitpatel]

nvarchar(4000) is different from nvarchar(max) (difference of 1).
SQL Server shows weird behavior for max_length for that specific type (or similar types almost at the max).

Type	max_length value
nvarchar(200)	400
nvarchar(4000)	NULL
nvarchar(max)	-1

We should report this to SQL Server team. Meanwhile we can handle this case specifically depending on timeline of former.

[mfields3]

That isn't an issue with SQL Server. Written in the scaffolding is some raw SQL which returns the data you've shared in the above table. There is a check to see if the size of the field is equal to maximum size of that type of field and if so, return NULL. Therefore perhaps that should be changed to return -1 if that's what the code expects?

[ErikEJ]

Look at: https://github.com/aspnet/EntityFramework/blob/dev/src/EFCore.SqlServer/Scaffolding/Internal/SqlServerDatabaseModelFactory.cs#L365

[mfields3]

^^ Exactly. Should that therefore be returning -1 instead of NULL?

[ErikEJ]

@mfields3 It should actually return 8000 for nvarchar(4000) !

https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql

[mfields3]

@ErikEJ It does return 8000, yet whoever wrote this CASE statement as part of scaffolding decided that when the length is equal to the maximum length of the field to return NULL.

Yet I believe as far as EF Core is concerned, the length of the string should be 4000, not 8000 nor MAX.
nvarchar(max) allows 2 GB. Yet the field is defined as nvarchar(4000) which uses 8000 bytes, yet has a max length of 4000.

Perhaps instead of sys.columns we use CHARACTER_MAXIMUM_LENGTH in INFORMATION_SCHEMA.COLUMNS (assuming support for SQL 2008 onwards is ok).

[smitpatel]

The case statement is incorrect. It should return 8000. There seem to be no usefuleness of nulling out when length is maximum. When its type(max) SqlServer will return -1 anyway. I will make the change when I will get time to fix the issue.

[ghost]

Scaffolding is also broken for varchar(8000) for I'm guessing is the same problem as above.

[ADefWebserver]

If you are stuck (as I was) you can use SQLite / SQL Server Compact Toolbox to scaffold your tables.

[JuanIrigoyen]

I have the same problem with fields with different lengths nvarchar(100), nvarchar(50) and the error is in runtime. When will be the new version that fixes this issue?

[ajcvickers]

@JuanIrigoyen The runtime isn't going to change. If you want to specify the store type abstractly, including the size, unicode, etc., then do so using the appropriate APIs/attributes (e.g. MaxLength, IsUnicode) and don't specify any explicit store type. If you want to specify an explicit store type, then do it fully, including the size.

[ajcvickers]

@Gaelmart That code looks right to me. Is it doing something unexpected?

[AxelAndersen]

I think it is unexpected that the autogenerated code fails like that, so yes!

When i create my models like this:
Scaffold-DbContext "Server=(local);Database=MyDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

this is what i get in one of the fields:

entity.Property(e => e.Text)
.IsRequired()
.HasColumnType("nvarchar");

and when running the code I get this error:
Data type 'nvarchar' is not supported in this form. Either specify the length explicitly in the type name, for example as 'nvarchar(16)', or remove the data type and use APIs such as HasMaxLength to allow EF choose the data type.

So there must be an error in the scaffolding.
This code and database worked before 2.0

[RCVarley]

Azure-hosted .Net Core 2.0 web app using Razor Pages:

I'm getting this same error when trying to build a Controller by scaffolding in EF Core 2.0. Oddly, the model that I'm referencing in the wizard was generated from a database as per this tutorial in MS Docs. The difference being that I'm using AdventureWorksLT, an example DB made available in Azure.

I'm wondering if it's the User Defined Data Types causing the issue. Specifically Name:nvarchar(50).

Output

Finding the generator 'controller'...
Running the generator 'controller'...
Attempting to compile the application in memory.
Attempting to figure out the EntityFramework metadata for the model and DbContext: 'Customer'
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using 'C:\Users\Ryan Varley\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
Data type 'nvarchar' is not supported in this form. Either specify the length explicitly in the type name, for example as 'nvarchar(16)', or remove the data type and use APIs such as HasMaxLength to allow EF choose the data type. StackTrace:
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerTypeMapper.ValidateTypeName(String storeType)
Data type 'nvarchar' is not supported in this form. Either specify the length explicitly in the type name, for example as 'nvarchar(16)', or remove the data type and use APIs such as HasMaxLength to allow EF choose the data type.   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateDataTypes(IModel model)

   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.<>c__DisplayClass5_0.<GetModel>b__0(Object k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_1(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass22_0.<RealizeService>b__0(ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
   at Microsoft.VisualStudio.Web.CodeGeneration.EntityFrameworkCore.EntityFrameworkServices.TryCreateContextUsingAppCode(Type dbContextType, Type startupType)
   at Microsoft.VisualStudio.Web.CodeGeneration.ActionInvoker.<BuildCommandLine>b__6_0()
   at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
   at Microsoft.VisualStudio.Web.CodeGeneration.ActionInvoker.Execute(String[] args)
   at Microsoft.VisualStudio.Web.CodeGeneration.CodeGenCommand.Execute(String[] args)

TableSQL

CREATE TABLE [SalesLT].[Product](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [dbo].[Name] 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,
 CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
 CONSTRAINT [AK_Product_Name] UNIQUE NONCLUSTERED 
(
	[Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
 CONSTRAINT [AK_Product_ProductNumber] UNIQUE NONCLUSTERED 
(
	[ProductNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
 CONSTRAINT [AK_Product_rowguid] UNIQUE NONCLUSTERED 
(
	[rowguid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

[jvelezc]

I am getting the same error but for varchar(MAX) and varchar(4000) and VARCHAR(8000).

[AxelAndersen]

Exactly what i experienced, there is for sure a bug in the scaffolding tool, or the EF Core

[SwapnilChandan]

I am also experiencing the same issue after updating application from 1.0 to core 2.0.

[RCVarley]

Sorry for the slow response. It turns out that my particular issue was a result of (hangs head in shame) a stray nvarchar value in my DbContext class, which has already been reported and addressed. Fortunately I can confirm that the Alias didn't screw anything up.

[ErikEJ]

@ajcvickers Is this being reconsidered for 2.0.1 ?

[MrBirdman]

Is there maybe a workaround for this?

[ErikEJ]

@MrBirdman I will consider a fix for EF Core Power Tools - does a replace of

 "nvarchar"

=>

 "nvarchar(4000)"

work for you?

[MrBirdman]

What about when we input more than 4000 characters?

[MrBirdman]

@ErikEJ yeah it might work for a while, i've used your tool to generate the context but it came out the same so i dropped it :)

[ErikEJ]

@MrBirdman Fixed in the latest daily release, pls try it out and let me know if it solves your problem, then I will publish the fix on VS Marketplace - https://github.com/ErikEJ/SqlCeToolbox/wiki/EF-Core-Power-Tools

[MrBirdman]

@ErikEJ I'm getting a "The resource you are looking for has been removed, had its name changed, or is temporarily unavailable." message when i try to download SQLite / SQL Server Compact Toolbox v4.7.384

[ErikEJ]

@MrBirdman This link works for me: http://vsixgallery.com/extensions/f4c4712c-ceae-4803-8e52-0e2049d5de9f/extension.vsix

[MrBirdman]

Actually i'm not sure this has done anything:
https://s1.postimg.org/1v7mlicdot/image.png

[ErikEJ]

Do not see anything relating to nvarchar on that screenshot. What is the runtime error you get?

[MrBirdman]

The left side is generated with EF, while the right side is with the toolbox. This table has several varchar(max) and nvarchar(max) columns, and it doesn't add or change anything to the model.

[ErikEJ]

@MrBirdman Do not use the Toolbox, use EF Core Power Tools (as per my wiki and direct link above)! Again, what runtime error do you get? (The Toolbox uses version 1.1 scaffolding)

[ajcvickers]

@ErikEJ We're going to discuss it, but no promises yet. Even if the team decides that it meets the bar, it will still need to go through patch approval before we will know for sure.

[ajcvickers]

Triage: we're going to try to get this fix into 2.0.1. See #9963.

[gate21]

I am getting 'varchar is not supported' when connecting to sql 2008 r2. I have tried all the suggestions above; any ideas.

[ErikEJ]

@gate21 replace "varchar" with "varchar(4000)" in the generated code... Or use EF Core Power Tools

[gate21]

I am using Code first. In my case I get the error on any varchar size.
I have tried "varchar(xxx)", HasMaxLength(xxx), HasColumnType("varchar").HasMaxLength(maxLength)...
I noticed when connecting to a Sql azure db for another project I do not have an issue, but when I connect to SQL 2008 R2 I get the issue.

[ErikEJ]

Have you tried : ?

HasColumnType("varchar(4000)")

[gate21]

yes I have tried HasColumnType("varchar(xxxx)").

I am using EFCore.sqlserver 2.0.1

[ajcvickers]

@gate21 Please file a new issue including a runnable project/solution or complete code listing that demonstrates the behavior you are seeing so that we can investigate.

[gate21]

Guys thanks for your help.

While trying to debug the issue I was having, I left the RegisterConfigurationFromAssembly code in place "this was picking up other configuration that I have not fixed". Once I saw that removed it and registered the congifurationObject I was debugging "varchar(xxx)" did fix the issue."

Regards