How can I unit test Entity Framework Code First Mappings?

Question

I'm using Code First to map classes to an existing database. I need a way to unit test these mappings, which are a mix of convention-based, attribute-based, and fluent-api.

To unit test, I need to confirm that properties of the classes map to the correct table and column names in the database. This test needs to be performed against the context, and should cover all configuration options for code first.

At a very high level, I'd be looking to assert something like (pseudo-code):

Assert.IsTrue(context.TableFor<Widget>().IsNamed("tbl_Widget"));
Assert.IsTrue(context.ColumnFor<Widget>(w => w.Property).IsNamed("WidgetProperty"));

Answer 1

Another idea to consider is using Linq and ToString().

For eaxample this :

context.Widget.Select(c => c.Property).ToString()

Will result in this for SQL Server Provider :

"SELECT [Var_3].[WidgetProperty] AS [WidgetProperty] FROM [dbo].[Widget]..."

Now we could hide it all in some Extension method that and parses resulting SQL it would look almost like Your pseudo-code :

Assert.IsTrue(context.Widgets.GetSqlColumnNameFor(w => w.Property).IsNamed("WidgetProperty"));

Draft for extension :

public string GetSqlColumnNameFor<TSource>(this DbSet<T> source, Expression<Func<TSource, TResult>> selector)
{
    var sql = source.Select(selector).ToString();

    var columnName = sql... // TODO : Some regex parsing

    return 
       columnName;
}

Similary we could create GetSqlTableNameFor().

UPDATE : I decided to look for some dedicates SQL Parsers, so this solution is more generic, obviously there is such a thing for .NET :

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/generate-internal-query-parse-tree-in-xml-for-further-processing/

Answer 2

If you wrote a method

public static string ToMappingString(this Widget obj)

Then you could easily testing this via approval tests ( www.approvaltests.com or nuget)

There's a video here: http://www.youtube.com/watch?v=vKLUycNLhgc

However, if you are looking to test "My objects save and retrive themselves" Then this is a perfect place of "Theory Based Testing"

Theory based testing Most unit test take the form of

Given A,B expect C

Theory based testing is

Given A,B expect Theory

The beauty of this is there is no need to worry about which particular form A & B take since you don't need to know C, so any random generator will work.

Example 1: Testing Add and Subtract methods

Normally you would have stuff like

Assert.AreEqual(5, Add(2,3));
Assert.AreEqual(9, Add(10,-1));
Assert.AreEqual(10, Add(5,5));
Assert.AreEqual(7, Subtract(10,3));

However if you wrote a Theory Test it would look like

for(int i = 1; i < 100; i++)
{ 
    int a = random.Next();
    int b = random.Next();
    Assert.AreEqual(a, Subtract(Add(a,b),b, string.Format("Failed for [a,b] = [{0},{1}], a,b));        
}

Now that you understand Theory based testing, the theory you are trying to test is

Given Model A
When A is stored to the database, and retrieved the resulting object is equal to A

Answer 3

The only way I can think of to cover every possible option would be to use the Entity Framework Power Tools to pre-compile the views of your DbContext, and probably use a combination of reflection on that generated type and RegEx on the generated code itself to verify everything maps the way you want it to. Sounds pretty painful to me.

Another thing that comes to mind is creating a facade around DbModelBuilder to intercept and check everything that passes through it, but I don't know if that would handle the convention-based stuff. Also sounds painful.

As a less-complete, but much easier alternative, you can probably knock out a large portion of this by switching to attribute-based mapping wherever possible. This would allow you to create a base test class, say, ModelTesting<TEntity>, which includes a few test methods that use reflection to verify that TEntity has:

• A single TableAttribute.
• Each property has a single ColumnAttribute or NotMappedAttribute.
• At least one property with a KeyAttribute.
• Each property type maps to a compatible database type.

You could even go so far as to enforce a naming convention based on the names of the properties and class (with a caveat for table-per-hierarchy types). It would also be possible to check the foreign key mappings as well. That's a write-once base class you can derive from once for each of your model types and catch the majority of your mistakes (well, it catches the majority of mine, anyway).

Anything that can't be represented by attributes, like TPH inheritance and such, becomes a little harder. An integration test that fires up the DbContext and does a FirstOrDefault on Set<TEntity>() would probably cover most of those bases, assuming your DbContext isn't generating your database for you.