public static List <Table> GetTables(
DataTable columns,
DataTable indexColumns,
DataTable indexes,
DataTable schemas,
DataTable systables)
{
var tables = new List <Table>();
var skipList = TemplateConstants.TablesToSkip
.Split(',')
.Select(a => a.Trim().ToLower())
.ToList();
// get tables
systables.Rows
.OfType <DataRow>()
.Where(a => !skipList.Any(s => Regex.IsMatch(a["name"].ToString(), @"^" + s + "$", RegexOptions.IgnoreCase)))
.ToList()
.ForEach(a =>
{
var t = new Table
{
GeneratedName = EntityDesignUtil.Singularize(
NamingUtil.GetTableName(a["name"].ToString())),
ObjectId = (int)a["object_id"],
SchemaName = GetSchemaNameById(schemas, (int)a["schema_id"]),
TableName = a["name"].ToString()
};
tables.Add(t);
});
// get indexes for each table
tables.ForEach(a =>
{
// get indexes for table
// for each index, create index
indexes.Select("object_id = " + a.ObjectId +
" AND type IN(1, 2)" +
" AND is_primary_key = 0",
"index_id ASC")
.ToList()
.ForEach(b =>
{
var index = new Index
{
IndexId = Convert.ToInt32(b["index_id"]),
Name = b["name"].ToString()
};
indexColumns
.Select("object_id = " + a.ObjectId +
" AND index_id = " + index.IndexId,
"index_column_id ASC")
.ToList()
.ForEach(c =>
{
index.Columns.Add(
columns
.Select("object_id = " + a.ObjectId +
" AND column_id = " + c["column_id"].ToString())
.Select(d => d["name"].ToString())
.FirstOrDefault());
});
a.Indexes.Add(index);
});
a.Indexes =
a.Indexes
.OrderBy(b => b.Name)
.ToList();
});
return(tables
.OrderBy(a => a.TableName)
.ToList());
}
public static string GenerateTableClassFileContent(
Table t,
string modelNamespace)
{
var sb = new StringBuilder();
// TODO: move using statements to AddUsings
//var hasDateColumns =
// t.Columns.Any(a => CreateDateFieldNames.ContainsKey(a.Name))
// || t.Columns.Any(a => ModifiedDateFieldNames.ContainsKey(a.Name));
//var hasDateRangeColumns =
// t.Columns.Any(a => StartDateFieldNames.ContainsKey(a.Name))
// || t.Columns.Any(a => EndDateFieldNames.ContainsKey(a.Name));
//var hasIsActiveColumns =
// t.Columns.Any(a => IsActiveFieldNames.ContainsKey(a.Name));
//var hasDeactivatedDateColumns =
// t.Columns.Any(a => DeactivatedDateFieldNames.ContainsKey(a.Name));
var hasKeyColumns = t.PrimaryKeyColumns != null &&
t.PrimaryKeyColumns.Count > 0;
var hasMultipleKeyColumns = t.PrimaryKeyColumns != null &&
t.PrimaryKeyColumns.Count > 1;
var interfaces = new List <string>();
//if (hasDateColumns && !ExcludePopulateDatesInterface.ContainsKey(t.TableName))
// interfaces.Add("IPopulateDates");
//if (hasDateRangeColumns && hasDeactivatedDateColumns
// && TemplateConstants
// .ExcludeDateRangeInterface
// .Split(',')
// .All(x => x != t.TableName))
//{
// interfaces.Add("IDateRange");
//}
//if (!hasDateRangeColumns && hasIsActiveColumns
// && TemplateConstants
// .ExcludeDateRangeInterface
// .Split(',')
// .All(x => x != t.TableName))
//{
// interfaces.Add("IDeletable");
//}
var subClass = interfaces.Contains("IDateRange") ? TemplateConstants.ModelsSubclassEffDate : TemplateConstants.ModelsSubclass;
var needsColumnAttribute = t.Columns
.Any(a => a.GeneratedName != a.Name ||
hasMultipleKeyColumns ||
a.Type == "varchar");
var needsKeyAttribute = hasKeyColumns;
var needsMaxLengthAttribute = t.Columns
.Any(a => a.ClrType == "string" &&
a.Type != "text" &&
a.Length != -1);
var needsModelTableAttribute = t.Columns
.Any(a => ExcludeFieldsFromModelTable.ContainsKey(
$"{t.TableName}.{a.Name}"));
var needsRequiredAttribute = t.Columns
.Any(a => a.ClrType == "string" &&
!a.IsNullable);
var needsTableAttribute = t.TableName != t.GeneratedName;
// ReSharper disable once InconsistentNaming
var needsUsingDGDeanModelsBase = !TablesToOptOutOfSubClass
.ContainsKey(t.TableName) &&
subClass != TemplateConstants.ModelsSubclassEffDate;
// ReSharper disable once InconsistentNaming
var needsUsingDGDeanModelsAttributes =
!string.IsNullOrWhiteSpace(t.ActiveColumnName) &&
subClass != TemplateConstants.ModelsSubclassEffDate;
const bool needsUsingNewtonsoftJson = false;
var needsUsingModelsAttributes = needsModelTableAttribute;
var needsUsingModelsInterfaces = interfaces.Count > 0;
var needsUsingSystem = t.Columns
.Where(a => subClass != TemplateConstants.ModelsSubclassEffDate ||
!ModelsSubclassEffDateExcludedColumns.ContainsKey(a.Name))
.Any(a => GetClrType(a.Type) == "DateTime" ||
GetClrType(a.Type) == "DateTimeOffset" ||
GetClrType(a.Type) == "Guid" ||
GetClrType(a.Type) == "Int16" ||
GetClrType(a.Type) == "Int64");
var needsUsingSystemCollectionsGeneric =
t.NavigationProperties
.Any(a => a.Type.StartsWith("ICollection<"))
// required for metadata properties
|| true;
var needsUsingSystemComponentModelDataAnnotations =
needsKeyAttribute ||
needsMaxLengthAttribute ||
needsRequiredAttribute ||
t.Columns.Any();
var needsUsingSystemComponentModelDataAnnotationsSchema =
needsColumnAttribute
// || needsDatabaseGeneratedAttribute
// || needsForeignKeyAttribute
// || needsInversePropertyAttribute
// || needsNotMappedAttribute
|| needsTableAttribute;
const bool needsUsingSystemRuntimeSerialization = false;
var needsUsingModelsDbCustom = interfaces.Contains("IDateRange");
AddGeneratedDisclaimer(sb);
AddUsing(
needsUsingDGDeanModelsAttributes,
sb,
"DGDean.Models.Attributes");
AddUsing(
needsUsingDGDeanModelsBase,
sb,
"DGDean.Models.Base");
AddUsing(
needsUsingNewtonsoftJson,
sb,
"Newtonsoft.Json");
AddUsing(
needsUsingModelsAttributes,
sb,
"DB.Models.Core.Attributes");
AddUsing(
needsUsingModelsInterfaces,
sb,
"DB.Models.Core.Interfaces");
AddUsing(
needsUsingModelsDbCustom,
sb,
"DB.Models.Core.DB.Custom");
AddUsing(needsUsingSystem, sb, "System");
AddUsing(
needsUsingSystemCollectionsGeneric,
sb,
"System.Collections.Generic");
AddUsing(
needsUsingSystemComponentModelDataAnnotations,
sb,
"System.ComponentModel.DataAnnotations");
AddUsing(
needsUsingSystemComponentModelDataAnnotationsSchema,
sb,
"System.ComponentModel.DataAnnotations.Schema");
AddUsing(
needsUsingSystemRuntimeSerialization,
sb,
"System.Runtime.Serialization");
var className = EntityDesignUtil.Singularize(
NamingUtil.GetTableName(t.TableName));
//var createDateProperty = hasDateColumns
// ? t.Columns
// .FirstOrDefault(a => CreateDateFieldNames.ContainsKey(a.Name))
// ?.Name ?? ""
// : "";
//var modifiedDateProperty = hasDateColumns
// ? t.Columns
// .FirstOrDefault(a => ModifiedDateFieldNames.ContainsKey(a.Name))
// ?.Name ?? ""
// : "";
//var startDateProperty = hasDateRangeColumns
// ? t.Columns
// .FirstOrDefault(a => StartDateFieldNames.ContainsKey(a.Name))
// ?.Name ?? ""
// : "";
//var endDateProperty = hasDateRangeColumns
// ? t.Columns
// .FirstOrDefault(a => EndDateFieldNames.ContainsKey(a.Name))
// ?.Name ?? ""
// : "";
//var dateRangeModelName =
// hasDateRangeColumns
// && TemplateConstants
// .ExcludeDateRangeInterface
// .Split(',')
// .All(x => x != t.TableName)
// ? Regex.Replace(className, @"Model$", "")
// : "";
sb.Append(
"\r\n" +
"namespace " + modelNamespace + "\r\n" +
"{\r\n" +
GetClassAttributes(t, className) +
GetClassStatement(
t.TableName,
className,
subClass,
interfaces,
//createDateProperty,
//modifiedDateProperty,
//startDateProperty,
//endDateProperty,
//dateRangeModelName,
GetNormalProperties(t, subClass),
GetNavigationProperties(t)) +
"}\r\n");
return(sb.ToString());
}
private static string GetNormalPropertyAttributes(
Table t,
Column c)
{
// need to support the following attributes
// Display (needed for automatically generated form field labels)
// ForeignKey (needed if foreign key column name doesn't match nav prop name + "Id")
// Index (needed if index that's not primary key exists)
// Inverse (needed for multiple relationships between classes)
// NotMapped (needed if any nav properties)
var keyNeeded = c.IsKey;
var key = keyNeeded ? "Key" : "";
var commaNeeded = keyNeeded;
var activeNeeded = !string.IsNullOrWhiteSpace(t.ActiveColumnName) &&
c.Name == t.ActiveColumnName;
var active = activeNeeded
? (commaNeeded ? ", " : "") + "Active(" +
(t.ActiveColumnValue ? "true" : "false") + ")"
: "";
commaNeeded = commaNeeded || activeNeeded;
var columnMultipleKeyCols = c.IsKey &&
t.PrimaryKeyColumns.Count > 1;
var columnNameMismatch = c.GeneratedName != c.Name;
var columnVarchar = c.Type == "varchar";
var columnNeeded = columnMultipleKeyCols ||
columnNameMismatch ||
columnVarchar;
var column = columnNeeded
? (commaNeeded ? ", " : "") + "Column(" +
// name
(columnNameMismatch
? "\"" + c.Name + "\""
: "") +
// order
(columnMultipleKeyCols
? (columnNameMismatch ? ", " : "") +
"Order = " + (t.PrimaryKeyColumns.IndexOf(c.Name) + 1)
: "") +
// typename
(columnVarchar
? (columnNameMismatch || columnMultipleKeyCols ? ", " : "") +
"TypeName = \"varchar\""
: "") +
")"
: "";
commaNeeded = commaNeeded || columnNeeded;
var databaseGeneratedNeeded = c.IsIdentity || c.IsComputed;
var databaseGenerated = databaseGeneratedNeeded
? (commaNeeded ? ", " : "") +
"DatabaseGenerated(DatabaseGeneratedOption." +
(c.IsIdentity ? "Identity" : "Computed") + ")"
: "";
commaNeeded = commaNeeded || databaseGeneratedNeeded;
// for now, generate the display attribute for every field
var display = (commaNeeded ? ", " : "") +
"Display(Name = \"" + NamingUtil.GetDisplayName(c.Name) + "\")";
commaNeeded = true;
var editableNeeded = EditableColumns.ContainsKey($"{t.TableName}.{c.Name}");
var editable = editableNeeded
? ", " + "Editable(true)"
: "";
var fk = keyNeeded
? t.ForeignKeys
.FirstOrDefault(a => a.Columns.Any(b => b.SourceColumn == c.Name))
: null;
var foreignKeyNeeded =
keyNeeded &&
fk != null;
var srcCol = fk != null
? fk.Columns[0].SourceColumn
: "";
var type = fk != null
? EntityDesignUtil.Singularize(
NamingUtil.GetTableName(fk.DestinationTableName))
: "";
// TODO: need to support list of source columns
var foreignKey = foreignKeyNeeded
? ", " + "ForeignKey(\"" +
(SourceNavPropNames.TryGetValue(
fk.SourceTableName + "." + string.Join("|", fk.Columns.Select(a => a.SourceColumn)),
out var sourcePropName)
? sourcePropName
: srcCol.ToLower().EndsWith("id") && srcCol.Length > 2
? srcCol.Substring(0, srcCol.Length - 2)
: type) +
"\")"
: "";
var indexNeeded = t.Indexes
.Any(a => a.Columns.Contains(c.Name));
var index = "";
t.Indexes
.Where(a => a.Columns.Contains(c.Name))
.ToList()
.ForEach(a =>
{
index +=
(string.IsNullOrWhiteSpace(index)
? ""
: ", ") +
"Index(\"" + a.Name + "\"" +
(a.Columns.Count > 1
? ", " + (a.Columns.IndexOf(c.Name) + 1).ToString()
: "") +
")";
});
index = string.IsNullOrWhiteSpace(index)
? ""
: (commaNeeded ? ", " : "") + index;
commaNeeded = commaNeeded || indexNeeded;
var maxLengthNeeded = c.ClrType == "string" &&
c.Type != "text" &&
c.Length > -1;
var maxLength = maxLengthNeeded
? (commaNeeded ? ", " : "") +
"MaxLength(" + (c.Type == "varchar" || c.Type == "char" ? c.Length : c.Length / 2) + ")"
: "";
commaNeeded = commaNeeded || maxLengthNeeded;
var modelTableNeeded = ExcludeFieldsFromModelTable.ContainsKey(
$"{t.TableName}.{c.Name}");
var modelTable = modelTableNeeded
? (commaNeeded ? ", " : "") +
"ModelTable(true)"
: "";
commaNeeded = commaNeeded || modelTableNeeded;
var requiredNeeded = c.ClrType == "string" && !c.IsNullable && c.DefaultValue == null;
var required = requiredNeeded
? (commaNeeded ? ", " : "") + "Required(AllowEmptyStrings = true)"
: "";
var attrNeeded = commaNeeded || requiredNeeded;
var attr = attrNeeded
? "["
+ key
+ active
+ column
+ databaseGenerated
+ display
+ editable
+ foreignKey
+ index
+ maxLength
+ modelTable
+ required
+ "]"
: "";
return(attr);
}
private static IEnumerable <NavigationProperty> GenerateNavigationProperties(
DataTable columnsTable,
DataTable foreignKeyColumnsTable,
DataTable foreignKeysTable,
DataTable tablesTable,
Table table,
IReadOnlyCollection <Table> tables)
{
var props = new List <NavigationProperty>();
// get foreign keys
var foreignKeys = DbUtil.GetForeignKeysByTableId(
columnsTable,
foreignKeysTable,
foreignKeyColumnsTable,
tablesTable,
table.ObjectId,
table.TableName);
// for each foreign key,
foreignKeys.ForEach(a =>
{
var isDestinationForeignKeyAllNonNullable =
IsDestinationNullable(a, columnsTable);
var isSourceForeignKeyAlsoPrimaryKey =
IsSourcePrimaryKey(table, a);
var type = EntityDesignUtil.Singularize(
NamingUtil.GetTableName(a.DestinationTableName));
var srcCol = a.Columns[0].SourceColumn;
var prop = new NavigationProperty
{
DestinationMultiplicity =
// Zero: if any source foreign key columns are nullable
!isDestinationForeignKeyAllNonNullable
? Multiplicity.Zero
// One: not Zero
: Multiplicity.One,
ForeignKey = a,
// *-1, 1-0/1
Name = SourceNavPropNames.TryGetValue(
a.SourceTableName + "." + string.Join("|", a.Columns.Select(b => b.SourceColumn)),
out var sourcePropName)
? sourcePropName
: srcCol.ToLower().EndsWith("id") && srcCol.Length > 2
? srcCol.Substring(0, srcCol.Length - 2)
: type,
SourceMultiplicity = isSourceForeignKeyAlsoPrimaryKey
? Multiplicity.One
: Multiplicity.Many,
Type = type
};
// only add if not in NavigationPropertiesToSkip
if (!NavPropsToSkip.ContainsKey(table.GeneratedName + "." + prop.Name))
{
props.Add(prop);
}
// get destination table
if (tables == null)
{
return;
}
{
var destTable = tables
.FirstOrDefault(b => b.TableName == a.DestinationTableName);
type = EntityDesignUtil.Singularize(
NamingUtil.GetTableName(a.SourceTableName));
var destProp = new NavigationProperty
{
DestinationMultiplicity = prop.SourceMultiplicity,
ForeignKey = a,
InverseProperty = prop.Name,
Name = DestNavPropNames.TryGetValue(
a.SourceTableName + "." + a.Columns[0].SourceColumn,
out var destPropName)
? destPropName
: prop.SourceMultiplicity == Multiplicity.Many
? EntityDesignUtil.Pluralize(type)
: type,
SourceMultiplicity = prop.DestinationMultiplicity,
Type = prop.SourceMultiplicity == Multiplicity.Many
? "ICollection<" + type + ">"
: type
};
if (destTable != null &&
!NavPropsToSkip.ContainsKey(destTable.GeneratedName + "." + destProp.Name))
{
destTable.NavigationProperties.Add(destProp);
}
}
});
// var isManyToMany = AreAllColumnsForeignKeys
// The relationship is many-to-many only if the
// relationship table only contains primary keys
// of both entities, and no other fields.
// 0, 1, *: 0-1, 1-0, 1->1, 1<-1, 0-*, 1-*, *-0, *-1, *-*
// if 0-1,
// add single, non-nullable navigation property
// of foreign key destination table class,
// and ensure foreign key destination table class
// has non-required navigation property to current table class
// if 1-0,
// do nothing, as non-required navigation property will be added
// when processing other side of foreign key relationship
// if 1->1,
// add single, non-nullable navigation property
// of foreign key destination table class,
// and ensure foreign key destination table class
// has required navigation property to current table class
// if 1<-1,
// required navigation property will be added
// when processing other side of foreign key relationship
// if 0-*,
// add public virtual ICollection of
// foreign key destination table class,
// and ensure foreign key destination table class
// has non-required navigation property to current table class
// if 1-*,
// add public virtual ICollection
// of foreign key destination table class,
// and ensure foreign key destination table class
// has required navigation property to current table class
// if *-0,
// do nothing, as non-required navigation property will be added
// when processing other side of foreign key relationship
// if *-1,
// do nothing, as required navigation property will be added
// when processing other side of foreign key relationship
// if *-*,
// well, there are no such tables, according to the
// Entity Framework definition, which is that all columns
// in the join table being both in the join table primary key,
// and a foreign key pointing from the join table to one of the
// joined tables.
return(props);
}
