I get errors when performing reverse engineering of my database and do not know because it is produced

[JuanIrigoyen]

In new console project run nugget console:

Install-Package EntityFramework.MicrosoftSqlServer –Pre
Install-Package EntityFramework.Commands –Pre
Install-Package EntityFramework.MicrosoftSqlServer.Design –Pre

Finaly execute:

Scaffold-DbContext -provider EntityFramework.MicrosoftSqlServer -connection "Server=Sealand;Database=Maldivas_oran;Trusted_Connection=True;"

The first test show:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlBuffer.get_String()
at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at Microsoft.Data.Entity.Scaffolding.SqlServerDatabaseModelFactory.GetIndexes()
at Microsoft.Data.Entity.Scaffolding.SqlServerDatabaseModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
at Microsoft.Data.Entity.Scaffolding.RelationalScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
at Microsoft.Data.Entity.Scaffolding.SqlServerScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet)
at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GetMetadataModel(ReverseEngineeringConfiguration configuration)
at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GenerateAsync(ReverseEngineeringConfiguration configuration, CancellationToken cancellationToken)
at Microsoft.Data.Entity.Design.DatabaseOperations.ReverseEngineerAsync(String provider, String connectionString, String outputDir, String dbContextClassName, List1 schemas, List1 tables, Boolean useDataAnnotations, CancellationToken cancellationToken)
at Microsoft.Data.Entity.Design.OperationExecutor.d__19.MoveNext()
at System.Linq.Buffer1..ctor(IEnumerable1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source) at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.<>c__DisplayClass4_01.b__0()
at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.Execute(Action action)
Data is Null. This method or property cannot be called on Null values.

Some key columns in different tables has same name by example, the key name in table 'ivas' is 'codigo', the keyname in table 'albaranes' is 'codigo'

Another test with empty database result:

Unable to interpret the string 1 as a SQLServer string literal.
Could not find type mapping for column 'General.Incidencias.Configuracion' with data type 'xml'. Skipping column.
Unable to interpret the string 111 as a SQLServer string literal.
Unable to interpret the string 0 as a SQLServer string literal.
Unable to interpret the string 1 as a SQLServer string literal.
For column Financiera.Cartera_estados.Codigo. This column is set up as an Identity column, but the SQL Server data type is tinyint. This will be mapped to CLR type byte which does not allow the SqlServerValueGenerationStrategy.IdentityColumn setting. Generating a matching Property but ignoring the Identity setting.
Unable to interpret the string 0 as a SQLServer string literal.
Unable to interpret the string 0 as a SQLServer string literal.
Unable to interpret the string 0 as a SQLServer string literal.
Unable to interpret the string 0 as a SQLServer string literal.
Unable to interpret the string 0 as a SQLServer string literal.
Unable to interpret the string 0 as a SQLServer string literal.
Could not scaffold the foreign key 'Compras.Compras_albaranes_cabecera(Iva,Iva)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Ivas'.
Could not scaffold the foreign key 'Compras.Compras_facturas_detalle(Codigo,Codigo)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Compras_facturas_cabecera'.
Could not scaffold the foreign key 'Ventas.Ventas_albaranes_cabecera(Forma_pago,Forma_pago)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Formas_pago'.
Could not scaffold the foreign key 'Compras.Compras_pedidos_detalle(Almacen,Almacen)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Almacen'.
Could not scaffold the foreign key 'Compras.Compras_proformas_detalle(Articulo,Articulo)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Articulos'.
Could not scaffold the foreign key 'Compras.Compras_proformas_detalle(Codigo,Codigo)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Compras_proformas_cabecera'.
Could not scaffold the foreign key 'Ventas.Ventas_pedidos_detalle(Iva,Iva)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Ivas'.

[lajones]

@micball Thanks for reporting this. The initial SqlNullException is a bug - we should fix that. Could you share your database schema including table and index definitions and any statements creating the primary, alternate and foreign keys (if separate) to help us track down what is causing it? Also could you include the version of SQL Server you're working with?

The other messages are warnings rather than errors.

