public void IdentifyMappingTable(List <ForeignKey> fkList, Tables tables, bool useCamelCase, string collectionType, bool checkForFkNameClashes, bool includeComments, bool isSqlCE)
{
IsMapping = false;
// Must have only 2 columns to be a mapping table
if (Columns.Count != 2)
{
return;
}
// All columns must be primary keys
if (PrimaryKeys.Count() != 2)
{
return;
}
// No columns should be nullable
if (Columns.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];
Table leftTable = tables.GetTable(left.PkTableName, left.PkSchema);
if (leftTable == null)
{
return;
}
Table rightTable = tables.GetTable(right.PkTableName, right.PkSchema);
if (rightTable == null)
{
return;
}
if (leftTable == rightTable)
{
return;
}
var leftPropName = leftTable.GetUniqueColumnPropertyName(rightTable.NameHumanCase, right, useCamelCase, checkForFkNameClashes, false);
var rightPropName = rightTable.GetUniqueColumnPropertyName(leftTable.NameHumanCase, left, useCamelCase, checkForFkNameClashes, false);
leftTable.AddMappingConfiguration(left, right, useCamelCase, leftPropName, rightPropName, isSqlCE);
IsMapping = true;
rightTable.AddReverseNavigation(Relationship.ManyToMany, rightTable.NameHumanCase, leftTable, rightPropName, null, collectionType, includeComments);
leftTable.AddReverseNavigation(Relationship.ManyToMany, leftTable.NameHumanCase, rightTable, leftPropName, null, collectionType, includeComments);
}
// When a table has no primary keys, all the NOT NULL columns are set as being the primary key.
// This function reads the unique indexes for a table, and correctly sets the columns being used as primary keys.
public override void ReadUniqueIndexes(Tables tables)
{
if (Cmd == null)
{
return;
}
if (Cmd.GetType().Name == "SqlCeCommand")
{
return;
}
Cmd.CommandText = UniqueIndexSQL + IncludeQueryTraceOn9481();
var list = new List <UniqueIndex>();
using (DbDataReader rdr = Cmd.ExecuteReader())
{
while (rdr.Read())
{
var uniqueIndex = new UniqueIndex();
uniqueIndex.Schema = rdr["TableSchema"].ToString().Trim();
uniqueIndex.TableName = rdr["TableName"].ToString().Trim();
uniqueIndex.IndexName = rdr["IndexName"].ToString().Trim();
uniqueIndex.KeyOrdinal = (byte)rdr["KeyOrdinal"];
uniqueIndex.Column = rdr["ColumnName"].ToString().Trim();
uniqueIndex.ColumnCount = (int)rdr["ColumnCount"];
list.Add(uniqueIndex);
}
}
Table t = null;
var indexes = list
.Select(x => new { x.Schema, x.TableName, x.IndexName })
.Distinct()
.OrderBy(o => o.Schema)
.ThenBy(o => o.TableName)
.ThenBy(o => o.IndexName);
foreach (var index in indexes)
{
if (t == null || t.Name != index.TableName || t.Schema != index.Schema)
{
t = tables.Find(x => x.Name == index.TableName && x.Schema == index.Schema);
}
if (t != null && !t.PrimaryKeys.Any())
{
// Table has no primary keys
var uniqueIndexKeys =
list.Where(x => x.Schema == index.Schema && x.TableName == index.TableName && x.IndexName == index.IndexName)
.Select(x => new { x.IndexName, x.KeyOrdinal, x.Column, x.ColumnCount })
.OrderBy(o => o.ColumnCount)
.ThenBy(o => o.IndexName);
// Process only the first index with the lowest unique column count
string indexName = null;
foreach (var key in uniqueIndexKeys)
{
if (indexName == null)
{
indexName = key.IndexName;
}
if (indexName != key.IndexName)
{
break; // First unique index with lowest column count has been processed, exit.
}
var col = t.Columns.Find(x => x.Name == key.Column);
if (col != null && !col.IsNullable && !col.Hidden)
{
col.IsPrimaryKey = true;
col.IsPrimaryKeyViaUniqueIndex = true;
col.UniqueIndexName = indexName;
}
}
}
}
}
public override void ProcessForeignKeys(List <ForeignKey> fkList, Tables tables, bool useCamelCase, bool prependSchemaName, string collectionType, bool checkForFkNameClashes, bool includeComments)
{
var constraints = fkList.Select(x => x.ConstraintName).Distinct();
foreach (var constraint in constraints)
{
var localConstraint = constraint;
var foreignKeys = fkList.Where(x => x.ConstraintName == localConstraint).ToList();
var casete = fkList.Where(x => x.ConstraintName == localConstraint).Select(t => t.Cascade).First();
var foreignKey = foreignKeys.First();
Table fkTable = tables.GetTable(foreignKey.FkTableName, foreignKey.FkSchema);
if (fkTable == null || fkTable.IsMapping || !fkTable.HasForeignKey)
{
continue;
}
Table pkTable = tables.GetTable(foreignKey.PkTableName, foreignKey.PkSchema);
if (pkTable == null || pkTable.IsMapping)
{
continue;
}
var fkCols = foreignKeys.Select(x => new
{
fkOrdinal = x.Ordinal,
col = fkTable.Columns.Find(n => n.PropertyName == x.FkColumn || n.PropertyName == x.FkColumn + "_")
})
.Where(x => x != null)
.ToList();
var pkCols = foreignKeys.Select(x => pkTable.Columns.Find(n => n.PropertyName == x.PkColumn)).Where(x => x != null).OrderBy(o => o.Ordinal).ToList();
if (!pkCols.Any())
{
pkCols = foreignKeys.Select(x => pkTable.Columns.Find(n => n.PropertyName == x.PkColumn + "_")).Where(x => x != null).OrderBy(o => o.Ordinal).ToList();
}
var fkCol = fkCols.First();
var pkCol = pkCols.First();
if (!pkCol.IsPrimaryKey)
{
continue;
}
var relationship = CodeFirstTools.CalcRelationship(pkTable, fkTable, fkCol.col, pkCol);
string pkTableHumanCase = foreignKey.PkTableHumanCase(useCamelCase, prependSchemaName);
string pkPropName = fkTable.GetUniqueColumnPropertyName(pkTableHumanCase, foreignKey, useCamelCase, checkForFkNameClashes, true);
bool fkMakePropNameSingular = (relationship == Relationship.OneToOne);
string fkPropName = pkTable.GetUniqueColumnPropertyName(fkTable.NameHumanCase, foreignKey, useCamelCase, checkForFkNameClashes, fkMakePropNameSingular);
fkCol.col.EntityFk = string.Format("public virtual {0} {1} {2}{3}", pkTable.Name, pkPropName, "{ get; set; }", includeComments ? " // " + foreignKey.ConstraintName : string.Empty);
string manyToManyMapping;
if (foreignKeys.Count > 1)
{
manyToManyMapping = string.Format("c => new {{ {0} }}", string.Join(", ", fkCols.OrderBy(o => o.fkOrdinal).Select(x => "c." + x.col.PropertyNameHumanCase).ToArray()));
}
else
{
manyToManyMapping = string.Format("c => c.{0}", fkCol.col.PropertyNameHumanCase);
}
fkCol.col.ConfigFk = string.Format(" {0};{1}", GetRelationship(relationship, fkCol.col, pkCol, pkPropName, fkPropName, manyToManyMapping, casete), includeComments ? " // " + foreignKey.ConstraintName : string.Empty);
pkTable.AddReverseNavigation(relationship, pkTableHumanCase, fkTable, fkPropName, string.Format("{0}.{1}", fkTable.Name, foreignKey.ConstraintName), collectionType, includeComments);
}
}
public override Tables ReadSchema(Regex tableFilterExclude, Regex columnFilterExclude, bool useCamelCase, bool prependSchemaName, bool includeComments, ExtendedPropertyCommentsStyle includeExtendedPropertyComments, Func <string, string, string> tableRename, string schemaNameFilter, Func <Column, Table, Column> updateColumn)
{
var result = new Tables();
if (Cmd == null)
{
return(result);
}
Cmd.CommandText = TableSQL + IncludeQueryTraceOn9481();
if (Cmd.GetType().Name == "SqlCeCommand")
{
Cmd.CommandText = TableSQLCE;
}
else
{
Cmd.CommandTimeout = 600;
}
using (DbDataReader rdr = Cmd.ExecuteReader())
{
var rxClean = new Regex("^(event|Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
var lastTable = string.Empty;
Table table = null;
while (rdr.Read())
{
string tableName = rdr["TableName"].ToString().Trim();
if (tableFilterExclude != null && tableFilterExclude.IsMatch(tableName))
{
continue;
}
string schema = rdr["SchemaName"].ToString().Trim();
if (schemaNameFilter != null && !schema.Equals(schemaNameFilter, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
if (lastTable != tableName || table == null)
{
// The data from the database is not sorted
table = result.Find(x => x.Name == tableName && x.Schema == schema);
if (table == null)
{
table = new Table
{
Name = tableName,
Schema = schema,
IsView = String.Compare(rdr["TableType"].ToString().Trim(), "View", StringComparison.OrdinalIgnoreCase) == 0,
// Will be set later
HasForeignKey = false,
HasNullableColumns = false
};
tableName = tableRename(tableName, schema);
CodeFirstTools.SchemaName = schema;
table.CleanName = CodeFirstTools.CleanUp(tableName);
table.ClassName = Inflector.MakeSingular(table.CleanName);
string singular = Inflector.MakeSingular(tableName);
table.NameHumanCase = (useCamelCase ? Inflector.ToTitleCase(singular) : singular).Replace(" ", "").Replace("$", "");
//if ((string.Compare(table.Schema, "dbo", StringComparison.OrdinalIgnoreCase) != 0) && prependSchemaName)
// table.NameHumanCase = table.Schema + "_" + table.NameHumanCase;
// Check for table or C# name clashes
if (CodeFirstTools.ReservedKeywords.Contains(table.NameHumanCase) ||
(useCamelCase && result.Find(x => x.NameHumanCase == table.NameHumanCase) != null))
{
table.NameHumanCase += "1";
}
result.Add(table);
}
}
var col = CreateColumn(rdr, rxClean, table, useCamelCase, columnFilterExclude, updateColumn);
if (col != null)
{
table.Columns.Add(col);
}
}
}
// Check for property name clashes in columns
foreach (Column c in result.SelectMany(tbl => tbl.Columns.Where(c => tbl.Columns.FindAll(x => x.PropertyNameHumanCase == c.PropertyNameHumanCase).Count > 1)))
{
c.PropertyNameHumanCase = c.PropertyName;
}
if (includeExtendedPropertyComments != ExtendedPropertyCommentsStyle.None)
{
ReadExtendedProperties(result);
}
ReadUniqueIndexes(result);
foreach (Table tbl in result)
{
tbl.Columns.ForEach(x => x.SetupEntityAndConfig(includeComments, includeExtendedPropertyComments));
}
return(result);
}
public abstract void ReadExtendedProperties(Tables tables);
public abstract void ReadUniqueIndexes(Tables tables);
public abstract void IdentifyForeignKeys(List <ForeignKey> fkList, Tables tables);
public abstract void ProcessForeignKeys(List <ForeignKey> fkList, Tables tables, bool useCamelCase, bool prependSchemaName, string collectionType, bool checkForFkNameClashes, bool includeComments);