/// <summary> /// Converts a string of the form 'There''s a double single quote in here' /// or, for unicode strings, N'There''s a double single quote in here' /// (including the optional N and the outer single quotes) to the string literal /// "There's a double single quote in here" (not including the double quotes). /// </summary> /// <param name="sqlServerStringLiteral"> the string to convert </param> /// <returns> the converted string, or null if it cannot convert </returns> public virtual string ConvertSqlServerStringLiteral([NotNull] string sqlServerStringLiteral) { Check.NotEmpty(sqlServerStringLiteral, nameof(sqlServerStringLiteral)); if (sqlServerStringLiteral[0] == 'N') { sqlServerStringLiteral = sqlServerStringLiteral.Substring(1); } var sqlServerStringLiteralLength = sqlServerStringLiteral.Length; if (sqlServerStringLiteralLength < 2) { Logger.LogWarning( SqlServerDesignStrings.CannotInterpretSqlServerStringLiteral(sqlServerStringLiteral)); return(null); } if (sqlServerStringLiteral[0] != '\'' || sqlServerStringLiteral[sqlServerStringLiteralLength - 1] != '\'') { Logger.LogWarning( SqlServerDesignStrings.CannotInterpretSqlServerStringLiteral(sqlServerStringLiteral)); return(null); } return(sqlServerStringLiteral.Substring(1, sqlServerStringLiteralLength - 2) .Replace("''", "'")); }
private PropertyBuilder VisitDefaultValue(ColumnModel column, PropertyBuilder propertyBuilder) { if (column.DefaultValue != null) { // unset default propertyBuilder.Metadata.ValueGenerated = null; propertyBuilder.Metadata.Relational().GeneratedValueSql = null; var property = propertyBuilder.Metadata; var defaultExpressionOrValue = _sqlServerLiteralUtilities .ConvertSqlServerDefaultValue( property.ClrType, column.DefaultValue); if (defaultExpressionOrValue?.DefaultExpression != null) { propertyBuilder.HasDefaultValueSql(defaultExpressionOrValue.DefaultExpression); } else if (defaultExpressionOrValue != null) { // Note: defaultExpressionOrValue.DefaultValue == null is valid propertyBuilder.HasDefaultValue(defaultExpressionOrValue.DefaultValue); } else { Logger.LogWarning( SqlServerDesignStrings.UnableToConvertDefaultValue( column.DisplayName, column.DefaultValue, property.ClrType, property.Name, property.DeclaringEntityType.Name)); } } return(propertyBuilder); }
private PropertyBuilder VisitDefaultValue(ColumnModel column, PropertyBuilder propertyBuilder) { if (column.DefaultValue != null) { ((Property)propertyBuilder.Metadata).SetValueGenerated(null, ConfigurationSource.Explicit); propertyBuilder.Metadata.Relational().GeneratedValueSql = null; var defaultExpression = ConvertSqlServerDefaultValue(column.DefaultValue); if (defaultExpression != null) { if (!(defaultExpression == "NULL" && propertyBuilder.Metadata.ClrType.IsNullableType())) { propertyBuilder.HasDefaultValueSql(defaultExpression); } } else { Logger.LogWarning( SqlServerDesignStrings.CannotInterpretDefaultValue( column.DisplayName, column.DefaultValue, propertyBuilder.Metadata.Name, propertyBuilder.Metadata.DeclaringEntityType.Name)); } } return(propertyBuilder); }
private void GetTables() { var command = _connection.CreateCommand(); command.CommandText = "SELECT schema_name(t.schema_id) AS [schema], t.name FROM sys.tables AS t " + $"WHERE t.name <> '{HistoryRepository.DefaultTableName}'" + TemporalTableWhereClause; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var table = new TableModel { Database = _databaseModel, SchemaName = reader.GetValueOrDefault <string>("schema"), Name = reader.GetValueOrDefault <string>("name") }; Logger.LogTrace(SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name)); if (_tableSelectionSet.Allows(table.SchemaName, table.Name)) { _databaseModel.Tables.Add(table); _tables[TableKey(table)] = table; } else { Logger.LogTrace( SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name)); } } } }
private PropertyBuilder VisitTypeMapping(PropertyBuilder propertyBuilder, ColumnModel column) { if (column.IsIdentity == true) { if (typeof(byte) == propertyBuilder.Metadata.ClrType) { Logger.LogWarning( SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy( column.DisplayName, column.DataType)); } else { propertyBuilder .ValueGeneratedOnAdd() .UseSqlServerIdentityColumn(); } } if (_dateTimePrecisionTypes.Contains(column.DataType) && column.Scale.HasValue && column.Scale != DefaultDateTimePrecision) { propertyBuilder.Metadata.SetMaxLength(null); propertyBuilder.HasColumnType($"{column.DataType}({column.Scale})"); //not a typo: .Scale is the right property for datetime precision } // undo quirk in reverse type mapping to litters code with unnecessary nvarchar annotations if (typeof(string) == propertyBuilder.Metadata.ClrType && propertyBuilder.Metadata.Relational().ColumnType == "nvarchar") { propertyBuilder.Metadata.Relational().ColumnType = null; } return(propertyBuilder); }
private void VisitComputedValue(PropertyBuilder propertyBuilder, ColumnModel column) { if (column.ComputedValue != null) { propertyBuilder.Metadata.Relational().ComputedColumnSql = null; var computedExpression = ConvertSqlServerDefaultValue(column.ComputedValue); if (computedExpression != null) { if (!(computedExpression == "NULL" && propertyBuilder.Metadata.ClrType.IsNullableType())) { propertyBuilder.HasComputedColumnSql(computedExpression); } else { ((Property)propertyBuilder.Metadata).SetValueGenerated(null, ConfigurationSource.Explicit); } } else { Logger.LogWarning( SqlServerDesignEventId.CannotInterpretComputedValueWarning, () => SqlServerDesignStrings.CannotInterpretComputedValue( column.DisplayName, column.ComputedValue, propertyBuilder.Metadata.Name, propertyBuilder.Metadata.DeclaringEntityType.DisplayName())); } } }
private ColumnModel FindColumnForForeignKey( string columnName, TableModel table, string fkName) { ColumnModel column; if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning( SqlServerDesignEventId.ColumnNameEmptyOnForeignKey, () => SqlServerDesignStrings.ColumnNameEmptyOnForeignKey( table.SchemaName, table.Name, fkName)); return(null); } if (!_tableColumns.TryGetValue( ColumnKey(table, columnName), out column)) { Logger.LogWarning( RelationalDesignEventId.ForeignKeyColumnsNotMappedWarning, () => SqlServerDesignStrings.UnableToFindColumnForForeignKey( fkName, columnName, table.SchemaName, table.Name)); return(null); } return(column); }
private void GetSequences() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT name, is_cycling, CAST(minimum_value AS bigint) as [minimum_value], CAST(maximum_value AS bigint) as [maximum_value], CAST(start_value AS bigint) as [start_value], CAST(increment AS int) as [increment], TYPE_NAME(user_type_id) as [type_name], OBJECT_SCHEMA_NAME(object_id) AS [schema_name] FROM sys.sequences"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var sequence = new SequenceModel { Database = _databaseModel, SchemaName = reader.GetValueOrDefault <string>("schema_name"), Name = reader.GetValueOrDefault <string>("name"), DataType = reader.GetValueOrDefault <string>("type_name"), IsCyclic = reader.GetValueOrDefault <bool?>("is_cycling"), IncrementBy = reader.GetValueOrDefault <int?>("increment"), Start = reader.GetValueOrDefault <long?>("start_value"), Min = reader.GetValueOrDefault <long?>("minimum_value"), Max = reader.GetValueOrDefault <long?>("maximum_value") }; Logger.LogDebug( RelationalDesignEventId.FoundSequence, () => SqlServerDesignStrings.FoundSequence( sequence.SchemaName, sequence.Name, sequence.DataType, sequence.IsCyclic, sequence.IncrementBy, sequence.Start, sequence.Min, sequence.Max)); if (string.IsNullOrEmpty(sequence.Name)) { Logger.LogWarning( RelationalDesignEventId.SequenceMustBeNamedWarning, () => RelationalDesignStrings.SequencesRequireName); continue; } if (_defaultSequenceMinMax.ContainsKey(sequence.DataType)) { var defaultMin = _defaultSequenceMinMax[sequence.DataType][0]; sequence.Min = sequence.Min == defaultMin ? null : sequence.Min; sequence.Start = sequence.Start == defaultMin ? null : sequence.Start; var defaultMax = _defaultSequenceMinMax[sequence.DataType][1]; sequence.Max = sequence.Max == defaultMax ? null : sequence.Max; } _databaseModel.Sequences.Add(sequence); } } }
private void GetSequences() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT name, is_cycling, CAST(minimum_value AS bigint) as [minimum_value], CAST(maximum_value AS bigint) as [maximum_value], CAST(start_value AS bigint) as [start_value], CAST(increment AS int) as [increment], TYPE_NAME(user_type_id) as [type_name], OBJECT_SCHEMA_NAME(object_id) AS [schema_name] FROM sys.sequences"; using (var reader = command.ExecuteReader()) { var dboIdx = reader.GetOrdinal("schema_name"); var typeIdx = reader.GetOrdinal("type_name"); var nameIdx = reader.GetOrdinal("name"); var cycleIdx = reader.GetOrdinal("is_cycling"); var minIdx = reader.GetOrdinal("minimum_value"); var maxIdx = reader.GetOrdinal("maximum_value"); var startIdx = reader.GetOrdinal("start_value"); var incrIdx = reader.GetOrdinal("increment"); while (reader.Read()) { var sequence = new SequenceModel { SchemaName = reader.GetStringOrNull(dboIdx), Name = reader.GetStringOrNull(nameIdx), DataType = reader.GetStringOrNull(typeIdx), IsCyclic = reader.GetBoolean(cycleIdx), IncrementBy = reader.GetInt32(incrIdx), Start = reader.GetInt64(startIdx), Min = reader.GetInt64(minIdx), Max = reader.GetInt64(maxIdx) }; if (string.IsNullOrEmpty(sequence.Name)) { Logger.LogWarning(SqlServerDesignStrings.SequenceNameEmpty(sequence.SchemaName)); continue; } if (_defaultSequenceMinMax.ContainsKey(sequence.DataType)) { var defaultMin = _defaultSequenceMinMax[sequence.DataType][0]; sequence.Min = sequence.Min == defaultMin ? null : sequence.Min; sequence.Start = sequence.Start == defaultMin ? null : sequence.Start; var defaultMax = _defaultSequenceMinMax[sequence.DataType][1]; sequence.Max = sequence.Max == defaultMax ? null : sequence.Max; } _databaseModel.Sequences.Add(sequence); } } }
public virtual void AddKeysToModel([NotNull] Entity.Metadata.Model relationalModel) { Check.NotNull(relationalModel, nameof(relationalModel)); var entityTypeToForeignKeyConstraintsMap = new Dictionary <EntityType, Dictionary <string, List <Property> > >(); // string is ConstraintId foreach (var entityType in relationalModel.EntityTypes) { var primaryKeyProperties = new List <Property>(); var constraints = new Dictionary <string, List <Property> >(); entityTypeToForeignKeyConstraintsMap[entityType] = constraints; foreach (var property in entityType.Properties) { int primaryKeyOrdinal; if (_primaryKeyOrdinals.TryGetValue(property.Name, out primaryKeyOrdinal)) { primaryKeyProperties.Add(property); } Dictionary <string, int> foreignKeyConstraintIdOrdinalMap; if (_foreignKeyOrdinals.TryGetValue(property.Name, out foreignKeyConstraintIdOrdinalMap)) { // relationalProperty represents (part of) a foreign key foreach (var constraintId in foreignKeyConstraintIdOrdinalMap.Keys) { List <Property> constraintProperties; if (!constraints.TryGetValue(constraintId, out constraintProperties)) { constraintProperties = new List <Property>(); constraints.Add(constraintId, constraintProperties); } constraintProperties.Add(property); } } } if (primaryKeyProperties.Count() > 0) { entityType.SetPrimaryKey( primaryKeyProperties .OrderBy(p => _primaryKeyOrdinals[p.Name]) // note: for relational property p.Name is its columnId .ToList()); } else { var errorMessage = SqlServerDesignStrings.NoPrimaryKeyColumns( entityType.Relational().Schema, entityType.Relational().TableName); entityType.AddAnnotation(AnnotationNameEntityTypeError, errorMessage); Logger.LogWarning(errorMessage); } } AddForeignKeysToModel(relationalModel, entityTypeToForeignKeyConstraintsMap); }
private void GetDefaultSchema() { var command = _connection.CreateCommand(); command.CommandText = "SELECT SCHEMA_NAME()"; var schema = command.ExecuteScalar() as string ?? "dbo"; Logger.LogTrace(SqlServerDesignStrings.FoundDefaultSchema(schema)); _databaseModel.DefaultSchemaName = schema; }
private void GetTables() { var command = _connection.CreateCommand(); // for origin of the sys.extended_properties SELECT statement // below see https://github.com/aspnet/EntityFramework/issues/5126 command.CommandText = @"SELECT schema_name(t.schema_id) AS [schema], t.name" + MemoryOptimizedTableColumn + @" FROM sys.tables AS t WHERE t.is_ms_shipped = 0 AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = t.object_id AND minor_id = 0 AND class = 1 AND name = N'microsoft_database_tools_support') " + $"AND t.name <> '{HistoryRepository.DefaultTableName}'" + TemporalTableWhereClause; // Interpolation okay; strings using (var reader = command.ExecuteReader()) { while (reader.Read()) { var table = new TableModel { Database = _databaseModel, SchemaName = reader.GetValueOrDefault <string>("schema"), Name = reader.GetValueOrDefault <string>("name") }; if (!string.IsNullOrEmpty(MemoryOptimizedTableColumn)) { table[SqlServerFullAnnotationNames.Instance.MemoryOptimized] = reader.GetValueOrDefault <bool?>("is_memory_optimized"); } Logger.LogDebug( RelationalDesignEventId.FoundTable, () => SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name)); if (_tableSelectionSet.Allows(table.SchemaName, table.Name)) { _databaseModel.Tables.Add(table); _tables[TableKey(table)] = table; } else { Logger.LogDebug( RelationalDesignEventId.TableSkipped, () => SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name)); } } } }
public void E2ETest_UseAttributesInsteadOfFluentApi() { var configuration = new ReverseEngineeringConfiguration { ConnectionString = _connectionString, ContextClassName = "AttributesContext", ProjectPath = TestProjectDir + Path.DirectorySeparatorChar, // tests that ending DirectorySeparatorChar does not affect namespace ProjectRootNamespace = TestNamespace, OutputPath = TestSubDir, TableSelectionSet = Filter }; var filePaths = Generator.GenerateAsync(configuration).GetAwaiter().GetResult(); var actualFileSet = new FileSet(InMemoryFiles, Path.GetFullPath(Path.Combine(TestProjectDir, TestSubDir))) { Files = Enumerable.Repeat(filePaths.ContextFile, 1).Concat(filePaths.EntityTypeFiles).Select(Path.GetFileName).ToList() }; var expectedFileSet = new FileSet(new FileSystemFileService(), Path.Combine("ReverseEngineering", "ExpectedResults", "E2E_UseAttributesInsteadOfFluentApi"), contents => contents.Replace("namespace " + TestNamespace, "namespace " + TestNamespace + "." + TestSubDir) .Replace("{{connectionString}}", _connectionString)) { Files = new List <string> { "AttributesContext.expected" } .Concat(_expectedEntityTypeFiles).ToList() }; AssertLog(new LoggerMessages { Warn = { RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geographyColumn", "geography"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geometryColumn", "geometry"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.hierarchyidColumn", "hierarchyid"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.sql_variantColumn", "sql_variant"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.xmlColumn", "xml"), RelationalDesignStrings.UnableToScaffoldIndexMissingProperty("IX_UnscaffoldableIndex"), SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy("dbo.PropertyConfiguration.PropertyConfigurationID", "tinyint"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.TableWithUnmappablePrimaryKeyColumn.TableWithUnmappablePrimaryKeyColumnID", "hierarchyid"), RelationalDesignStrings.PrimaryKeyErrorPropertyNotFound("dbo.TableWithUnmappablePrimaryKeyColumn"), RelationalDesignStrings.UnableToGenerateEntityType("dbo.TableWithUnmappablePrimaryKeyColumn") } }); AssertEqualFileContents(expectedFileSet, actualFileSet); AssertCompile(actualFileSet); }
public void E2ETest_AllFluentApi() { var configuration = new ReverseEngineeringConfiguration { ConnectionString = _connectionString, ProjectPath = TestProjectDir, ProjectRootNamespace = TestNamespace, OutputPath = null, // not used for this test UseFluentApiOnly = true, TableSelectionSet = Filter, }; var filePaths = Generator.GenerateAsync(configuration).GetAwaiter().GetResult(); var actualFileSet = new FileSet(InMemoryFiles, Path.GetFullPath(TestProjectDir)) { Files = Enumerable.Repeat(filePaths.ContextFile, 1).Concat(filePaths.EntityTypeFiles).Select(Path.GetFileName).ToList() }; var expectedFileSet = new FileSet(new FileSystemFileService(), Path.Combine("ReverseEngineering", "ExpectedResults", "E2E_AllFluentApi"), inputFile => inputFile.Replace("{{connectionString}}", _connectionString)) { Files = (new List <string> { "SqlServerReverseEngineerTestE2EContext.expected" }) .Concat(_expectedEntityTypeFiles).ToList() }; AssertLog(new LoggerMessages { Warn = { RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geographyColumn", "geography"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geometryColumn", "geometry"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.hierarchyidColumn", "hierarchyid"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.sql_variantColumn", "sql_variant"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.xmlColumn", "xml"), RelationalDesignStrings.UnableToScaffoldIndexMissingProperty("IX_UnscaffoldableIndex"), SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy("dbo.PropertyConfiguration.PropertyConfigurationID", "tinyint"), RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.TableWithUnmappablePrimaryKeyColumn.TableWithUnmappablePrimaryKeyColumnID", "hierarchyid"), RelationalDesignStrings.PrimaryKeyErrorPropertyNotFound("dbo.TableWithUnmappablePrimaryKeyColumn"), RelationalDesignStrings.UnableToGenerateEntityType("dbo.TableWithUnmappablePrimaryKeyColumn"), } }); AssertEqualFileContents(expectedFileSet, actualFileSet); AssertCompile(actualFileSet); }
private void VisitTypeMapping(PropertyBuilder propertyBuilder, ColumnModel column) { if (column.SqlServer().IsIdentity) { if (typeof(byte) == propertyBuilder.Metadata.ClrType) { Logger.LogWarning( SqlServerDesignEventId.DataTypeDoesNotAllowSqlServerIdentityStrategyWarning, () => SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy( column.DisplayName, column.DataType)); } else { propertyBuilder .ValueGeneratedOnAdd() .UseSqlServerIdentityColumn(); } } var dateTimePrecision = column.SqlServer().DateTimePrecision; if (dateTimePrecision.HasValue && dateTimePrecision.Value != DefaultTimeTimePrecision) { propertyBuilder.Metadata.SetMaxLength(null); propertyBuilder.HasColumnType( string.Format(CultureInfo.InvariantCulture, "{0}({1})", column.DataType, dateTimePrecision.Value)); } else if (!HasTypeAlias(column)) { var qualifiedColumnTypeAndMaxLength = MaxLengthQualifiedDataType(column.DataType, column.MaxLength); if (qualifiedColumnTypeAndMaxLength != null) { propertyBuilder.HasColumnType(qualifiedColumnTypeAndMaxLength.Item1); propertyBuilder.Metadata.SetMaxLength(qualifiedColumnTypeAndMaxLength.Item2); } } var columnType = propertyBuilder.Metadata.SqlServer().ColumnType; if (columnType != null) { TypeMapper.ValidateTypeName(columnType); } }
private void GetTypeAliases() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT [schema_name], [type_name], [underlying_system_type] FROM (SELECT s1.[name] as [schema_name], t1.[name] as [type_name], ( CASE WHEN t1.[xusertype] = t1.[xtype] THEN NULL ELSE ( SELECT t2.[name] FROM [sys].[systypes] AS t2 WHERE t2.[xusertype] = t2.[xtype] AND t2.[xusertype] = t1.[xtype] ) END) as [underlying_system_type] FROM [sys].[systypes] AS t1 LEFT JOIN [sys].[types] AS t3 ON t1.[xusertype] = t3.[user_type_id] AND t1.[xtype] = t3.[system_type_id] LEFT JOIN [sys].[schemas] AS s1 ON t3.[schema_id] = s1.[schema_id] ) AS t WHERE [underlying_system_type] IS NOT NULL"; var typeAliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var aliasSchema = reader.GetValueOrDefault <string>("schema_name"); var alias = reader.GetValueOrDefault <string>("type_name"); var underlyingSystemType = reader.GetValueOrDefault <string>("underlying_system_type"); Logger.LogDebug( SqlServerDesignEventId.FoundTypeAlias, () => SqlServerDesignStrings.FoundTypeAlias(aliasSchema, alias, underlyingSystemType)); typeAliasMap.Add(SchemaQualifiedKey(alias, aliasSchema), underlyingSystemType); } } _databaseModel.SqlServer().TypeAliases = typeAliasMap; }
private void GetTables() { var command = _connection.CreateCommand(); // for origin of the sys.extended_properties SELECT statement // below see https://github.com/aspnet/EntityFramework/issues/5126 command.CommandText = @"SELECT schema_name(t.schema_id) AS [schema], t.name FROM sys.tables AS t WHERE t.is_ms_shipped = 0 AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = t.object_id AND minor_id = 0 AND class = 1 AND name = N'microsoft_database_tools_support') " + $"AND t.name <> '{HistoryRepository.DefaultTableName}'" + TemporalTableWhereClause; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var table = new TableModel { Database = _databaseModel, SchemaName = reader.GetValueOrDefault <string>("schema"), Name = reader.GetValueOrDefault <string>("name") }; Logger.LogTrace(SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name)); if (_tableSelectionSet.Allows(table.SchemaName, table.Name)) { _databaseModel.Tables.Add(table); _tables[TableKey(table)] = table; } else { Logger.LogTrace( SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name)); } } } }
public virtual void AddPropertiesToModel([NotNull] Entity.Metadata.Model relationalModel) { Check.NotNull(relationalModel, nameof(relationalModel)); foreach (var tc in _tableColumns.Values) { var table = _tables[tc.TableId]; if (!_tableSelectionSet.Allows(table.SchemaName, table.TableName)) { continue; } EntityType entityType; if (!_tableIdToEntityType.TryGetValue(tc.TableId, out entityType)) { Logger.LogWarning( SqlServerDesignStrings.CannotFindTableForColumn(tc.Id, tc.TableId)); continue; } // If we come across a column with a SQL Server type which we can't map we will ignore it. // Note: foreign key properties appear just like any other property in the relational model. Type clrPropertyType; if (!SqlServerTypeMapping._sqlTypeToClrTypeMap.TryGetValue(tc.DataType, out clrPropertyType)) { Logger.LogWarning( SqlServerDesignStrings.CannotFindTypeMappingForColumn(tc.Id, tc.DataType)); continue; } if (tc.IsNullable) { clrPropertyType = clrPropertyType.MakeNullable(); } var property = entityType.AddProperty(tc.Id, clrPropertyType); property.Relational().ColumnName = _tableColumns[tc.Id].ColumnName; _columnIdToProperty[tc.Id] = property; AddFacetsOnProperty(property, _tableColumns[tc.Id]); } }
public virtual Property FindTargetProperty( [NotNull] string foreignKeyConstraintId, [NotNull] string fromColumnId) { Check.NotEmpty(foreignKeyConstraintId, nameof(foreignKeyConstraintId)); Check.NotEmpty(fromColumnId, nameof(fromColumnId)); ForeignKeyColumnMapping foreignKeyColumnMapping; if (!_foreignKeyColumnMappings.TryGetValue( foreignKeyConstraintId + fromColumnId, out foreignKeyColumnMapping)) { Logger.LogWarning( SqlServerDesignStrings.CannotFindForeignKeyMappingForConstraintId( foreignKeyConstraintId, fromColumnId)); return(null); } var toTable = _tables[_tableColumns[foreignKeyColumnMapping.ToColumnId].TableId]; if (!_tableSelectionSet.Allows(toTable.SchemaName, toTable.TableName)) { // target property belongs to a table which was excluded by the TableSelectionSet return(null); } Property toColumnRelationalProperty; if (!_columnIdToProperty.TryGetValue( foreignKeyColumnMapping.ToColumnId, out toColumnRelationalProperty)) { Logger.LogWarning( SqlServerDesignStrings.CannotFindRelationalPropertyForColumnId( foreignKeyConstraintId, foreignKeyColumnMapping.ToColumnId)); return(null); } return(toColumnRelationalProperty); }
private void GetTypeAliases() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT [type_name], [underlying_system_type] FROM (SELECT t1.[name] as [type_name], ( CASE WHEN t1.[xusertype] = t1.[xtype] THEN NULL ELSE ( SELECT t2.[name] FROM [sys].[systypes] AS t2 WHERE t2.[xusertype] = t2.[xtype] AND t2.[xusertype] = t1.[xtype] ) END) as [underlying_system_type] FROM [sys].[systypes] AS t1 ) AS t WHERE [underlying_system_type] IS NOT NULL"; var typeAliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var alias = reader.GetValueOrDefault <string>("type_name"); var underlyingSystemType = reader.GetValueOrDefault <string>("underlying_system_type"); Logger.LogTrace(SqlServerDesignStrings.FoundTypeAlias(alias, underlyingSystemType)); typeAliasMap.Add(alias, underlyingSystemType); } } _databaseModel.SqlServer().TypeAliases = typeAliasMap; }
public virtual void AddFacetsOnProperty( [NotNull] Property property, [NotNull] TableColumn tableColumn) { Check.NotNull(property, nameof(property)); Check.NotNull(tableColumn, nameof(tableColumn)); property.IsNullable = tableColumn.IsNullable; if (property.Name != tableColumn.ColumnName) { property.Relational().ColumnName = tableColumn.ColumnName; } string typeName = null; if (DataTypesForNumericPrecisionAndScale.Contains(tableColumn.DataType)) { if (tableColumn.NumericPrecision.HasValue) { if (tableColumn.Scale.HasValue) { typeName = tableColumn.DataType + "(" + tableColumn.NumericPrecision.Value + ", " + tableColumn.Scale.Value + ")"; } else { typeName = tableColumn.DataType + "(" + tableColumn.NumericPrecision.Value + ")"; } } } else if (DataTypesForDateTimePrecisionAndScale.Contains(tableColumn.DataType)) { if (tableColumn.DateTimePrecision.HasValue) { if (tableColumn.Scale.HasValue) { typeName = tableColumn.DataType + "(" + tableColumn.DateTimePrecision.Value + ", " + tableColumn.Scale.Value + ")"; } else { typeName = tableColumn.DataType + "(" + tableColumn.DateTimePrecision.Value + ")"; } } } if (typeName != null) { property.Relational().ColumnType = typeName; } if (tableColumn.IsIdentity) { property.ValueGenerated = ValueGenerated.OnAdd; if (typeof(byte) == SqlServerTypeMapping._sqlTypeToClrTypeMap[tableColumn.DataType]) { Logger.LogWarning( SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy(tableColumn.Id, tableColumn.DataType)); } else { property.SqlServer().ValueGenerationStrategy = SqlServerValueGenerationStrategy.IdentityColumn; } } if (tableColumn.IsStoreGenerated || tableColumn.DataType == "timestamp") { // timestamp columns should always be treated as store generated // (rowversion columns are stored as data type 'timestamp') property.ValueGenerated = ValueGenerated.OnAddOrUpdate; } if (tableColumn.DefaultValue != null) { var defaultExpressionOrValue = _sqlServerLiteralUtilities .ConvertSqlServerDefaultValue( ((IProperty)property).ClrType, tableColumn.DefaultValue); if (defaultExpressionOrValue != null && defaultExpressionOrValue.DefaultExpression != null) { property.Relational().GeneratedValueSql = defaultExpressionOrValue.DefaultExpression; } else if (defaultExpressionOrValue != null && defaultExpressionOrValue.DefaultValue != null) { property.Relational().DefaultValue = defaultExpressionOrValue.DefaultValue; } else { Logger.LogWarning( SqlServerDesignStrings.UnableToConvertDefaultValue( tableColumn.Id, tableColumn.DefaultValue, ((IProperty)property).ClrType, property.Name, property.DeclaringEntityType.Name)); } } }
private void GetForeignKeys() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT schema_name(f.schema_id) AS [schema_name], object_name(f.parent_object_id) AS table_name, f.name AS foreign_key_name, object_schema_name(f.referenced_object_id) AS principal_table_schema_name, object_name(f.referenced_object_id) AS principal_table_name, col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name, col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name, is_disabled, delete_referential_action_desc, update_referential_action_desc, fc.constraint_column_id FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id ORDER BY schema_name(f.schema_id), object_name(f.parent_object_id), f.name"; using (var reader = command.ExecuteReader()) { var lastFkName = string.Empty; var lastFkSchemaName = string.Empty; var lastFkTableName = string.Empty; ForeignKeyModel fkInfo = null; while (reader.Read()) { var schemaName = reader.GetString(0); var tableName = reader.GetString(1); var fkName = reader.GetStringOrNull(2); if (string.IsNullOrEmpty(fkName)) { Logger.LogWarning(SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName)); continue; } if (!_tableSelectionSet.Allows(schemaName, tableName)) { continue; } if (fkInfo == null || lastFkSchemaName != schemaName || lastFkTableName != tableName || lastFkName != fkName) { lastFkName = fkName; lastFkSchemaName = schemaName; lastFkTableName = tableName; var table = _tables[TableKey(tableName, schemaName)]; var principalSchemaTableName = reader.GetStringOrNull(3); var principalTableName = reader.GetStringOrNull(4); TableModel principalTable = null; if (!string.IsNullOrEmpty(principalSchemaTableName) && !string.IsNullOrEmpty(principalTableName)) { _tables.TryGetValue(TableKey(principalTableName, principalSchemaTableName), out principalTable); } fkInfo = new ForeignKeyModel { Name = fkName, Table = table, PrincipalTable = principalTable, OnDelete = ConvertToReferentialAction(reader.GetStringOrNull(8)) }; table.ForeignKeys.Add(fkInfo); } var fkColumn = new ForeignKeyColumnModel { Ordinal = reader.GetInt32(10) }; var fromColumnName = reader.GetStringOrNull(5); ColumnModel fromColumn; if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null) { fkColumn.Column = fromColumn; } if (fkInfo.PrincipalTable != null) { var toColumnName = reader.GetString(6); ColumnModel toColumn; if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null) { fkColumn.PrincipalColumn = toColumn; } } fkInfo.Columns.Add(fkColumn); } } }
private void GetColumns() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT DISTINCT schema_name(t.schema_id) AS [schema], t.name AS [table], type_name(c.user_type_id) AS [typename], c.name AS [column_name], c.column_id AS [ordinal], c.is_nullable AS [nullable], CAST(ic.key_ordinal AS int) AS [primary_key_ordinal], object_definition(c.default_object_id) AS [default_sql], CAST(CASE WHEN c.precision <> tp.precision THEN c.precision ELSE null END AS int) AS [precision], CAST(CASE WHEN c.scale <> tp.scale THEN c.scale ELSE null END AS int) AS [scale], CAST(CASE WHEN c.max_length <> tp.max_length THEN c.max_length ELSE null END AS int) AS [max_length], c.is_identity, c.is_computed FROM sys.index_columns ic RIGHT JOIN (SELECT * FROM sys.indexes WHERE is_primary_key = 1) AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id RIGHT JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id RIGHT JOIN sys.types tp ON tp.user_type_id = c.user_type_id JOIN sys.tables AS t ON t.object_id = c.object_id WHERE t.name <> '" + HistoryRepository.DefaultTableName + "'"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var schemaName = reader.GetString(0); var tableName = reader.GetString(1); var columnName = reader.GetStringOrNull(3); if (!_tableSelectionSet.Allows(schemaName, tableName)) { continue; } if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning(SqlServerDesignStrings.ColumnNameEmptyOnTable(schemaName, tableName)); continue; } TableModel table; if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table)) { Logger.LogWarning( SqlServerDesignStrings.UnableToFindTableForColumn(columnName, schemaName, tableName)); continue; } var dataTypeName = reader.GetString(2); var nullable = reader.IsDBNull(5) ? false : reader.GetBoolean(5); var scale = reader.IsDBNull(9) ? default(int?) : reader.GetInt32(9); var maxLength = reader.IsDBNull(10) ? default(int?) : reader.GetInt32(10); var dateTimePrecision = default(int?); if (dataTypeName == "nvarchar" || dataTypeName == "nchar") { maxLength /= 2; } if (dataTypeName == "decimal" || dataTypeName == "numeric") { // maxlength here represents storage bytes. The server determines this, not the client. maxLength = null; } if (_dateTimePrecisionTypes.Contains(dataTypeName)) { dateTimePrecision = scale ?? DefaultDateTimePrecision; scale = null; } var isIdentity = !reader.IsDBNull(11) && reader.GetBoolean(11); var isComputed = reader.GetBoolean(12) || dataTypeName == "timestamp"; var column = new ColumnModel { Table = table, DataType = dataTypeName, Name = columnName, Ordinal = reader.GetInt32(4) - 1, IsNullable = nullable, PrimaryKeyOrdinal = reader.IsDBNull(6) ? default(int?) : reader.GetInt32(6), DefaultValue = reader.IsDBNull(7) ? null : reader.GetString(7), Precision = reader.IsDBNull(8) ? default(int?) : reader.GetInt32(8), Scale = scale, MaxLength = maxLength <= 0 ? default(int?) : maxLength, ValueGenerated = isIdentity ? ValueGenerated.OnAdd : isComputed ? ValueGenerated.OnAddOrUpdate : default(ValueGenerated?) }; column.SqlServer().IsIdentity = isIdentity; column.SqlServer().DateTimePrecision = dateTimePrecision; table.Columns.Add(column); _tableColumns.Add(ColumnKey(table, column.Name), column); } } }
private void GetIndexes() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT object_schema_name(i.object_id) AS [schema_name], object_name(i.object_id) AS [table_name], i.name AS [index_name], i.is_unique, c.name AS [column_name], i.type_desc, ic.key_ordinal 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 object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"' ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal"; using (var reader = command.ExecuteReader()) { IndexModel index = null; while (reader.Read()) { var schemaName = reader.GetString(0); var tableName = reader.GetString(1); var indexName = reader.GetStringOrNull(2); if (!_tableSelectionSet.Allows(schemaName, tableName)) { continue; } if (string.IsNullOrEmpty(indexName)) { Logger.LogWarning(SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName)); continue; } if (index == null || index.Name != indexName || index.Table.Name != tableName || index.Table.SchemaName != schemaName) { TableModel table; if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table)) { Logger.LogWarning( SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName)); continue; } index = new IndexModel { Table = table, Name = indexName, IsUnique = reader.IsDBNull(3) ? false : reader.GetBoolean(3), }; index.SqlServer().IsClustered = reader.GetStringOrNull(5) == "CLUSTERED"; table.Indexes.Add(index); } var columnName = reader.GetStringOrNull(4); var indexOrdinal = reader.GetByte(6); ColumnModel column = null; if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning( SqlServerDesignStrings.ColumnNameEmptyOnIndex( schemaName, tableName, indexName)); } else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column)) { Logger.LogWarning( SqlServerDesignStrings.UnableToFindColumnForIndex( indexName, columnName, schemaName, tableName)); } else { var indexColumn = new IndexColumnModel { Index = index, Column = column, Ordinal = indexOrdinal }; index.IndexColumns.Add(indexColumn); } } } }
private void GetColumns() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT DISTINCT schema_name(t.schema_id) AS [schema], t.name AS [table], type_name(c.user_type_id) AS [typename], c.name AS [column_name], c.column_id AS [ordinal], c.is_nullable AS [nullable], CAST(ic.key_ordinal AS int) AS [primary_key_ordinal], object_definition(c.default_object_id) AS [default_sql], cc.definition AS [computed_sql], CAST(CASE WHEN c.precision <> tp.precision THEN c.precision ELSE null END AS int) AS [precision], CAST(CASE WHEN c.scale <> tp.scale THEN c.scale ELSE null END AS int) AS [scale], CAST(CASE WHEN c.max_length <> tp.max_length THEN c.max_length ELSE null END AS int) AS [max_length], c.is_identity, c.is_computed FROM sys.index_columns ic RIGHT JOIN (SELECT * FROM sys.indexes WHERE is_primary_key = 1) AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id RIGHT JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id RIGHT JOIN sys.types tp ON tp.user_type_id = c.user_type_id LEFT JOIN sys.computed_columns cc ON cc.object_id = c.object_id AND cc.column_id = c.column_id JOIN sys.tables AS t ON t.object_id = c.object_id WHERE t.name <> '" + HistoryRepository.DefaultTableName + "'" + TemporalTableWhereClause; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var schemaName = reader.GetValueOrDefault <string>("schema"); var tableName = reader.GetValueOrDefault <string>("table"); var columnName = reader.GetValueOrDefault <string>("column_name"); var dataTypeName = reader.GetValueOrDefault <string>("typename"); var ordinal = reader.GetValueOrDefault <int>("ordinal"); var nullable = reader.GetValueOrDefault <bool>("nullable"); var primaryKeyOrdinal = reader.GetValueOrDefault <int?>("primary_key_ordinal"); var defaultValue = reader.GetValueOrDefault <string>("default_sql"); var computedValue = reader.GetValueOrDefault <string>("computed_sql"); var precision = reader.GetValueOrDefault <int?>("precision"); var scale = reader.GetValueOrDefault <int?>("scale"); var maxLength = reader.GetValueOrDefault <int?>("max_length"); var isIdentity = reader.GetValueOrDefault <bool>("is_identity"); var isComputed = reader.GetValueOrDefault <bool>("is_computed"); Logger.LogDebug( RelationalDesignEventId.FoundColumn, () => SqlServerDesignStrings.FoundColumn( schemaName, tableName, columnName, dataTypeName, ordinal, nullable, primaryKeyOrdinal, defaultValue, computedValue, precision, scale, maxLength, isIdentity, isComputed)); if (!_tableSelectionSet.Allows(schemaName, tableName)) { Logger.LogDebug( RelationalDesignEventId.ColumnSkipped, () => SqlServerDesignStrings.ColumnNotInSelectionSet(columnName, schemaName, tableName)); continue; } if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning( SqlServerDesignEventId.ColumnMustBeNamedWarning, () => SqlServerDesignStrings.ColumnNameEmptyOnTable(schemaName, tableName)); continue; } TableModel table; if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table)) { Logger.LogWarning( RelationalDesignEventId.MissingTableWarning, () => SqlServerDesignStrings.UnableToFindTableForColumn(columnName, schemaName, tableName)); continue; } if (dataTypeName == "nvarchar" || dataTypeName == "nchar") { maxLength /= 2; } if (dataTypeName == "decimal" || dataTypeName == "numeric") { // maxlength here represents storage bytes. The server determines this, not the client. maxLength = null; } var dateTimePrecision = default(int?); if (_dateTimePrecisionTypes.Contains(dataTypeName)) { dateTimePrecision = scale ?? DefaultDateTimePrecision; scale = null; } var column = new ColumnModel { Table = table, DataType = dataTypeName, Name = columnName, Ordinal = ordinal - 1, IsNullable = nullable, PrimaryKeyOrdinal = primaryKeyOrdinal, DefaultValue = defaultValue, ComputedValue = computedValue, Precision = precision, Scale = scale, MaxLength = maxLength <= 0 ? default(int?) : maxLength, ValueGenerated = isIdentity ? ValueGenerated.OnAdd : isComputed || dataTypeName == "timestamp" ? ValueGenerated.OnAddOrUpdate : default(ValueGenerated?) }; column.SqlServer().IsIdentity = isIdentity; column.SqlServer().DateTimePrecision = dateTimePrecision; table.Columns.Add(column); _tableColumns.Add(ColumnKey(table, column.Name), column); } } }
private void GetForeignKeys() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT schema_name(f.schema_id) AS [schema_name], object_name(f.parent_object_id) AS table_name, f.name AS foreign_key_name, object_schema_name(f.referenced_object_id) AS principal_table_schema_name, object_name(f.referenced_object_id) AS principal_table_name, col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name, col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name, is_disabled, delete_referential_action_desc, update_referential_action_desc, fc.constraint_column_id FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id ORDER BY schema_name(f.schema_id), object_name(f.parent_object_id), f.name"; using (var reader = command.ExecuteReader()) { var lastFkName = string.Empty; var lastFkSchemaName = string.Empty; var lastFkTableName = string.Empty; ForeignKeyModel fkInfo = null; while (reader.Read()) { var schemaName = reader.GetValueOrDefault <string>("schema_name"); var tableName = reader.GetValueOrDefault <string>("table_name"); var fkName = reader.GetValueOrDefault <string>("foreign_key_name"); var principalTableSchemaName = reader.GetValueOrDefault <string>("principal_table_schema_name"); var principalTableName = reader.GetValueOrDefault <string>("principal_table_name"); var fromColumnName = reader.GetValueOrDefault <string>("constraint_column_name"); var toColumnName = reader.GetValueOrDefault <string>("referenced_column_name"); var updateAction = reader.GetValueOrDefault <string>("update_referential_action_desc"); var deleteAction = reader.GetValueOrDefault <string>("delete_referential_action_desc"); var ordinal = reader.GetValueOrDefault <int>("constraint_column_id"); Logger.LogDebug( RelationalDesignEventId.FoundForeignKeyColumn, () => SqlServerDesignStrings.FoundForeignKeyColumn( schemaName, tableName, fkName, principalTableSchemaName, principalTableName, fromColumnName, toColumnName, updateAction, deleteAction, ordinal)); if (string.IsNullOrEmpty(fkName)) { Logger.LogWarning( SqlServerDesignEventId.ForeignKeyMustBeNamedWarning, () => SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName)); continue; } if (!_tableSelectionSet.Allows(schemaName, tableName)) { Logger.LogDebug( SqlServerDesignEventId.ForeignKeyColumnSkipped, () => SqlServerDesignStrings.ForeignKeyColumnNotInSelectionSet( fromColumnName, fkName, schemaName, tableName)); continue; } if (fkInfo == null || lastFkSchemaName != schemaName || lastFkTableName != tableName || lastFkName != fkName) { lastFkName = fkName; lastFkSchemaName = schemaName; lastFkTableName = tableName; var table = _tables[TableKey(tableName, schemaName)]; TableModel principalTable = null; if (!string.IsNullOrEmpty(principalTableSchemaName) && !string.IsNullOrEmpty(principalTableName)) { _tables.TryGetValue(TableKey(principalTableName, principalTableSchemaName), out principalTable); } if (principalTable == null) { Logger.LogDebug( RelationalDesignEventId.ForeignKeyReferencesMissingTable, () => SqlServerDesignStrings.PrincipalTableNotInSelectionSet( fkName, schemaName, tableName, principalTableSchemaName, principalTableName)); } fkInfo = new ForeignKeyModel { Name = fkName, Table = table, PrincipalTable = principalTable, OnDelete = ConvertToReferentialAction(deleteAction) }; table.ForeignKeys.Add(fkInfo); } var fkColumn = new ForeignKeyColumnModel { Ordinal = ordinal }; ColumnModel fromColumn; if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null) { fkColumn.Column = fromColumn; } if (fkInfo.PrincipalTable != null) { ColumnModel toColumn; if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null) { fkColumn.PrincipalColumn = toColumn; } } fkInfo.Columns.Add(fkColumn); } } }
private void GetIndexes() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT object_schema_name(i.object_id) AS [schema_name], object_name(i.object_id) AS [table_name], i.name AS [index_name], i.is_unique, c.name AS [column_name], i.type_desc, ic.key_ordinal 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 INNER JOIN sys.tables t ON t.object_id = i.object_id WHERE object_schema_name(i.object_id) <> 'sys' AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'" + TemporalTableWhereClause + @" ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal"; using (var reader = command.ExecuteReader()) { IndexModel index = null; while (reader.Read()) { var schemaName = reader.GetValueOrDefault <string>("schema_name"); var tableName = reader.GetValueOrDefault <string>("table_name"); var indexName = reader.GetValueOrDefault <string>("index_name"); var isUnique = reader.GetValueOrDefault <bool>("is_unique"); var typeDesc = reader.GetValueOrDefault <string>("type_desc"); var columnName = reader.GetValueOrDefault <string>("column_name"); var indexOrdinal = reader.GetValueOrDefault <byte>("key_ordinal"); Logger.LogDebug( RelationalDesignEventId.FoundIndexColumn, () => SqlServerDesignStrings.FoundIndexColumn( schemaName, tableName, indexName, isUnique, typeDesc, columnName, indexOrdinal)); if (!_tableSelectionSet.Allows(schemaName, tableName)) { Logger.LogDebug( RelationalDesignEventId.IndexColumnSkipped, () => SqlServerDesignStrings.IndexColumnNotInSelectionSet( columnName, indexName, schemaName, tableName)); continue; } if (string.IsNullOrEmpty(indexName)) { Logger.LogWarning( SqlServerDesignEventId.IndexMustBeNamedWarning, () => SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName)); continue; } Debug.Assert(index == null || index.Table != null); if (index == null || index.Name != indexName || index.Table.Name != tableName || index.Table.SchemaName != schemaName) { TableModel table; if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table)) { Logger.LogWarning( SqlServerDesignEventId.IndexTableMissingWarning, () => SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName)); continue; } index = new IndexModel { Table = table, Name = indexName, IsUnique = isUnique }; index.SqlServer().IsClustered = typeDesc == "CLUSTERED"; table.Indexes.Add(index); } ColumnModel column; if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning( SqlServerDesignEventId.IndexColumnMustBeNamedWarning, () => SqlServerDesignStrings.ColumnNameEmptyOnIndex( schemaName, tableName, indexName)); } else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column)) { Logger.LogWarning( RelationalDesignEventId.IndexColumnsNotMappedWarning, () => SqlServerDesignStrings.UnableToFindColumnForIndex( indexName, columnName, schemaName, tableName)); } else { var indexColumn = new IndexColumnModel { Index = index, Column = column, Ordinal = indexOrdinal }; index.IndexColumns.Add(indexColumn); } } } }