The ones of the form Unable to interpret the string 0 as a SQLServer string literal are expecting the string to look like 'your_string_here' (or N'your_string_here' for Unicode strings). Usually this would happen when there are DEFAULT VALUE statements and the scaffolding code is trying to interpret the default value so as to add it to the generated code. You will still get a matching property in the generated code but it will not be assigned a default value. It sounds like, when the columns were created, either someone meant to e.g. assign the default value '0' (string) but by mistake assigned the default value 0 (integer). Or they defined a column as a varchar/char(n)/... when they meant to define it as an integer column.

The ones that start with Could not scaffold the foreign key are because it cannot find a key on the principal end to which to map that end of the foreign key. The database schema mentioned above would help tracking down the problem with that too.

[JuanIrigoyen]

Thank you for your help, my sql versión is:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Attach scripts for replicate database, they must be executed in the order.

[JuanIrigoyen]

1 - Maldivas Test - Create Schemas and Tables.txt
2 - Maldivas Test - DT and Functions.txt
3 - Maldivas Test - Create Views.txt
4 - Maldivas Test - Store Procedures.txt
5 - Maldivas Test - Create Triggers.txt

[lajones]

@micball. I am unable to apply scripts 2 onwards because they reference schema [Maldivas_base] which has not been defined. And I was unable to reproduce the SqlNullValueException - but I haven't tried with your exact version of SQL Server yet.

However even with just script 1 I could analyze some of the issue:
The analysis above about warnings of the form Unable to interpret the string 0 as a SQLServer string literal was correct. Your script 1 has lines of the form:

ALTER TABLE [Logistica].[Articulos_lotes] ADD CONSTRAINT [DF_Articulos_lotes_Almacen] DEFAULT ((111)) FOR [Almacen]

In this case [Almacen] is defined as an nchar(3). So this should read:

ALTER TABLE [Logistica].[Articulos_lotes] ADD CONSTRAINT [DF_Articulos_lotes_Almacen] DEFAULT '111' FOR [Almacen]

Parentheses are ignored but the quotes are important for string-like columns. Unfortunately SQL Server does not stop you putting incorrect values in the default at definition-time. You only find out when you attempt to use the default (or in our case read metadata about the default).

The warnings of the form Could not scaffold the foreign key 'Compras.Compras_facturas_detalle(Codigo,Codigo)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Compras_facturas_cabecera'. are a bug on our side. You define different foreign keys which have the same name but belong to different tables in different schemas. We were not including the name of the table/schema in the identity of the FK so e.g. for the above we were treating the 2 FK's [Compras].[FK_Facturas_detalle_Facturas_cabecera] and [Ventas].[FK_Facturas_detalle_Facturas_cabecera] each on a single column Codigo as a single FK on 2 columns Codigo,Codigo. I'll send out a fix for that.

[lajones]

As a faster way of diagnosing the SqlNullValueException could you run the following query on your database please and post me the results?

SELECT 
    i.name AS [index_name],
    object_schema_name(i.object_id) AS [schema_name],
    object_name(i.object_id) AS [table_name],
    i.is_unique,
    c.name AS [column_name],
    i.type_desc
FROM sys.indexes i
    inner join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE   object_schema_name(i.object_id) <> 'sys' 
    AND i.is_primary_key <> 1
ORDER BY i.name, ic.key_ordinal

[JuanIrigoyen]

Hi lajones, thank you very much for your help, I have corrected the names duplicates of the foreign keys and I have reviewed all my defaults used the following query:

SELECT TOP 100 PERCENT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, RTRIM(SCHEMA_NAME(t.schema_id)) + '.' + RTRIM(t.name) AS FullName,
c.name AS ColumnName, tp.name as DataType, object_definition(c.default_object_id) AS ValueColumn
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.objects o ON o.object_id = c.object_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE object_definition(c.default_object_id) IS NOT NULL
AND NOT (object_definition(c.default_object_id) = '((0))') ORDER BY TableName

Finally I created a new project and model and receive the next error:

