Efcore: System.InvalidCastException when using Scaffold-DbContext for MSSQL

Created on 17 Nov 2020 · 11Comments · Source: dotnet/efcore

Hey!

I tried to create a dbcontext from our MSSQL database (Windows Server 2016 Datacenter (10.0), Version 14.0.3192.2), but ran for a few tables into an InvalidCastException when running the Scaffold-DbContext command from the package-manger-console:

Scaffold-DbContext "database=<db>;server=<server>;Integrated Security=no;User ID=<userid>;pwd=<pwd>;Encrypt=no;Persist Security Info=False" Microsoft.EntityFrameworkCore.SqlServer -Tables <table> -OutputDir Data -Context EventContext -verbose

The problem occurred for 3 of my tables - however I tried some others that worked, so I assume it is not a general db problem or a wrong configuration or something (those are all "real" tables of our db that we use daily and currently address with plain ado.net, so it's not some kind of artificially for this prototype created or misconfigured test-tables if you know what I mean)

I ran the command in verbose mode, I hope this is enough information - if not please let me know how I can provide more information to solve the issue. As you may understand I had to change some sensitive information like connection-string, table- and column-names.

Stack traces

System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlDataReaderExtension.GetFieldValue[T](DbDataRecord record, String name)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.<>c.<GetForeignKeys>b__23_2(DbDataRecord c)
   at System.Linq.Lookup`2.Create(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
   at System.Linq.GroupedEnumerable`2.GetEnumerator()
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetForeignKeys(DbConnection connection, IReadOnlyList`1 tables, String tableFilter)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetTables(DbConnection connection, DatabaseModel databaseModel, Func`3 tableFilter, IReadOnlyDictionary`2 typeAliases)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   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)
   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)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

Verbose output

C:\Program Files\dotnet\dotnet.exe exec --depsfile C:\Users\<username>\Source\Repos\<username>\eventservice\bin\Debug\net5.0\EventService.deps.json --additionalprobingpath C:\Users\<username>\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig C:\Users\<username>\Source\Repos\<username>\eventservice\bin\Debug\net5.0\EventService.runtimeconfig.json C:\Users\<username>\.nuget\packages\microsoft.entityframeworkcore.tools\5.0.0\tools\netcoreapp2.0\any\ef.dll dbcontext scaffold "database=<db>;server=<server>;Integrated Security=no;User ID=<userid>;pwd=<pwd>;Encrypt=no;Persist Security Info=False" Microsoft.EntityFrameworkCore.SqlServer --json --output-dir Data --context EventContext --table <table> --verbose --no-color --prefix-output --assembly C:\Users\<username>\Source\Repos\<username>\eventservice\bin\Debug\net5.0\EventService.dll --startup-assembly C:\Users\<username>\Source\Repos\<username>\eventservice\bin\Debug\net5.0\EventService.dll --project-dir C:\Users\<username>\Source\Repos\<username>\eventservice\ --language C# --working-dir C:\Users\<username>\Source\Repos\<username>\eventservice --root-namespace EventService Using assembly 'EventService'. Using startup assembly 'EventService'. Using application base 'C:\Users\<username>\Source\Repos\<username>\eventservice\bin\Debug\net5.0'. Using working directory 'C:\Users\<username>\Source\Repos\<username>\eventservice'. Using root namespace 'EventService'. Using project directory 'C:\Users\<username>\Source\Repos\<username>\eventservice\'. Remaining arguments: . Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'... Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'. Finding design-time services referenced by assembly 'EventService'... Finding design-time services referenced by assembly 'EventService'... No referenced design-time services were found. Finding IDesignTimeServices implementations in assembly 'EventService'... No design-time services were found. To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263. Found default schema 'dbo'. Found type alias with name: sys.sysname which maps to underlying data type nvarchar(128). Found table with name: dbo.<table>. Found column with table: dbo.<table>, column name: Col1, ordinal: 1, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: True, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col2, ordinal: 2, data type: sys.timestamp, maximum length: 8, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col3, ordinal: 3, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col4, ordinal: 4, data type: sys.datetime, maximum length: 8, precision: 23, scale: 3, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col5, ordinal: 5, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col6, ordinal: 6, data type: sys.datetime, maximum length: 8, precision: 23, scale: 3, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col7, ordinal: 7, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col8, ordinal: 8, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col9, ordinal: 9, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col10, ordinal: 10, data type: sys.decimal, maximum length: 9, precision: 19, scale: 2, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col11, ordinal: 11, data type: sys.bit, maximum length: 1, precision: 1, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col12, ordinal: 12, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col13, ordinal: 13, data type: sys.bigint, maximum length: 8, precision: 19, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col14, ordinal: 14, data type: sys.bit, maximum length: 1, precision: 1, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col15, ordinal: 15, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col16, ordinal: 16, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col17, ordinal: 17, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col18, ordinal: 18, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col19, ordinal: 19, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found column with table: dbo.<table>, column name: Col20, ordinal: 20, data type: sys.bigint, maximum length: 8, precision: 19, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False Found primary key with name: XPK<table>, table: dbo.<table>. Found index with name: FIE1<table>, table: dbo.<table>, is unique: False. Found index with name: FUX1_<table>, table: dbo.<table>, is unique: True. Found index with name: XIE_<table>_OLTP1, table: dbo.<table>, is unique: False. System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'. at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlDataReaderExtension.GetFieldValue[T](DbDataRecord record, String name) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.<>c.<GetForeignKeys>b__23_2(DbDataRecord c) at System.Linq.Lookup`2.Create(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer) at System.Linq.GroupedEnumerable`2.GetEnumerator() at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetForeignKeys(DbConnection connection, IReadOnlyList`1 tables, String tableFilter) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetTables(DbConnection connection, DatabaseModel databaseModel, Func`3 tableFilter, IReadOnlyDictionary`2 typeAliases) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions) 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) 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) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) Unable to cast object of type 'System.DBNull' to type 'System.String'.

