public List <ErikEJ.SqlCeScripting.Constraint> GetAllForeignKeys() { var result = new List <ErikEJ.SqlCeScripting.Constraint>(); var dt = cn.GetSchema("ForeignKeys"); for (int i = 0; i < dt.Rows.Count; i++) { var fk = new ErikEJ.SqlCeScripting.Constraint(); fk.ColumnName = dt.Rows[i]["FKEY_FROM_COLUMN"].ToString(); fk.ConstraintName = dt.Rows[i]["CONSTRAINT_NAME"].ToString(); fk.ConstraintTableName = dt.Rows[i]["TABLE_NAME"].ToString(); fk.DeleteRule = dt.Rows[i]["FKEY_ON_DELETE"].ToString(); fk.UniqueColumnName = dt.Rows[i]["FKEY_TO_COLUMN"].ToString(); fk.UniqueConstraintName = "PK_" + dt.Rows[i]["FKEY_TO_TABLE"].ToString(); fk.UniqueConstraintTableName = dt.Rows[i]["FKEY_TO_TABLE"].ToString(); fk.UpdateRule = dt.Rows[i]["FKEY_ON_UPDATE"].ToString(); fk.Columns = new ColumnList(); fk.UniqueColumns = new ColumnList(); result.Add(fk); } return(RepositoryHelper.GetGroupForeingKeys(result, GetAllTableNames())); }
// Contrib from hugo on CodePlex - thanks! public static List<Constraint> GetGroupForeingKeys(List<Constraint> foreignKeys, List<string> allTables) { var groupedForeingKeys = new List<Constraint>(); var uniqueTables = (from c in foreignKeys select c.ConstraintTableName).Distinct().ToList(); int i = 1; foreach (string tableName in uniqueTables) { { var uniqueConstraints = (from c in foreignKeys where c.ConstraintTableName == tableName select c.ConstraintName).Distinct().ToList(); foreach (string item in uniqueConstraints) { string value = item; var constraints = foreignKeys.Where(c => c.ConstraintName.Equals(value, System.StringComparison.Ordinal) && c.ConstraintTableName == tableName).ToList(); if (constraints.Count == 1) { Constraint constraint = constraints[0]; constraint.Columns.Add(constraint.ColumnName); constraint.UniqueColumns.Add(constraint.UniqueColumnName); var found = groupedForeingKeys.Where(fk => fk.ConstraintName == constraint.ConstraintName && fk.ConstraintTableName != constraint.ConstraintTableName).Any(); if (found) { constraint.ConstraintName = constraint.ConstraintName + i.ToString(); i++; } else { var tfound = allTables.Where(ut => ut == constraint.ConstraintName).Any(); if (tfound) { constraint.ConstraintName = constraint.ConstraintName + i.ToString(); i++; } } groupedForeingKeys.Add(constraint); } else { var newConstraint = new Constraint { ConstraintTableName = constraints[0].ConstraintTableName, ConstraintName = constraints[0].ConstraintName, UniqueConstraintTableName = constraints[0].UniqueConstraintTableName, UniqueConstraintName = constraints[0].UniqueConstraintName, DeleteRule = constraints[0].DeleteRule, UpdateRule = constraints[0].UpdateRule, Columns = new ColumnList(), UniqueColumns = new ColumnList() }; foreach (Constraint c in constraints) { newConstraint.Columns.Add(c.ColumnName); newConstraint.UniqueColumns.Add(c.UniqueColumnName); } var found = groupedForeingKeys.Where(fk => fk.ConstraintName == newConstraint.ConstraintName && fk.ConstraintTableName != newConstraint.ConstraintTableName).Any(); if (found) { newConstraint.ConstraintName = newConstraint.ConstraintName + i.ToString(); i++; } groupedForeingKeys.Add(newConstraint); } } } } return groupedForeingKeys; }