Scaffold-DbContext -provider EntityFramework.MicrosoftSqlServer -connection "Server=Sealand;Database=Maldivas_test;Trusted_Connection=True;"
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.Collections.Generic.Dictionary2.get_Item(TKey key) at Microsoft.Data.Entity.Scaffolding.SqlServerDatabaseModelFactory.GetIndexes() at Microsoft.Data.Entity.Scaffolding.SqlServerDatabaseModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet) at Microsoft.Data.Entity.Scaffolding.RelationalScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet) at Microsoft.Data.Entity.Scaffolding.SqlServerScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet) at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GetMetadataModel(ReverseEngineeringConfiguration configuration) at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GenerateAsync(ReverseEngineeringConfiguration configuration, CancellationToken cancellationToken) at Microsoft.Data.Entity.Design.DatabaseOperations.ReverseEngineerAsync(String provider, String connectionString, String outputDir, String dbContextClassName, List1 schemas, List1 tables, Boolean useDataAnnotations, CancellationToken cancellationToken) at Microsoft.Data.Entity.Design.OperationExecutor.<ReverseEngineerImpl>d__19.MoveNext() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source)
at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.<>c__DisplayClass4_0`1.b__0()
at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.Execute(Action action)
The given key was not present in the dictionary.

Attach update file.
1 - Maldivas Test - Create Schemas and Tables.txt

[lajones]

@micball The second error indicates that our code is trying to create an index on a table that it doesn't know about. I could do with the results of the query I mentioned before - that's the query we run to look for the index columns. Plus, rather than changing your schema, I would like to try to figure out what is happening - so I can harden our code against database metadata which is incorrect and issue more helpful error messages.

Also re your query for default values - the line AND NOT (object_definition(c.default_object_id) = '((0))') may exclude some values which are wrong. Assigning a default value of 0 is not the same as assigning a default value of '0' - and which one is correct depends on the data type of the column you're assigning the default value to.

[JuanIrigoyen]

@lajones, when execute the query of (SqlNullValueException) I see the next line

index_name schema_name table_name is_unique column_name type_desc
NULL Picking Ventas_albaranes_carga 0 Fecha_entrada HEAP

I understand that this is because the table has a defined partition on the field ( fecha_entrada ] )

Note: The partitions is only avaible in enterprise versions of Sql Server

However, when the script is generated i dont see the code of the partition, only the last line show
ON PickingVentasAlbaranesCargaSchema([Fecha_entrada])

Attach query result of indexes
Database Index.txt

Table with partition.txt

[JuanIrigoyen]

I think the main problem is that the generator does not display the information with the name of the tables, fields, indexes or relationships that produce this errors.

The query of the default zero value is ​​omitted because I use to configure my system metadata in own entities.

[JuanIrigoyen]

I run the next querys for incorrect default values in numeric fields

SELECT TOP 100 PERCENT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, RTRIM(SCHEMA_NAME(t.schema_id)) + '.' + RTRIM(t.name) AS FullName,
c.name AS ColumnName, tp.name as DataType, object_definition(c.default_object_id) AS ValueColumn
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.objects o ON o.object_id = c.object_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE object_definition(c.default_object_id) IS NOT NULL
and object_definition(c.default_object_id) = '((0))' AND tp.name not in ('smallint', 'money', 'decimal', 'int', 'bit', 'tinyint', 'smallmoney', 'numeric', 'float', 'bigint','real')

SELECT TOP 100 PERCENT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, RTRIM(SCHEMA_NAME(t.schema_id)) + '.' + RTRIM(t.name) AS FullName,
c.name AS ColumnName, tp.name as DataType, object_definition(c.default_object_id) AS ValueColumn
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.objects o ON o.object_id = c.object_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE object_definition(c.default_object_id) IS NOT NULL
and object_definition(c.default_object_id) LIKE '%((%' AND tp.name not in ('smallint', 'money', 'decimal', 'int', 'bit', 'tinyint', 'smallmoney', 'numeric', 'float', 'bigint','real')

[JuanIrigoyen]

This errors how 'Could not scaffold the foreign key 'Compras.Compras_albaranes_cabecera(Iva,Iva)'. A key for 'Codigo,Codigo' was not found in the principal entity type 'Ivas'. These errors occur because there are the same names in different tables and are resolved by renaming these to unique names.

[JuanIrigoyen]

I send the updated script after deleting the partition, rename the names of the relations and update erroneous default values.

1 - Maldivas Test - Create Schemas Tables and Index.txt

Attach query with diagnosing the SqlNullValueException
Database Index.txt

When execute again with the update model...
Scaffold-DbContext -provider EntityFramework.MicrosoftSqlServer -connection "Server=Sealand;Database=Maldivas_test;Trusted_Connection=True;"

Console return:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.Collections.Generic.Dictionary2.get_Item(TKey key) at Microsoft.Data.Entity.Scaffolding.SqlServerDatabaseModelFactory.GetIndexes() at Microsoft.Data.Entity.Scaffolding.SqlServerDatabaseModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet) at Microsoft.Data.Entity.Scaffolding.RelationalScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet) at Microsoft.Data.Entity.Scaffolding.SqlServerScaffoldingModelFactory.Create(String connectionString, TableSelectionSet tableSelectionSet) at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GetMetadataModel(ReverseEngineeringConfiguration configuration) at Microsoft.Data.Entity.Scaffolding.Internal.ReverseEngineeringGenerator.GenerateAsync(ReverseEngineeringConfiguration configuration, CancellationToken cancellationToken) at Microsoft.Data.Entity.Design.DatabaseOperations.ReverseEngineerAsync(String provider, String connectionString, String outputDir, String dbContextClassName, List1 schemas, List1 tables, Boolean useDataAnnotations, CancellationToken cancellationToken) at Microsoft.Data.Entity.Design.OperationExecutor.<ReverseEngineerImpl>d__19.MoveNext() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source)
at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.<>c__DisplayClass4_0`1.b__0()
at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.Execute(Action action)
The given key was not present in the dictionary.

