System System.Collections.Generic System.Collections.Specialized System.Data System.Data.SqlTypes System.Globalization System.IO System.Linq System.Net System.Security.Cryptography System.Text System.Threading.Tasks void Main() { // Create a new instance of the library with the data context to use. var schemaUpdater = new SchemaUpdater(Util.CurrentDataContext); // Link the classes to tables. schemaUpdater.AddEntity(typeof(Account), typeof(Accounts)); // Commit any updates to the database. schemaUpdater.SubmitUpdates(); } // The conceptual class to modify. class Account { public Guid Id { get; set; } public string Name { get; set; } } class Award { public Guid Id { get; set; } public Guid AccountId { get; set; } public string AwardName { get; set; } } // The code supporting the schema evolution. #region Classes class SchemaUpdater { private List trackedEntities; private DataContext dataContext; private static Dictionary SQLTypeMap = new Dictionary { { typeof(string), "VARCHAR(255)" }, { typeof(Guid), "UNIQUEIDENTIFIER" }, { typeof(bool), "BIT" }, { typeof(int), "INT" }, { typeof(float), "FLOAT" }, { typeof(DateTime), "DATETIME" } }; public SchemaUpdater(DataContext dataContext) { this.dataContext = dataContext; this.trackedEntities = new List(); } public SchemaUpdater AddEntity(Type conceptualType, Type persistantType) { this.trackedEntities.Add(new EntityPair(conceptualType, persistantType)); return this; } public SchemaUpdater SubmitUpdates() { "Beginning update...".Dump(); this.trackedEntities.ForEach(e => UpdateEntity(e)); "\nUpdate complete.".Dump(); return this; } private void UpdateEntity(EntityPair entityPair) { string.Format("\n[{0}]:\n", entityPair.PersistantEntity.Name).Dump(); // Generate a list of their fields. var conceptualFields = entityPair.ConceptualEntity.GetProperties().Select(p => new Field(p.Name, p.PropertyType)); var persistantFields = entityPair.PersistantEntity.GetFields().Select(f => new Field(f.Name, f.FieldType)); // Create some useful models. Relation conceptual = new Relation("Conceptual", conceptualFields); Relation persistant = new Relation("Persistant", persistantFields); // Compare the two list of fields. var missingColumns = conceptual.Diff(persistant); if (missingColumns.Count() != 0) { foreach (var column in missingColumns) { this.CreateColumn(entityPair.PersistantEntity.Name, column.Name, SQLTypeMap[column.FieldType]); string.Format("* Added new column [{0}].[{1}] of type {2}.", entityPair.PersistantEntity.Name, column.Name, SQLTypeMap[column.FieldType]).Dump(); } } else { "* No columns to add.".Dump(); } // Identify which columns can be deleted. persistant.Diff(conceptual).ForEach(c => string.Format("* Column '{0}' can be deleted.", c.Name).Dump()); } private void CreateColumn(string tableName, string columnName, string sqlType) { string sqlStatment = string.Format(@"ALTER TABLE [{0}] ADD [{1}] {2};", tableName, columnName, sqlType); this.RunSQL(sqlStatment); } private void RunSQL(string sqlStatment) { this.dataContext.ExecuteCommand(sqlStatment); } } class Relation { public string Name { get; set; } public IEnumerable Fields { get; set; } public Relation (String name, IEnumerable fields) { this.Name = name; this.Fields = fields; } public IEnumerable Diff (Relation input) { return this.Fields.Where(f => input.Fields.Contains(f) == false); } } class Field : IEquatable { public string Name { get; set; } public Type FieldType { get; set; } public Field (String name, Type fieldType) { this.Name = name; this.FieldType = fieldType; } // Fields are equal if their names and field types are equal. public bool Equals (Field field) { // Check whether the fields properties are equal. return this.Name == field.Name && this.FieldType == field.FieldType; } } #endregion #region Structs public struct EntityPair { public Type ConceptualEntity; public Type PersistantEntity; public EntityPair(Type conceptualType, Type persistantType) { ConceptualEntity = conceptualType; PersistantEntity = persistantType; } } #endregion