Following on from my previous question, I think my Entity Framework model code is now complete. First I'll provide a quick overview of the program - everything above the next horizontal rule can be assumed to be 'fixed' & not changeable by me.
Because this is kinda long, I'm going to break this into 2 questions, this one will focus on the Model, the other will focus on the Controller (that's over here)
I'm writing a data dictionary app, and have written a project named Model, using Entity Framework 6, to represent the tables on a Sql Server. The tables are as follows:
- SysData.Database, SysData.Schema, SysData.Table,SysData.Column - these tables contain (tidied) copies of the contents of sys.databases, sys.tables etc. They are refreshed by the server every night, and represent a current (near enough) reflection of all the databases, schemas, etc on the server. They contain field(s) to represent a complete unique identifier (The Schema table has DatabaseID & SchemaID for eg), and also fields containing the system metadata (ColumnName, TypeName, etc)
- Dictionary.Database, Dictionary.Schema, Dictionary.Table, Dictionary.Column - these tables contain the same fields as the SysData tables, but have additional fields that record descriptive information entered into my application (for eg Description). These tables do not automatically refresh - one requirement of the app is to present differences between SysData and Dictionary tables and allow the user to chose a relevant action.
- Compare.Database, Compare.Schema, Compare.Table, Compare.Column - these views compare the contents, at field value level, of the respective SysData and Dictionary tables, and return the IDs of the records that have changed. An additional field called ChangeType states "VALUE" (where the metadata of a record has changed), "NEW" (where a record exists in SysData but not in Dictionary), or "DELETED" (where a record exists in Dictionary but not in SysData). These views only contain the ID fields and ChangeType column.
In my Model project, I have generated 3 Entity Framework models. With a bit of name-tweaking and namespace-organising, they are organised as follows:
namespace Model {
namespace Dictionary {
public class Context : DbContext; //EF-provided DbContext-extended class
public class Database; //EF-provided class to represent a Dictionary.Database record
public class Schema; //EF-provided class to represent a Dictionary.Schema record
public class Table; //EF-provided class to represent a Dictionary.Table record
public class Column; //EF-provided class to represent a Dictionary.Column record
}
namespace SysData {
public class Context : DbContext; //EF-provided DbContext-extended class
public class Database; //EF-provided class to represent a SysData.Database record
public class Schema; //EF-provided class to represent a SysData.Schema record
public class Table; //EF-provided class to represent a SysData.Table record
public class Column; //EF-provided class to represent a SysData.Column record
}
namespace Compare {
public class Context : DbContext; //EF-provided DbContext-extended class
public class Database; //EF-provided class to represent a Compare.Database record
public class Schema; //EF-provided class to represent a Compare.Schema record
public class Table; //EF-provided class to represent a Compare.Table record
public class Column; //EF-provided class to represent a Compare.Column record
}
}
Alright, that's all the provided / pre-my-involvement details. Now onto my stuff!
There's a couple of stylistic changes I've made specifically for CodeReview to prevent this question being literally miles long. Opening { are on the same line (I usually put them on the line below), documentation is removed (any
//comments here I've added just for CodeReview & aren't in my project), and classes are bunched together in the same code blocks (in my project, every class / interface lives in its own file)
Model
Entities
First off, I've identified a lot of patterns throughout the classes and added structure around them. All the Database classes contain a DatabaseID property, all the Schema classes contain DatabaseID & SchemaID, etc, so I created the following common interfaces. These properties should not be writable by anything other than EF itself, so the interfaces only include get accessors:
namespace Model {
public interface IDatabaseKey {
int DatabaseID { get; }
}
public interface ISchemaKey {
int DatabaseID { get; }
int SchemaID { get; }
}
public interface ITableKey {
int DatabaseID { get; }
int SchemaID { get; }
int TableID { get; }
}
public interface IColumnKey {
int DatabaseID { get; }
int SchemaID { get; }
int TableID { get; }
int ColumnID { get; }
}
}
More generally, every table contains a primary key, and it would be useful to be able to retrieve that as one object rather than needing to check manually what fields constitute it. I added another interface to enforce a PrimaryKey property that returns an array of objects
namespace Model {
interface IEntityKey {
object[] PrimaryKey { get; }
}
}
I then realised that, by using reflection, the code to retrieve the primary key can be identical for every class I currently have (EF marks key fields with a [Key] attribute). An abstract class containing the implementation seemed appropriate:
public abstract class EntityKey
: IEntityKey {
public object[] PrimaryKey {
get {
return (from property in this.GetType().GetProperties()
where Attribute.IsDefined(property, typeof(KeyAttribute))
select property.GetValue(this)).ToArray();
}
}
}
Following similar logic, the Dictionary and SysData classes all contain relevant metadata fields (whilst Compare does not). More common interfaces:
namespace Model {
public interface IDatabaseMetadata {
string DatabaseName { get; set; }
string Collation { get; set; }
DateTime CreatedDate { get; set; }
}
public interface ISchemaMetadata {
string SchemaName { get; set; }
}
public interface ITableMetadata { /* you get the idea */ }
public interface IColumnMetadata { /* and again */ }
}
I may add additional interface to represent the common descriptive fields of all the Dictionary classes, and the ChangeType field of the Compare classes, but I currently haven't found a use for those in my app so haven't yet.
My concrete EF classes are edited to extend/implement these:
// namespaces Model.Dictionary & Model.SysData
public class Database : EntityKey, IDatabaseKey, IDatabaseMetadata { ... }
public class Schema : EntityKey, ISchemaKey, ISchemaMetadata { ... }
public class Table : EntityKey, ITableKey, ITableMetadata { ... }
public class Column : EntityKey, IColumnKey, IColumnMetadata { ... }
// namespace Model.Compare
public class Database : EntityKey, IDatabaseKey { ... }
public class Schema : EntityKey, ISchemaKey { ... }
public class Table : EntityKey, ITableKey { ... }
public class Column : EntityKey, IColumnKey { ... }
I will list the final full class for Dictionary.Database, including EF attributes.The other classes are structurally the same:
[Table("Dictionary.Databases")]
public partial class Database
: EntityKey, IDatabaseKey, IDatabaseMetadata
{
// internal set, because EF needs to be able to set this but nothing outside the model should.
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int DatabaseID { get; internal set; } // from IDatabaseKey
[Required]
[StringLength(128)]
public string DatabaseName { get; set; } // from IDatabaseMetadata
[StringLength(128)]
public string Collation { get; set; } // from IDatabaseMetadata
public DateTime CreatedDate { get; set; } // from IDatabaseMetadata
public string Description { get; set; }
}
Contexts
The contexts proved a bit trickier to generalize. The contexts created by EF look like this (constructor & methods omitted):
namespace Model.Dictionary {
public partial class Context {
public virtual DbSet<Database> Databases { get; set; }
public virtual DbSet<Schema> Schemas { get; set; }
public virtual DbSet<Table> Tables { get; set; }
public virtual DbSet<Column> Columns { get; set; }
}
}
namespace Model.SysData {
public partial class Context {
public virtual DbSet<Database> Databases { get; set; }
public virtual DbSet<Schema> Schemas { get; set; }
public virtual DbSet<Table> Tables { get; set; }
public virtual DbSet<Column> Columns { get; set; }
}
}
namespace Model.Compare {
public partial class Context {
public virtual DbSet<Database> Databases { get; set; }
public virtual DbSet<Schema> Schemas { get; set; }
public virtual DbSet<Table> Tables { get; set; }
public virtual DbSet<Column> Columns { get; set; }
}
}
Bear in mind that DbSet<Database> in Model.Dictionary.Context is actually DbSet<Model.Dictionary.Database>, as opposed to DbSet<Model.SysData.Database> in Model.SysData.Context - they're different types.
I solved this (with help) by creating the following interface:
public interface IContext<TDatabase, TSchema, TTable, TColumn>
where TDatabase : class, IEntityKey, IDatabaseKey
where TSchema : class, IEntityKey, ISchemaKey
where TTable : class, IEntityKey, ITableKey
where TColumn : class, IEntityKey, IColumnKey
{
DbSet<TDatabase> Databases { get; set; }
DbSet<TSchema> Schemas { get; set; }
DbSet<TTable> Tables { get; set; }
DbSet<TColumn> Columns { get; set; }
}
Implemented on my concrete Contexts like so:
// Model.Dictionary.Context
public partial class Context
: DbContext, IContext<Dictionary.Database, Dictionary.Schema, Dictionary.Table, Dictionary.Column>
{ ... }
// Model.SysData.Context
public partial class Context
: DbContext, IContext<SysData.Database, SysData.Schema, SysData.Table, SysData.Column>
{ ... }
// Model.Compare.Context
public partial class Context
: DbContext, IContext<Compare.Database, Compare.Schema, Compare.Table, Compare.Column>
{ ... }
How's the structure so far? Bear in mind that all those interfaces are useful, they're not there just for the sake of being there. The usefulness of most will become more obvious in the controller.
Controller question is here
