How can I detect if an Entity Framework Migration broke my SQL View?

Question

I have a ton of tables, and some queries are provided by SQL Views.

What I don't want is to create a maintenance nightmare where a developer changes a column in code first causing one or more SQL Views to break.

Is there a mechanism in EF to check this?

score 0 · Answer 1 · edited May 23 '17 at 12:07

Are the views mapped in EF?
Usually I run an EF configuration test that checks all the tables.
I post also the main code but it works only if the primary key contains one and only one property named Id. You could fix it calling FirstOrDefault method (you can have a look here on how to call it How do I use reflection to call a generic method? ).

[TestMethod]
public void All()
{

    var properties = typeof (Context).GetProperties().Where(p => IsSubclassOfRawGeneric(typeof(DbSet<>), p.PropertyType));

    foreach (PropertyInfo property in properties)
    {
        Type entityType = property.PropertyType.GetGenericArguments()[0];
        PropertyInfo idProperty = entityType.GetProperty("Id");
        if (idProperty == null)
        {
            Console.WriteLine("Id property not found. Cannot check type configuration");
            continue;
        }
        Type idPropertyType = idProperty.PropertyType;
        DbSet dbSet = _context.Set(entityType);
        if (idPropertyType == typeof(string))
        {
            try
            {
                dbSet.Find("A");
            }
            catch (Exception e)
            {
                throw new Exception("Cannot access to DbSet " + property.Name, e);
            }
        }
        else if (idPropertyType == typeof (int))
        {
            try
            {
                dbSet.Find(1);
            }
            catch (Exception e)
            {
                throw new Exception("Cannot access to DbSet " + property.Name, e);
            }
        }
        else
        {
            Console.WriteLine("Id property type not supported ('{0}'). Cannot check type configuration", idPropertyType.Name);
            continue;
        }

    }

}


static bool IsSubclassOfRawGeneric(Type generic, Type toCheck)
{
    while (toCheck != null && toCheck != typeof(object))
    {
        var cur = toCheck.IsGenericType ? toCheck.GetGenericTypeDefinition() : toCheck;
        if (generic == cur)
        {
            return true;
        }
        toCheck = toCheck.BaseType;
    }
    return false;
}

score 0 · Answer 2 · answered Apr 02 '16 at 09:31

No, there is not. Which goes down in a long list of arguments why code first migrations are a toy, not a tool.

Compare that with maintaining the database in a SSDT environment and manually generating AND VALIDATING change scripts. Migrations turn into a "load SQL Scripts and execute them" way faster the moment you use any non-baseline feature (and those features are useful).

So, no.

What you CAN do is have a unit / integration test automatically testing the application works on the last migration. Automated testing to the rescue.

How can I detect if an Entity Framework Migration broke my SQL View?

2 Answers2