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);
}
}
}