[lajones]

@micball I was unable to repro the KeyNotFoundException but it could only arise from where an index is expecting to find a table that is not present (or similar situations involving columns). So I've updated the code to guard against these situations and issue warning messages instead.

[lajones]

Replaced PR #3933 with #3934.

[lajones]

PR #3934 checked in with commit 454968a. As I mentioned I couldn't repro it, but I've put in guard code which should guard against all of the situations you were seeing above plus fix the issue with confusing the 2 indexes with the same name but different schemas. This should be available in tonight's nightly build.

[JuanIrigoyen]

Lagones thank you very much for your work, I would like to add two interesting points that you could use to improve EF , one is referring to the name of the tables and entities and this has caused me some problems, it may be the case according to the security scheme you can find two tables with the same name, index and relations, for example :

Table 1 schema buy,
buy.orders

Table 2 Schema sell
sell.orders

Both tables have the same name, same name even indices in relationships and certainly create some error.

I understand that to solve this the name of the entities, index and relationships should be Schema Name + Table Name + Index Name or Relation Name, etc.

Another detected error that we have not talked about is the reference to the XML field type is
Unable to interpret the string 1 as a literal string SQLServer. Could not find type mapping for column … with data type 'xml' . Skipping column. In my model i treat entities as a string .

Regarding duplicate indexes you can use the following statement

;WITH MyDuplicate AS (SELECT
Sch.[name] AS SchemaName,
Obj.[name] AS TableName,
Idx.[name] AS IndexName,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
FROM sys.indexes Idx
INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id]
INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]
WHERE index_id > 0)
SELECT MD1.SchemaName, MD1.TableName, MD1.IndexName,
MD2.IndexName AS OverLappingIndex,
MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
FROM MyDuplicate MD1
INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
AND MD1.indexname <> MD2.indexname
AND MD1.Col1 = MD2.Col1
AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
ORDER BY MD1.SchemaName,MD1.TableName,MD1.IndexName

As you can see, I'll have to rename a few ... attach image.

[lajones]

@micball Re the xml data type - that issue is already tracked in issue #3076.

Re naming entity types to include their schema - this has been discussed in the past (as was the idea of converting the schema into the C# namespace) and we decided not to do it. Instead we decided just to name the first entity type e.g. MyTable and further ones differing only by schema will be named MyTable1, MyTable2, ...

Other duplicates should now just work. E.g. having 2 indexes which only differ by schema should now work with no renaming required on your end. It was just a bug in our code that wasn't distinguishing them.

[lajones]

Also best response for the warnings of the form:

"For column Financiera.Cartera_estados.Codigo. This column is set up as an Identity column, but the SQL Server data type is tinyint. This will be mapped to CLR type byte which does not allow the SqlServerValueGenerationStrategy.IdentityColumn setting. Generating a matching Property but ignoring the Identity setting."

is to change the data type in the schema to something a bit bigger e.g. smallint or, even better, int. The reason is that while generating identities on a tinyint column is valid to do in SQL Server, in fact you could only generate 256 rows before you ran out of identities that wouldn't clash.