Provider and version information

EF Core version: 5.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10 1909
IDE: Visual Studio 2019 16.8.1

area-scaffolding customer-reported type-bug waiting-for-response

Source

deuko

Most helpful comment

I scaffolded database with above script and the context is generated successfully. So still no repro.
Looking at verbose log output. For SqlServer, we find all tables first then columns, keys, fks in that strict order for all objects.
My command line output is consistent with that.

Found default schema 'dbo'.
Found type alias with name: sys.sysname which maps to underlying data type nvarchar(128).
Found table with name: dbo.contextTable.
Found table with name: dbo.refTable1.
Found table with name: dbo.refTable2.
Found table with name: dbo.refTable3.
Found table with name: dbo.refTable4.
Found table with name: dbo.refTable5.
Found table with name: dbo.refTable6.
Found table with name: dbo.refTable7.
Found table with name: dbo.refTable8.
Found column with table: dbo.contextTable, column name: contextTablePkCol, ordinal: 1, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: True, default value: (null), computed value: (null), computed value is stored: False

Looking at verbose output in customer report, there is no data ever found for any table other than the contextTable. (The output goes through tables/columns/keys and throwing in FKs). Further, the potential cause of the error is that principal table value for FK constraint is null which we expect to be non-null. This seems like permission issue in the user scaffolding the database. @deuko - can you verify that the credentials used to connect to database has valid permissions on all the tables?

smitpatel on 17 Nov 2020

👍3

All 11 comments

Can you share the CREATE TABLE script, including any foreign key defintions

ErikEJ on 17 Nov 2020

@smitpatel Could this be another problem with constraint names? Can they be null in SQL Server?

ajcvickers on 17 Nov 2020

This is the complete CREATE TABLE script generated from ssms:

USE [<db>]
GO

