Is there an easy way to check if the database schema is exactly what I expect from using the Entity Framework?

Question

I want my application to check if the database is consistent at startup.

Is there an easy way to check if the database schema is exactly what I expect from using the Entity Framework?

EF4 itself does some validation. If the mapped type contains a column that does not exist in the target table when EF4 materializes, it throws an exception. Good. However, there are some things that he does not: at first, he does not check the entire database. This does not throw an exception when the target table contains a column that is not displayed.

Is there an easy way to achieve this?

Answer 1

, EF , . , , SQL- ( ) EF, . , , , , .

Answer 2

, ( SqlServer) - EF script ( , , NHibernate , , ) , , Smo, Sql.

Answer 3

I used a specific solution for this in the code review question using this related Github Repository .

I decided not to use MetadataWorkspace, but the code can be changed to use it instead of reflection.

EDIT

Here is an example of the corresponding code:

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