/// <summary> Creates the model. </summary> /// /// <returns> The new model. </returns> private static DbCompiledModel CreateModel() { // Create a model + register types to it. var modelBuilder = new DbModelBuilder(); modelBuilder.Conventions.Remove <PluralizingEntitySetNameConvention>(); //For generating Entities without 's' at the end DbCompiledModel compiledDatabaseModel = null; try { #region Read Schema and Add to Model // https://www.nuget.org/packages/DatabaseSchemaReader/ using (var dbReader = new DatabaseReader(cConnectionStringSettings.ConnectionString, cConnectionStringSettings.ProviderName)) { var schema = dbReader.ReadAll(); var dynamicClassFactory = new DynamicClassFactory(); #region Read Tables var tableBuildHelperList = new List <TableBuildHelper>(); List <string> failedTableColumns = new List <string>(); foreach (var table in schema.Tables) { try { var tableBuildHelper = new TableBuildHelper { Name = table.Name, Properties = new List <TablePropertyBuildHelper>() }; #region Field properties foreach (var col in table.Columns) { if (col.DataType == null) { failedTableColumns.Add(string.Format("{0} - {1}", table.Name, col.ToString())); } else { Type colType = Type.GetType(col.DataType.NetDataType); if (col.Nullable) { if (col.DataType.IsInt) { colType = typeof(Nullable <int>); } else if (col.DataType.IsDateTime) { colType = typeof(Nullable <DateTime>); } else if (col.DataType.IsFloat) { colType = typeof(Nullable <float>); } else if (col.DataType.IsNumeric) { colType = typeof(Nullable <decimal>); } else if (col.DataType.TypeName == "datetimeoffset") { colType = typeof(Nullable <DateTimeOffset>); } else if (col.DataType.NetDataTypeCSharpName == "bool") { colType = typeof(Nullable <bool>); } } //Sequense logic string sequenceScript = null; if (col.IsPrimaryKey && !string.IsNullOrEmpty(col.DefaultValue) && col.DefaultValue.StartsWith("(NEXT VALUE FOR")) { sequenceScript = col.DefaultValue.Substring(1, col.DefaultValue.Length - 2); } FieldPropertyData fieldPropertyData = new FieldPropertyData() { IsPrimaryKey = col.IsPrimaryKey, IsForeignKey = col.IsForeignKey, Order = table.Columns.IndexOf(col) + 1, Nullable = col.Nullable, Type = colType, MaxLength = col.Length, IsComputedID = col.IsPrimaryKey && col.IdentityDefinition == null, SequenceScript = sequenceScript, ColumnName = col.Name }; string name = col.Name; while (table.Name == name || tableBuildHelper.Properties.Exists(p => p.Name == name)) { name = name + "1"; } var tablePropertyBuildHelper = new TablePropertyBuildHelper { Name = name, Data = fieldPropertyData }; tableBuildHelper.Properties.Add(tablePropertyBuildHelper); } } #endregion //Make all existing foreign keys as primary key if entity has no primary key if (tableBuildHelper.Properties.FirstOrDefault(x => ((FieldPropertyData)x.Data).IsPrimaryKey) == null) { var foreignRows = tableBuildHelper.Properties.Where(x => (((FieldPropertyData)x.Data).IsForeignKey)).ToList(); foreignRows.ForEach(p => ((FieldPropertyData)p.Data).IsPrimaryKey = true); } var tableTypeBuilder = CreateTypeBuilder(dynamicClassFactory, table.SchemaOwner, table.Name, null); tableBuildHelper.TypeBuilder = tableTypeBuilder; tableBuildHelperList.Add(tableBuildHelper); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError(string.Format("CreateModel: table '{0}'", table.Name), exception); } } #region Navigation properties foreach (var table in schema.Tables) { #region Foreign Keys foreach (var foreignKey in table.ForeignKeys) { try { var tableBuildHelper = tableBuildHelperList.Find(table.Name); var columnType = tableBuildHelperList.Find(foreignKey.RefersToTable).TypeBuilder; //Foreign Key property { ForeignKeyPropertyData foreignKeyPropertyData = new ForeignKeyPropertyData() { Type = columnType, ColumnName = tableBuildHelper.Properties.Find(foreignKey.Columns[0]).Name }; string propName = foreignKey.RefersToTable; while (table.Name == propName || tableBuildHelper.Properties.Exists(propName)) { propName = propName + "1"; } var tablePropertyBuildHelper = new TablePropertyBuildHelper { Name = propName, Data = foreignKeyPropertyData }; tableBuildHelper.Properties.Add(tablePropertyBuildHelper); } var fkTableBuildHelper = tableBuildHelperList.Find(foreignKey.RefersToTable); var fkColumnType = typeof(ICollection <>).MakeGenericType(tableBuildHelper.TypeBuilder); //if (table.Name == "BatchProductionRecord") //foreignKey.Columns[0] == "Events") //Inverse property { InversePropertyData inversePropertyData = new InversePropertyData() { Type = fkColumnType, ColumnName = tableBuildHelper.Properties.Last().Name //propName }; string propName = foreignKey.TableName; while (foreignKey.RefersToTable == propName || fkTableBuildHelper.Properties.Exists(propName)) { propName = propName + "1"; } var tablePropertyBuildHelper = new TablePropertyBuildHelper { Name = propName, Data = inversePropertyData }; fkTableBuildHelper.Properties.Add(tablePropertyBuildHelper); } } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError(string.Format("CreateModel: foreignKey '{0}'", foreignKey.Name), exception); } } #endregion } #endregion #region Create properties and table types from type builder and add to DB model foreach (var table in tableBuildHelperList) { foreach (var property in table.Properties) { try { dynamicClassFactory.CreateProperty(table.TypeBuilder, new KeyValuePair <string, DynamicPropertyData>(property.Name, property.Data), null); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError(string.Format("CreateModel: property '{0}'", property.Name), exception); } } try { var tableType = table.TypeBuilder.CreateType(); var entity = modelBuilder.Entity(tableType); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } } #endregion #endregion #region Read Views List <string> failedViewColumns = new List <string>(); foreach (var view in schema.Views) { try { AddViewToModel(modelBuilder, dynamicClassFactory, failedViewColumns, view); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError(string.Format("CreateModel: view '{0}'", view.Name), exception); } } #endregion #region Read Actions AddActionsToModel(modelBuilder, schema, dynamicClassFactory); #endregion } #endregion #region Add Metadata object for user rules custom metadata try { var metadataObject = modelBuilder.Entity <DynamicMetadataObject>(); metadataObject.HasKey(a => a.Name); metadataObject.Property(a => a.Type); metadataObject.Property(a => a.Schema); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } #endregion #region Add service info object to model try { var serviceInfoObject = modelBuilder.Entity <DynamicServiceInfoObject>(); serviceInfoObject.HasKey(a => a.IISVersion); serviceInfoObject.Property(a => a.TargetFramework); serviceInfoObject.Property(a => a.AppDomainAppPath); serviceInfoObject.Property(a => a.AssemblyDictionary); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } #endregion var databaseModel = modelBuilder.Build(new System.Data.SqlClient.SqlConnection(cConnectionStringSettings.ConnectionString)); compiledDatabaseModel = databaseModel.Compile(); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); throw; } return(compiledDatabaseModel); }
/// <summary> Creates the model. </summary> /// /// <returns> The new model. </returns> private static DbCompiledModel CreateModel() { // Create a model + register types to it. var modelBuilder = new DbModelBuilder(); modelBuilder.Conventions.Remove<PluralizingEntitySetNameConvention>(); //For generating Entities without 's' at the end DbCompiledModel compiledDatabaseModel = null; try { #region Read Schema and Add to Model // https://www.nuget.org/packages/DatabaseSchemaReader/ using (var dbReader = new DatabaseReader(cConnectionStringSettings.ConnectionString, cConnectionStringSettings.ProviderName, DynamicContext.cDefaultSchemaName)) { var schema = dbReader.ReadAll(); var dynamicClassFactory = new DynamicClassFactory(); #region Read Tables var tableBuildHelperList = new List<TableBuildHelper>(); List<string> failedTableColumns = new List<string>(); foreach (var table in schema.Tables) { try { var tableBuildHelper = new TableBuildHelper(); tableBuildHelper.Name = table.Name; tableBuildHelper.Properties = new List<TablePropertyBuildHelper>(); #region Field properties foreach (var col in table.Columns) { if (col.DataType == null) { failedTableColumns.Add(string.Format("{0} - {1}", table.Name, col.ToString())); } else { Type colType = Type.GetType(col.DataType.NetDataType); if (col.Nullable) { if (col.DataType.IsInt) { colType = typeof(Nullable<int>); } else if (col.DataType.IsDateTime) { colType = typeof(Nullable<DateTime>); } else if (col.DataType.IsFloat) { colType = typeof(Nullable<float>); } else if (col.DataType.IsNumeric) { colType = typeof(Nullable<decimal>); } else if (col.DataType.TypeName == "datetimeoffset") { colType = typeof(Nullable<DateTimeOffset>); } else if (col.DataType.NetDataTypeCSharpName == "bool") { colType = typeof(Nullable<bool>); } } //Sequense logic string sequenceScript = null; if (col.IsPrimaryKey && !string.IsNullOrEmpty(col.DefaultValue) && col.DefaultValue.StartsWith("(NEXT VALUE FOR")) sequenceScript = col.DefaultValue.Substring(1, col.DefaultValue.Length - 2); FieldPropertyData fieldPropertyData = new FieldPropertyData() { IsPrimaryKey = col.IsPrimaryKey, IsForeignKey = col.IsForeignKey, Order = table.Columns.IndexOf(col) + 1, Nullable = col.Nullable, Type = colType, MaxLength = col.Length, IsComputedID = col.IsPrimaryKey && col.IdentityDefinition == null, SequenceScript = sequenceScript, ColumnName = col.Name }; string name = col.Name; while (table.Name == name || tableBuildHelper.Properties.Exists(p => p.Name == name)) name = name + "1"; var tablePropertyBuildHelper = new TablePropertyBuildHelper(); tablePropertyBuildHelper.Name = name; tablePropertyBuildHelper.Data = fieldPropertyData; tableBuildHelper.Properties.Add(tablePropertyBuildHelper); } } #endregion //Make all existing foreign keys as primary key if entity has no primary key if (tableBuildHelper.Properties.FirstOrDefault(x => ((FieldPropertyData)x.Data).IsPrimaryKey) == null) { var foreignRows = tableBuildHelper.Properties.Where(x => (((FieldPropertyData)x.Data).IsForeignKey)).ToList(); foreignRows.ForEach(p => ((FieldPropertyData)p.Data).IsPrimaryKey = true); } var tableTypeBuilder = CreateTypeBuilder(dynamicClassFactory, table.Name, null); tableBuildHelper.TypeBuilder = tableTypeBuilder; tableBuildHelperList.Add(tableBuildHelper); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } } #region Navigation properties foreach (var table in schema.Tables) { #region Foreign Keys foreach (var foreignKey in table.ForeignKeys) { try { var tableBuildHelper = tableBuildHelperList.Find(table.Name); var columnType = tableBuildHelperList.Find(foreignKey.RefersToTable).TypeBuilder; //Foreign Key property { ForeignKeyPropertyData foreignKeyPropertyData = new ForeignKeyPropertyData() { Type = columnType, ColumnName = tableBuildHelper.Properties.Find(foreignKey.Columns[0]).Name }; string propName = foreignKey.RefersToTable; while (table.Name == propName || tableBuildHelper.Properties.Exists(propName)) { propName = propName + "1"; } var tablePropertyBuildHelper = new TablePropertyBuildHelper(); tablePropertyBuildHelper.Name = propName; tablePropertyBuildHelper.Data = foreignKeyPropertyData; tableBuildHelper.Properties.Add(tablePropertyBuildHelper); } var fkTableBuildHelper = tableBuildHelperList.Find(foreignKey.RefersToTable); var fkColumnType = typeof(ICollection<>).MakeGenericType(tableBuildHelper.TypeBuilder); //if (table.Name == "BatchProductionRecord") //foreignKey.Columns[0] == "Events") //Inverse property { InversePropertyData inversePropertyData = new InversePropertyData() { Type = fkColumnType, ColumnName = tableBuildHelper.Properties.Last().Name //propName }; string propName = foreignKey.TableName; while (foreignKey.RefersToTable == propName || fkTableBuildHelper.Properties.Exists(propName)) { propName = propName + "1"; } var tablePropertyBuildHelper = new TablePropertyBuildHelper(); tablePropertyBuildHelper.Name = propName; tablePropertyBuildHelper.Data = inversePropertyData; fkTableBuildHelper.Properties.Add(tablePropertyBuildHelper); } } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } } #endregion } #endregion #region Create properties and table types from type builder and add to DB model foreach (var table in tableBuildHelperList) { foreach (var property in table.Properties) { try { dynamicClassFactory.CreateProperty(table.TypeBuilder, new KeyValuePair<string, DynamicPropertyData>(property.Name, property.Data), null); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } } try { var tableType = table.TypeBuilder.CreateType(); var entity = modelBuilder.Entity(tableType); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } } #endregion #endregion #region Read Views List<string> failedViewColumns = new List<string>(); foreach (var view in schema.Views) { try { AddViewToModel(modelBuilder, dynamicClassFactory, failedViewColumns, view); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } } #endregion #region Read Actions AddActionsToModel(modelBuilder, schema, dynamicClassFactory); #endregion } #endregion #region Add Metadata object for user rules custom metadata try { var metadataObject = modelBuilder.Entity<DynamicMetadataObject>(); metadataObject.HasKey(a => a.Name); metadataObject.Property(a => a.Type); metadataObject.Property(a => a.Schema); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } #endregion #region Add service info object to model try { var serviceInfoObject = modelBuilder.Entity<DynamicServiceInfoObject>(); serviceInfoObject.HasKey(a => a.IISVersion); serviceInfoObject.Property(a => a.TargetFramework); serviceInfoObject.Property(a => a.AppDomainAppPath); serviceInfoObject.Property(a => a.AssemblyDictionary); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); } #endregion var databaseModel = modelBuilder.Build(new System.Data.SqlClient.SqlConnection(cConnectionStringSettings.ConnectionString)); compiledDatabaseModel = databaseModel.Compile(); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("CreateModel", exception); throw exception; } return compiledDatabaseModel; }