/****** Object:  Table [dbo].[<table>]    Script Date: 17.11.2020 16:17:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[<table>](
    [Col1] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Col2] [timestamp] NOT NULL,
    [Col3] [int] NOT NULL,
    [Col4] [datetime] NOT NULL,
    [Col5] [int] NULL,
    [Col6] [datetime] NULL,
    [Col7] [int] NOT NULL,
    [Col8] [int] NOT NULL,
    [Col9] [int] NOT NULL,
    [Col10] [decimal](19, 2) NOT NULL,
    [Col11] [bit] NOT NULL,
    [Col12] [int] NOT NULL,
    [Col13] [bigint] NULL,
    [Col14] [bit] NOT NULL,
    [Col15] [int] NULL,
    [Col16] [int] NULL,
    [Col17] [int] NULL,
    [Col18] [int] NULL,
    [Col19] [int] NULL,
    [Col20] [bigint] NULL,
 CONSTRAINT [XPK<table>] PRIMARY KEY CLUSTERED 
(
    [Col1] 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].[<table>] ADD  CONSTRAINT [DF_<table>_Col11]  DEFAULT ((0)) FOR [Col11]
GO

ALTER TABLE [dbo].[<table>] ADD  CONSTRAINT [DF_<table>_Col12]  DEFAULT ((7)) FOR [Col12]
GO

ALTER TABLE [dbo].[<table>] ADD  CONSTRAINT [DF_<table>_Col14]  DEFAULT ((1)) FOR [Col14]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable1>_<table>_Col7] FOREIGN KEY([Col7])
REFERENCES [dbo].[<fktable1>] ([Col7])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable1>_<table>_Col7]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable2>_<table>_Col19] FOREIGN KEY([Col19])
REFERENCES [dbo].[<fktable2>] ([Col19])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable2>_<table>_Col19]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable3>_<table>_Col18] FOREIGN KEY([Col18])
REFERENCES [dbo].[<fktable3>] ([<fktable3pk>])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable3>_<table>_Col18]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable3>_<table>_Col17] FOREIGN KEY([Col17])
REFERENCES [dbo].[<fktable3>] ([<fktable3pk>])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable3>_<table>_Col17]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable4>_<table>_Col8] FOREIGN KEY([Col8])
REFERENCES [dbo].[<fktable4>] ([<fktable4pk>])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable4>_<table>_Col8]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable5>_<table>_Col3] FOREIGN KEY([Col3])
REFERENCES [dbo].[<fktable5>] ([<fktable5pk>])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable5>_<table>_Col3]
GO

ALTER TABLE [dbo].[<table>]  WITH CHECK ADD  CONSTRAINT [<fktable5>_<table>_Col5] FOREIGN KEY([Col5])
REFERENCES [dbo].[<fktable5>] ([<fktable5pk>])
GO

ALTER TABLE [dbo].[<table>] CHECK CONSTRAINT [<fktable5>_<table>_Col5]
GO

deuko on 17 Nov 2020

Potential cause of error is table name or principal table name being null in database but feels unlikely. I will try to repro this.

smitpatel on 17 Nov 2020

@deuko - I am unable to run that script in SSMS to create the database. The table contains FK constraints but definition for referenced tables are not present. Please provide a full repro code so we can investigate the error.

smitpatel on 17 Nov 2020

Sorry, this is now the full script to create all tables with its dependecies / foreign keys - i was able to reproduce the error after running this script:

USE [testdb]
GO

/****** Object:  Table [dbo].[refTable1]    Script Date: 17.11.2020 19:19:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable1](
    [refTable1PkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn4] [datetime] NULL,
    [refTable1Col1] [varchar](255) NOT NULL,
    [refTable1Col2] [varchar](255) NOT NULL,
    [refTable1Col3] [varchar](255) NULL,
    [refTable1Col4] [varchar](255) NOT NULL,
    [refTable1Col5] [bit] NOT NULL,
    [refTable1Col6] [varchar](255) NULL,
    [refTable1Col7] [varchar](255) NULL,
    [refTable1Col8] [varchar](255) NULL,
    [refTable1Col9] [varbinary](max) NULL,
    [refTable1Col10] [bit] NOT NULL,
    [refTable1Col11] [datetime] NULL,
    [refTable1Col12] [int] NOT NULL,
    [refTable1Col13] [datetime] NULL,
    [refTable1Col14] [datetime] NULL,
 CONSTRAINT [XPKrefTable1] PRIMARY KEY CLUSTERED 
(
    [refTable1PkCol] 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

ALTER TABLE [dbo].[refTable1] ADD  CONSTRAINT [DF_refTable1_refTable1Col5]  DEFAULT (0) FOR [refTable1Col5]
GO

ALTER TABLE [dbo].[refTable1] ADD  CONSTRAINT [DF_refTable1_refTable1Col10]  DEFAULT ((0)) FOR [refTable1Col10]
GO

ALTER TABLE [dbo].[refTable1] ADD  CONSTRAINT [DF_refTable1_refTable1Col12]  DEFAULT ((0)) FOR [refTable1Col12]
GO




USE [testdb]
GO

/****** Object:  Table [dbo].[refTable2]    Script Date: 17.11.2020 19:18:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable2](
    [refTable2PkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [metaColumn6] [nvarchar](255) NOT NULL,
 CONSTRAINT [XPKrefTable2] PRIMARY KEY CLUSTERED 
(
    [refTable2PkCol] 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].[refTable2]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable2_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable2] CHECK CONSTRAINT [refTable1_refTable2_metaColumn1]
GO

ALTER TABLE [dbo].[refTable2]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable2_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable2] CHECK CONSTRAINT [refTable1_refTable2_metaColumn3]
GO



USE [testdb]
GO

/****** Object:  Table [dbo].[refTable3]    Script Date: 17.11.2020 19:20:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable3](
    [refTable3PkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [metaColumn6] [nvarchar](255) NOT NULL,
 CONSTRAINT [XPKrefTable3] PRIMARY KEY CLUSTERED 
(
    [refTable3PkCol] 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].[refTable3]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable3_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable3] CHECK CONSTRAINT [refTable1_refTable3_metaColumn1]
GO

ALTER TABLE [dbo].[refTable3]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable3_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable3] CHECK CONSTRAINT [refTable1_refTable3_metaColumn3]
GO



USE [testdb]
GO

/****** Object:  Table [dbo].[refTable4]    Script Date: 17.11.2020 19:19:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable4](
    [pkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [Col1] [nvarchar](255) NOT NULL,
    [Col2] [nvarchar](255) NULL,
    [Col3] [nvarchar](255) NULL,
    [Col4] [nvarchar](255) NULL,
    [Col5] [nvarchar](255) NULL,
    [Col6] [nvarchar](255) NULL,
    [Col7] [nvarchar](255) NULL,
    [Col8] [nvarchar](255) NULL,
    [Col9] [nvarchar](255) NULL,
    [Col10] [nvarchar](255) NULL,
    [Col11] [nvarchar](255) NULL,
    [refTable2PkCol] [int] NOT NULL,
    [Col12] [int] NOT NULL,
    [Col13] [int] NOT NULL,
    [Col14] [int] NOT NULL,
    [Col15] [int] NOT NULL,
    [Col16] [int] NOT NULL,
    [Col17] [int] NOT NULL,
    [Col18] [int] NOT NULL,
    [Col19] [int] NOT NULL,
    [Col20] [int] NOT NULL,
    [Col21] [int] NOT NULL,
    [Col22] [int] NOT NULL,
    [Col23] [bit] NOT NULL,
 CONSTRAINT [XPKrefTable4] PRIMARY KEY CLUSTERED 
(
    [pkCol] 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].[refTable4] ADD  CONSTRAINT [DF_refTable4_refTable2PkCol]  DEFAULT ((1)) FOR [refTable2PkCol]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col12]  DEFAULT ((2)) FOR [Col12]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col13]  DEFAULT ((3)) FOR [Col13]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col14]  DEFAULT ((3)) FOR [Col14]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col15]  DEFAULT ((3)) FOR [Col15]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col16]  DEFAULT ((3)) FOR [Col16]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col17]  DEFAULT ((3)) FOR [Col17]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col18]  DEFAULT ((3)) FOR [Col18]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col19]  DEFAULT ((3)) FOR [Col19]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col20]  DEFAULT ((3)) FOR [Col20]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col21]  DEFAULT ((3)) FOR [Col21]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col22]  DEFAULT ((3)) FOR [Col22]
GO

ALTER TABLE [dbo].[refTable4] ADD  CONSTRAINT [DF_refTable4_Col23]  DEFAULT ((0)) FOR [Col23]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable2_refTable4_refTable2PkCol] FOREIGN KEY([refTable2PkCol])
REFERENCES [dbo].[refTable2] ([refTable2PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable2_refTable4_refTable2PkCol]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col12] FOREIGN KEY([Col12])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col12]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col13] FOREIGN KEY([Col13])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col13]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col19] FOREIGN KEY([Col19])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col19]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col17] FOREIGN KEY([Col17])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col17]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col14] FOREIGN KEY([Col14])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col14]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col15] FOREIGN KEY([Col15])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col15]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col22] FOREIGN KEY([Col22])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col22]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col18] FOREIGN KEY([Col18])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col18]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col16] FOREIGN KEY([Col16])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col16]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col20] FOREIGN KEY([Col20])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col20]
GO

ALTER TABLE [dbo].[refTable4]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable4_Col21] FOREIGN KEY([Col21])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable4] CHECK CONSTRAINT [refTable3_refTable4_Col21]
GO



USE [testdb]
GO

/****** Object:  Table [dbo].[refTable5]    Script Date: 17.11.2020 19:17:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable5](
    [refTable5PkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [Col1] [int] NOT NULL,
    [Col2] [bit] NOT NULL,
    [Col3] [decimal](19, 2) NOT NULL,
    [Col4] [int] NOT NULL,
    [Col5] [bigint] NULL,
    [Col6] [int] NOT NULL,
    [refTable2PkCol] [int] NOT NULL,
    [Col7] [bit] NOT NULL,
    [Col8] [bit] NOT NULL,
 CONSTRAINT [XPKrefTable5] PRIMARY KEY CLUSTERED 
(
    [refTable5PkCol] 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].[refTable5] ADD  CONSTRAINT [DF_refTable5_Col2]  DEFAULT (0) FOR [Col2]
GO

ALTER TABLE [dbo].[refTable5] ADD  CONSTRAINT [DF_refTable5_Col6]  DEFAULT ((7)) FOR [Col6]
GO

ALTER TABLE [dbo].[refTable5] ADD  CONSTRAINT [DF_refTable5_refTable2PkCol]  DEFAULT ((1)) FOR [refTable2PkCol]
GO

ALTER TABLE [dbo].[refTable5] ADD  CONSTRAINT [DF_refTable5_Col7]  DEFAULT ((1)) FOR [Col7]
GO

ALTER TABLE [dbo].[refTable5] ADD  CONSTRAINT [DF_refTable5_Col8]  DEFAULT ((0)) FOR [Col8]
GO

ALTER TABLE [dbo].[refTable5]  WITH CHECK ADD  CONSTRAINT [refTable2_refTable5_refTable2PkCol] FOREIGN KEY([refTable2PkCol])
REFERENCES [dbo].[refTable2] ([refTable2PkCol])
GO

ALTER TABLE [dbo].[refTable5] CHECK CONSTRAINT [refTable2_refTable5_refTable2PkCol]
GO

ALTER TABLE [dbo].[refTable5]  WITH CHECK ADD  CONSTRAINT [refTable4_refTable5_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[refTable4] ([pkCol])
GO

ALTER TABLE [dbo].[refTable5] CHECK CONSTRAINT [refTable4_refTable5_Col1]
GO

ALTER TABLE [dbo].[refTable5]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable5_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable5] CHECK CONSTRAINT [refTable1_refTable5_metaColumn1]
GO

ALTER TABLE [dbo].[refTable5]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable5_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable5] CHECK CONSTRAINT [refTable1_refTable5_metaColumn3]
GO





USE [testdb]
GO

/****** Object:  Table [dbo].[refTable6]    Script Date: 17.11.2020 19:21:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable6](
    [refTable6PkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [metaColumn6] [nvarchar](255) NOT NULL,
 CONSTRAINT [CPK_refTable6] PRIMARY KEY CLUSTERED 
(
    [refTable6PkCol] 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].[refTable6]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable6_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable6] CHECK CONSTRAINT [refTable1_refTable6_metaColumn1]
GO

ALTER TABLE [dbo].[refTable6]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable6_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable6] CHECK CONSTRAINT [refTable1_refTable6_metaColumn3]
GO



USE [testdb]
GO

/****** Object:  Table [dbo].[refTable7]    Script Date: 17.11.2020 19:21:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable7](
    [refTable7PkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [Col1] [varchar](255) NOT NULL,
    [Col2] [bit] NOT NULL,
    [Col3] [decimal](19, 2) NOT NULL,
    [Col4] [decimal](19, 2) NOT NULL,
    [Col5] [decimal](19, 2) NOT NULL,
 CONSTRAINT [XPKrefTable7] PRIMARY KEY CLUSTERED 
(
    [refTable7PkCol] 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].[refTable7] ADD  CONSTRAINT [DF_refTable7_Col2]  DEFAULT ((0)) FOR [Col2]
GO

ALTER TABLE [dbo].[refTable7]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable7_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable7] CHECK CONSTRAINT [refTable1_refTable7_metaColumn1]
GO

ALTER TABLE [dbo].[refTable7]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable7_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable7] CHECK CONSTRAINT [refTable1_refTable7_metaColumn3]
GO


USE [testdb]
GO

/****** Object:  Table [dbo].[refTable8]    Script Date: 17.11.2020 19:22:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refTable8](
    [pkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [Col1] [nvarchar](255) NOT NULL,
    [Col2] [nvarchar](255) NULL,
    [Col3] [nvarchar](255) NULL,
    [Col4] [nvarchar](255) NULL,
    [Col5] [nvarchar](255) NULL,
    [Col6] [nvarchar](255) NULL,
    [Col7] [nvarchar](255) NULL,
    [Col8] [nvarchar](255) NULL,
    [Col9] [nvarchar](255) NULL,
    [Col10] [nvarchar](255) NULL,
    [Col11] [nvarchar](255) NULL,
    [refTable2PkCol] [int] NOT NULL,
    [Col12] [int] NOT NULL,
    [Col13] [int] NOT NULL,
    [Col14] [int] NOT NULL,
    [Col15] [int] NOT NULL,
    [Col16] [int] NOT NULL,
    [Col17] [int] NOT NULL,
    [Col18] [int] NOT NULL,
    [Col19] [int] NOT NULL,
    [Col20] [int] NOT NULL,
    [Col21] [int] NOT NULL,
    [Col22] [int] NOT NULL,
    [Col23] [bit] NOT NULL,
 CONSTRAINT [XPKrefTable8] PRIMARY KEY CLUSTERED 
(
    [pkCol] 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].[refTable8] ADD  CONSTRAINT [DF_refTable8_refTable2PkCol]  DEFAULT ((1)) FOR [refTable2PkCol]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col12]  DEFAULT ((2)) FOR [Col12]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col13]  DEFAULT ((3)) FOR [Col13]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col14]  DEFAULT ((3)) FOR [Col14]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col15]  DEFAULT ((3)) FOR [Col15]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col16]  DEFAULT ((3)) FOR [Col16]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col17]  DEFAULT ((3)) FOR [Col17]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col18]  DEFAULT ((3)) FOR [Col18]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col19]  DEFAULT ((3)) FOR [Col19]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col20]  DEFAULT ((3)) FOR [Col20]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col21]  DEFAULT ((3)) FOR [Col21]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col22]  DEFAULT ((3)) FOR [Col22]
GO

ALTER TABLE [dbo].[refTable8] ADD  CONSTRAINT [DF_refTable8_Col23]  DEFAULT ((0)) FOR [Col23]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable2_refTable8_refTable2PkCol] FOREIGN KEY([refTable2PkCol])
REFERENCES [dbo].[refTable2] ([refTable2PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable2_refTable8_refTable2PkCol]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col12] FOREIGN KEY([Col12])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col12]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col13] FOREIGN KEY([Col13])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col13]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col19] FOREIGN KEY([Col19])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col19]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col17] FOREIGN KEY([Col17])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col17]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col14] FOREIGN KEY([Col14])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col14]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col15] FOREIGN KEY([Col15])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col15]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col22] FOREIGN KEY([Col22])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col22]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col18] FOREIGN KEY([Col18])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col18]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col16] FOREIGN KEY([Col16])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col16]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col20] FOREIGN KEY([Col20])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col20]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable3_refTable8_Col21] FOREIGN KEY([Col21])
REFERENCES [dbo].[refTable3] ([refTable3PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable3_refTable8_Col21]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable8_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable1_refTable8_metaColumn1]
GO

ALTER TABLE [dbo].[refTable8]  WITH CHECK ADD  CONSTRAINT [refTable1_refTable8_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[refTable8] CHECK CONSTRAINT [refTable1_refTable8_metaColumn3]
GO



USE [testdb]
GO

/****** Object:  Table [dbo].[contextTable]    Script Date: 17.11.2020 19:17:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[contextTable](
    [contextTablePkCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [metaColumn5] [timestamp] NOT NULL,
    [metaColumn1] [int] NOT NULL,
    [metaColumn2] [datetime] NOT NULL,
    [metaColumn3] [int] NULL,
    [metaColumn4] [datetime] NULL,
    [refTable5PkCol] [int] NOT NULL,
    [Col1] [int] NOT NULL,
    [Col4] [int] NOT NULL,
    [Col3] [decimal](19, 2) NOT NULL,
    [Col2] [bit] NOT NULL,
    [Col6] [int] NOT NULL,
    [Col8] [bigint] NULL,
    [Col7] [bit] NOT NULL,
    [Col9] [int] NULL,
    [Col10] [int] NULL,
    [refTable7PkCol] [int] NULL,
    [refTable7PkCol2] [int] NULL,
    [refTable6PkCol] [int] NULL,
    [Col11] [bigint] NULL,
 CONSTRAINT [XPKcontextTable] PRIMARY KEY CLUSTERED 
(
    [contextTablePkCol] 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].[contextTable] ADD  CONSTRAINT [DF_contextTable_Col2]  DEFAULT ((0)) FOR [Col2]
GO

ALTER TABLE [dbo].[contextTable] ADD  CONSTRAINT [DF_contextTable_Col6]  DEFAULT ((7)) FOR [Col6]
GO

ALTER TABLE [dbo].[contextTable] ADD  CONSTRAINT [DF_contextTable_Col7]  DEFAULT ((1)) FOR [Col7]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable5_contextTable_SportID] FOREIGN KEY([refTable5PkCol])
REFERENCES [dbo].[refTable5] ([refTable5PkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable5_contextTable_SportID]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable6_contextTable_refTable6PkCol] FOREIGN KEY([refTable6PkCol])
REFERENCES [dbo].[refTable6] ([refTable6PkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable6_contextTable_refTable6PkCol]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable7_contextTable_refTable7PkCol2] FOREIGN KEY([refTable7PkCol2])
REFERENCES [dbo].[refTable7] ([refTable7PkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable7_contextTable_refTable7PkCol2]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable7_contextTable_refTable7PkCol] FOREIGN KEY([refTable7PkCol])
REFERENCES [dbo].[refTable7] ([refTable7PkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable7_contextTable_refTable7PkCol]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable8_contextTable_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[refTable8] ([pkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable8_contextTable_Col1]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable1_contextTable_metaColumn1] FOREIGN KEY([metaColumn1])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable1_contextTable_metaColumn1]
GO

ALTER TABLE [dbo].[contextTable]  WITH CHECK ADD  CONSTRAINT [refTable1_contextTable_metaColumn3] FOREIGN KEY([metaColumn3])
REFERENCES [dbo].[refTable1] ([refTable1PkCol])
GO

ALTER TABLE [dbo].[contextTable] CHECK CONSTRAINT [refTable1_contextTable_metaColumn3]
GO

Then i tried to create the context for "contextTable":

Scaffold-DbContext "database=<db>;server=<server>;Integrated Security=no;User ID=<userid>;pwd=<pwd>;Encrypt=no;Persist Security Info=False" Microsoft.EntityFrameworkCore.SqlServer -Tables "contextTable" -OutputDir Data -Context EventContext -verbose

and again following error:

Found default schema 'dbo'.
Found type alias with name: sys.sysname which maps to underlying data type nvarchar(128).
Found table with name: dbo.contextTable.
Found column with table: dbo.contextTable, column name: contextTablePkCol, ordinal: 1, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: True, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: metaColumn5, ordinal: 2, data type: sys.timestamp, maximum length: 8, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: metaColumn1, ordinal: 3, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: metaColumn2, ordinal: 4, data type: sys.datetime, maximum length: 8, precision: 23, scale: 3, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: metaColumn3, ordinal: 5, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: metaColumn4, ordinal: 6, data type: sys.datetime, maximum length: 8, precision: 23, scale: 3, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: refTable5PkCol, ordinal: 7, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col1, ordinal: 8, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col4, ordinal: 9, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col3, ordinal: 10, data type: sys.decimal, maximum length: 9, precision: 19, scale: 2, nullable: False, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col2, ordinal: 11, data type: sys.bit, maximum length: 1, precision: 1, scale: 0, nullable: False, identity: False, default value: ((0)), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col6, ordinal: 12, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: False, default value: ((7)), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col8, ordinal: 13, data type: sys.bigint, maximum length: 8, precision: 19, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col7, ordinal: 14, data type: sys.bit, maximum length: 1, precision: 1, scale: 0, nullable: False, identity: False, default value: ((1)), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col9, ordinal: 15, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col10, ordinal: 16, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: refTable7PkCol, ordinal: 17, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: refTable7PkCol2, ordinal: 18, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: refTable6PkCol, ordinal: 19, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found column with table: dbo.contextTable, column name: Col11, ordinal: 20, data type: sys.bigint, maximum length: 8, precision: 19, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null), computed value is stored: False
Found primary key with name: XPKcontextTable, table: dbo.contextTable.
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlDataReaderExtension.GetFieldValue[T](DbDataRecord record, String name)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.<>c.<GetForeignKeys>b__23_2(DbDataRecord c)
   at System.Linq.Lookup`2.Create(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
   at System.Linq.GroupedEnumerable`2.GetEnumerator()
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetForeignKeys(DbConnection connection, IReadOnlyList`1 tables, String tableFilter)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetTables(DbConnection connection, DatabaseModel databaseModel, Func`3 tableFilter, IReadOnlyDictionary`2 typeAliases)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   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)
   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)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to cast object of type 'System.DBNull' to type 'System.String'.

I hope you can now also reproduce the error!

deuko on 17 Nov 2020

Found default schema 'dbo'.
Found type alias with name: sys.sysname which maps to underlying data type nvarchar(128).
Found table with name: dbo.contextTable.
Found table with name: dbo.refTable1.
Found table with name: dbo.refTable2.
Found table with name: dbo.refTable3.
Found table with name: dbo.refTable4.
Found table with name: dbo.refTable5.
Found table with name: dbo.refTable6.
Found table with name: dbo.refTable7.
Found table with name: dbo.refTable8.
Found column with table: dbo.contextTable, column name: contextTablePkCol, ordinal: 1, data type: sys.int, maximum length: 4, precision: 10, scale: 0, nullable: False, identity: True, default value: (null), computed value: (null), computed value is stored: False

smitpatel on 17 Nov 2020

👍3

@smitpatel Thank you very much, yes it was indeed a permission issue. After I set the "View definition" permission for all affected tables the scaffolding worked.

deuko on 18 Nov 2020

👍1

@smitpatel Related to https://github.com/dotnet/efcore/issues/22842#issuecomment-701168538 ?

ErikEJ on 18 Nov 2020

Yes, related to that. I re-opened this issue to read nullable from server and skip over FK constraint rather than throwing error. Then it will match to that issue. We only scaffold based on what snapshot we get from the server.

smitpatel on 18 Nov 2020

@dotnet/efteam - Testing for this would require changing permission of a table during the test? Do we want to go down that path or just make source code change and verify it outside of test and call it good?

smitpatel on 25 Nov 2020

Was this page helpful?

0 / 5 - 0 ratings