Verifying the Entity Data Model schema against a production database

Question

We have an entity data model (database first) for a legacy product we are migrating to .Net from Delphi. We recently deployed to a server where some of the tables had been modified and columns deleted. The results were catastrophic. I'm working on a way of validating the EDM schema against the live SQL server database.

public Validation(ADbContext db)
{
    _connectionString = db.Database.Connection.ConnectionString;
}

private readonly string _connectionString;

public ILookup<string, List<string>> Run()
{
    // A tolerance to deal with Entity Framework renaming
    var modelValidation = GetModelProperties<ADbContext>(tolerance);
    var isValid = !modelValidation.Any(v => v.Any(x => x.Count > 0));
    if (!isValid)
        Logger.Activity(BuildMessage(modelValidation));
    return modelValidation;
}

public string BuildMessage(ILookup<string, List<string>> modelValidation)
{
    // build a message to be logged
} 

public List<string> GetMissingColumns(IEnumerable<string> props, IEnumerable<string> columns, int tolerance)
{
    // compare whether the entity properties have corresponding columns in the database
    var missing = props.Where(p => !columns.Any(c => p.StartsWith(c) && Math.Abs(c.Length - p.Length) <= tolerance)).ToList();
    return missing;
}

public string[] GetSQLColumnNames(Type t)
{
    SqlConnection connection = new SqlConnection(_connectionString);
    var table = t.Name;
    DynamicParameters dparams = new DynamicParameters();
    dparams.Add("Table", table);
    var query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table ";
    // Using dapper to retrieve list of columns from that table
    List<string> columns = connection.Query<string>(query, dparams).ToList();
    return columns.ToArray();
}

static string[] GetEntityPropertyNames(Type t)
{
    var properties = t.GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(p => p.CanRead && !p.PropertyType.FullName.Contains("My.Namespace") && !p.PropertyType.FullName.Contains("Collection"))
            .Select(p => p.Name)
            .ToArray();
    // these conditions excludes navigation properties: !p.PropertyType.FullName.Contains("My.Namespace") && !p.PropertyType.FullName.Contains("Collection")
    return properties;
}

ILookup<string, List<string>> GetModelProperties<T>(int tolerance, T source = default(T))
{
    var properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(p => p.PropertyType.IsGenericType)
            .Select(p => p.PropertyType.GetGenericArguments()[0])
            .Select(p => new
            {
                Entity = p.Name,
                Properties = GetEntityPropertyNames(p),
                Columns = GetSQLColumnNames(p),
            })
            .ToArray();

    return properties.ToLookup(p => p.Entity, p => GetMissingColumns(p.Properties, p.Columns, tolerance));
}

1. Is there a better way?
2. Does Entity Framework already provide a mechanism for achieving this?
3. Would this be better in the data layer or in another assembly?
4. Is adding Dapper (just for this purpose) overkill, does it add unnecessary complexity to the project?

Update I've created a Github repository with a library I've been using that implements this. To minimise dependencies, I've used reflection in favour of Entity specific features.

Answer 1

Is there a better way?
Does Entity Framework already provide a mechanism for achieving this?

In fact this is one question. If EF would provide a mechanism, undoubtedly that would be better, because you'd know for sure it yields something that EF is happy with. Unfortunately, no, EF hasn't got such a mechanism.

As far as EF checks the consistency of the database with an existing model, it only compares a previously stored model hash in the database with the current model hash. It never compares actual database objects. That would take far too much time in checking whether a migration should be applied. And then, all these methods are internal.

There is however a shorter and more reliable way to get entity and property names from a context. More reliable, because you'll only get the mapped entities and properties without having to jump through hoops to exclude non-mapped ones. I'll give you the heart of it, so you can fit it in appropriately.

var oc = ((IObjectContextAdapter)dbcontext).ObjectContext;

var items = oc.MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>();
foreach (var entityType in items)
{
    var props = string.Join(",", entityType.Properties);
    Debug.WriteLine(string.Format("{0}: {1}", entityType.Name, props));
}

Would this be better in the data layer or in another assembly?

I would make the part that provides table and column names part of the data layer, because there you have all required references. I would not include the part that queries the database, because it's done with Dapper, and...

Is adding Dapper (just for this purpose) overkill, does it add unnecessary complexity to the project?

No, Dapper is great. But since this is a third-party dependency I would use it in a separate assembly that has a reference to your DAL (to obtain the database object names), so your DAL doesn't have this dependency.