2
\$\begingroup\$

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

\$\endgroup\$
1
\$\begingroup\$

One improvement I've discovered myself - I can improve these:

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; }
}

By adding an inheritance chain (ISchemaKey implements IDatabaseKey, ITableKey implements ISchemaKey, etc), I can both reduce the size of these interfaces to a single property each and add additional flexibility:

public interface IDatabaseKey 
{
    int DatabaseID { get; }
}

public interface ISchemaKey
    : IDatabaseKey
{
    int SchemaID { get; }
}

public interface ITableKey 
    : ISchemaKey
{
    int TableID { get; }
}

public interface IColumnKey
    : ITableKey
{
    int ColumnID { get; }
}

Now, I can do things like provide an IColumnKey wherever an IDatabaseKey is expected:

public void foo(DataDictionary.Column someColumn) {
    IDatabaseKey myDatabaseKey = someColumn;
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.