public void IdentifyMappingTable(List <ForeignKey> fkList, Tables tables, bool checkForFkNameClashes)
{
IsMapping = false;
var nonReadOnlyColumns = Columns.Where(c => !c.IsIdentity && !c.IsRowVersion && !c.IsStoreGenerated && !c.Hidden).ToList();
// Ignoring read-only columns, it must have only 2 columns to be a mapping table
if (nonReadOnlyColumns.Count != 2)
{
return;
}
// Must have 2 primary keys
if (nonReadOnlyColumns.Count(x => x.IsPrimaryKey) != 2)
{
return;
}
// No columns should be nullable
if (nonReadOnlyColumns.Any(x => x.IsNullable))
{
return;
}
// Find the foreign keys for this table
var foreignKeys = fkList.Where(x =>
string.Compare(x.FkTableName, Name, StringComparison.OrdinalIgnoreCase) == 0 &&
string.Compare(x.FkSchema, Schema, StringComparison.OrdinalIgnoreCase) == 0)
.ToList();
// Each column must have a foreign key, therefore check column and foreign key counts match
if (foreignKeys.Select(x => x.FkColumn).Distinct().Count() != 2)
{
return;
}
ForeignKey left = foreignKeys[0];
ForeignKey right = foreignKeys[1];
if (!left.IncludeReverseNavigation || !right.IncludeReverseNavigation)
{
return;
}
Table leftTable = tables.GetTable(left.PkTableName, left.PkSchema);
if (leftTable == null)
{
return;
}
Table rightTable = tables.GetTable(right.PkTableName, right.PkSchema);
if (rightTable == null)
{
return;
}
var leftPropName = leftTable.GetUniqueColumnName(rightTable.NameHumanCase, right, checkForFkNameClashes, false, Relationship.ManyToOne); // relationship from the mapping table to each side is Many-to-One
var rightPropName = rightTable.GetUniqueColumnName(leftTable.NameHumanCase, left, checkForFkNameClashes, false, Relationship.ManyToOne); // relationship from the mapping table to each side is Many-to-One
leftTable.AddMappingConfiguration(left, right, leftPropName, rightPropName);
IsMapping = true;
rightTable.AddReverseNavigation(Relationship.ManyToMany, rightTable.NameHumanCase, leftTable, rightPropName, null, null, this);
leftTable.AddReverseNavigation(Relationship.ManyToMany, leftTable.NameHumanCase, rightTable, leftPropName, null, null, this);
}
public static void AddRelationship(List <ForeignKey> fkList, Tables tablesAndViews, String relationshipName, String pkSchema, String pkTableName, String[] pkColumns, String fkSchema, String fkTableName, String[] fkColumns)
{
// Argument validation:
if (fkList == null)
{
throw new ArgumentNullException(nameof(fkList));
}
if (tablesAndViews == null)
{
throw new ArgumentNullException(nameof(tablesAndViews));
}
if (String.IsNullOrEmpty(relationshipName))
{
throw new ArgumentNullException(nameof(relationshipName));
}
if (String.IsNullOrEmpty(pkSchema))
{
throw new ArgumentNullException(nameof(pkSchema));
}
if (String.IsNullOrEmpty(pkTableName))
{
throw new ArgumentNullException(nameof(pkTableName));
}
if (pkColumns == null)
{
throw new ArgumentNullException(nameof(pkColumns));
}
if (pkColumns.Length == 0 || pkColumns.Any(s => String.IsNullOrEmpty(s)))
{
throw new ArgumentException(nameof(pkColumns));
}
if (String.IsNullOrEmpty(fkSchema))
{
throw new ArgumentNullException(nameof(fkSchema));
}
if (String.IsNullOrEmpty(fkTableName))
{
throw new ArgumentNullException(nameof(fkTableName));
}
if (fkColumns == null)
{
throw new ArgumentNullException(nameof(fkColumns));
}
if (fkColumns.Length != pkColumns.Length || fkColumns.Any(s => String.IsNullOrEmpty(s)))
{
throw new ArgumentException(nameof(fkColumns));
}
//////////////////
Table pkTable = tablesAndViews.GetTable(pkTableName, pkSchema);
Table fkTable = tablesAndViews.GetTable(fkTableName, fkSchema);
for (int i = 0; i < pkColumns.Length; i++)
{
String pkc = pkColumns[i];
String fkc = fkColumns[i];
String pkTableNameFiltered = Settings.TableRename(pkTableName, pkSchema, pkTable.IsView); // TODO: This can probably be done-away with. Is `AddRelationship` called before or after table.NameFiltered is set?
ForeignKey fk = new ForeignKey(
fkTableName: fkTable.Name,
fkSchema: fkSchema,
pkTableName: pkTable.Name,
pkSchema: pkSchema,
fkColumn: fkc,
pkColumn: pkc,
constraintName: "AddRelationship: " + relationshipName,
pkTableNameFiltered: pkTableNameFiltered,
ordinal: Int32.MaxValue,
cascadeOnDelete: false,
isNotEnforced: false
);
fk.IncludeReverseNavigation = true;
fkList.Add(fk);
fkTable.HasForeignKey = true;
}
}
public string GetUniqueColumnName(string tableNameHumanCase, ForeignKey foreignKey, bool checkForFkNameClashes, bool makeSingular, Relationship relationship)
{
var addReverseNavigationUniquePropName = (checkForFkNameClashes || Name == foreignKey.FkTableName || (Name == foreignKey.PkTableName && foreignKey.IncludeReverseNavigation));
if (ReverseNavigationUniquePropName.Count == 0)
{
ReverseNavigationUniquePropName.Add(NameHumanCase);
ReverseNavigationUniquePropName.AddRange(Columns.Select(c => c.NameHumanCase));
}
if (!makeSingular)
{
tableNameHumanCase = Inflector.MakePlural(tableNameHumanCase);
}
if (checkForFkNameClashes && ReverseNavigationUniquePropName.Contains(tableNameHumanCase) && !ReverseNavigationUniquePropNameClashes.Contains(tableNameHumanCase))
{
ReverseNavigationUniquePropNameClashes.Add(tableNameHumanCase); // Name clash
}
// Attempt 1
string fkName = (Settings.UsePascalCase ? Inflector.ToTitleCase(foreignKey.FkColumn) : foreignKey.FkColumn).Replace(" ", "").Replace("$", "");
string name = Settings.ForeignKeyName(tableNameHumanCase, foreignKey, fkName, relationship, 1);
string col;
if (!ReverseNavigationUniquePropNameClashes.Contains(name) && !ReverseNavigationUniquePropName.Contains(name))
{
if (addReverseNavigationUniquePropName)
{
ReverseNavigationUniquePropName.Add(name);
}
return(name);
}
if (Name == foreignKey.FkTableName)
{
// Attempt 2
if (fkName.ToLowerInvariant().EndsWith("id"))
{
col = Settings.ForeignKeyName(tableNameHumanCase, foreignKey, fkName, relationship, 2);
if (checkForFkNameClashes && ReverseNavigationUniquePropName.Contains(col) &&
!ReverseNavigationUniquePropNameClashes.Contains(col))
{
ReverseNavigationUniquePropNameClashes.Add(col); // Name clash
}
if (!ReverseNavigationUniquePropNameClashes.Contains(col) &&
!ReverseNavigationUniquePropName.Contains(col))
{
if (addReverseNavigationUniquePropName)
{
ReverseNavigationUniquePropName.Add(col);
}
return(col);
}
}
// Attempt 3
col = Settings.ForeignKeyName(tableNameHumanCase, foreignKey, fkName, relationship, 3);
if (checkForFkNameClashes && ReverseNavigationUniquePropName.Contains(col) &&
!ReverseNavigationUniquePropNameClashes.Contains(col))
{
ReverseNavigationUniquePropNameClashes.Add(col); // Name clash
}
if (!ReverseNavigationUniquePropNameClashes.Contains(col) &&
!ReverseNavigationUniquePropName.Contains(col))
{
if (addReverseNavigationUniquePropName)
{
ReverseNavigationUniquePropName.Add(col);
}
return(col);
}
}
// Attempt 4
col = Settings.ForeignKeyName(tableNameHumanCase, foreignKey, fkName, relationship, 4);
if (checkForFkNameClashes && ReverseNavigationUniquePropName.Contains(col) && !ReverseNavigationUniquePropNameClashes.Contains(col))
{
ReverseNavigationUniquePropNameClashes.Add(col); // Name clash
}
if (!ReverseNavigationUniquePropNameClashes.Contains(col) && !ReverseNavigationUniquePropName.Contains(col))
{
if (addReverseNavigationUniquePropName)
{
ReverseNavigationUniquePropName.Add(col);
}
return(col);
}
// Attempt 5
for (int n = 1; n < 99; ++n)
{
col = Settings.ForeignKeyName(tableNameHumanCase, foreignKey, fkName, relationship, 5) + n;
if (ReverseNavigationUniquePropName.Contains(col))
{
continue;
}
if (addReverseNavigationUniquePropName)
{
ReverseNavigationUniquePropName.Add(col);
}
return(col);
}
// Give up
return(Settings.ForeignKeyName(tableNameHumanCase, foreignKey, fkName, relationship, 6));
}
