May20

Entity Framework Compliance VistaDB vs SQL Server 2008 R2

Entity Framework Shootout VistaDB vs SQL Server 2008 R2 We have spent a lot of time on our Entity Framework provider over the past 18 months or so.  There is a sample EF provider from Microsoft that illustrates basic points, and if you look at all the open source databases they pretty much all used the default code in their implementations.  But what if that sample code doesn’t implement something?  Or if it is just plain wrong?

Unexpected Results

I have to tell you we didn’t expect the results we got from this test at all.  We expected that we probably had some room for improvement and that SQL Server would ace all the tests.  Didn’t turn out that way.

Entity Framework Compliance?

The short answer to this question is that there IS NO compliance test to make sure your EF provider works correctly, or matches some baseline specification.  We really wanted some way to see if we matched up against the SQL Server provider, because we try really hard to be as compatible with SQL Server as possible. 

This post is about the journey we have taken so far in trying to figure out what that compliance might look like, how do we score today, what tests are missing, and what do others do to test their providers?

Standard LINQ test?

Well, the first thing I thought of was the LINQ 101 Samples from the MIX 2007 conference that Microsoft produced.  I have seen a number of other providers implement them as “proof” of their compliance, so I thought that would be a good starting point.  We found the 101 LINQ samples page on Microsoft, but most of them are not database related.

There is also a post on the ADO.Net Team Blog about 101 samples, but those are pretty old and still referencing Orcas as the target (Visual Studio 2008).  The most recent version of this we could find was on CodePlex under the ADO.NET Samples project.

Getting started

We first noticed that the Northwind they used in their tests is not the standard Northwind, or the NorthwindEF you see in most demos.  This Northwind has some specific changes to test some functionality.  So we migrated their database to VistaDB 4 and started using it.

You would think that with the promise of EF abstracting you from the database it would be pretty easy to use the same model across two providers.  Wrong, dead wrong.  It is a lot more complicated than it should be, and Matt did a great blog post about the steps he had to go through to make a single Entity Framework model work with two providers.

90 Tests?

EF Model Yea, we know.  It is called the 101 LINQ Samples, but there are actually only 90 tests in it, and some of them don’t work against the schema included.  The EF model is huge (see image zoomed WAY out).

The sample model worked fine in VistaDB 4 EF using Visual Studio 2010, but the tests that are in that sample project are not written as unit tests.

Building Unit Tests for the Samples

So we built unit tests using a Visual Studio 2010 test project.  Each sample was written to accept a NorthwindEntities data context object, and execute the query.

Then we build a test runner for each test that executes the VistaDB EF model, then the SQL Server model, and then compares the results to make sure we both returned the exact same data.  If a test blows up it is flagged as a failure for that provider.  If both providers succeed, but the results are different then it is still a failure.

Example Test Code

[TestMethod]
public void Test01()
{
    List<Customer> vdbResult = null;
    List<Customer> sqlResult = null;

    ExecutionResults vdb = ExecutionResults.Passed;
    ExecutionResults sql = ExecutionResults.Passed;
    CompareResults compare = CompareResults.Match;

    try
    {
        vdbResult = Samples.Sample01(EntitiesCache.VDBEntities);
    }
    catch (Exception e)
    {
        vdb = ExecutionResults.Failed;
    }
    try
    {
        sqlResult = Samples.Sample01(EntitiesCache.SQLEntities);
    }
    catch (Exception e)
    {
        sql = ExecutionResults.Failed;
    }

    if (!ResultCompare.Compare(vdbResult, sqlResult))
        compare = CompareResults.Inequal;

    TestResult(vdb, sql, compare);
}

Test Result Code

static void TestResult(ExecutionResults vdb, ExecutionResults sql, CompareResults compare)
{
    Assert.IsTrue(!(vdb == ExecutionResults.Failed && sql == ExecutionResults.Passed), 
            "VistaDB Failed to execute the query and SqlServer passed.");
    
    Assert.IsTrue(!(vdb == ExecutionResults.Passed && sql == ExecutionResults.Failed), 
            "SqlServer Failed to execute the query and VistaDB passed.");

    Assert.IsTrue(!(vdb == ExecutionResults.Failed && sql == ExecutionResults.Failed), 
        "Both SqlServer and VistaDB failed to execute the query");

    Assert.IsTrue(!(compare == CompareResults.Inequal), 
        "Both SqlServer and VistaDB executed the query both got different results");
}

Actual Sample Code

Each sample has a code block like this one to ensure the code is the exact same for each provider.

//Where - Simple 1
//This sample uses WHERE to find all customers in Seattle.
public static List<Customer> Sample01(NorthwindEntities Entities)
{
    var vdbQuery = from cust in Entities.Customers
                    where cust.City == "Seattle"
                    select cust;

    List<Customer> customers = new List<Customer>();
    foreach (Customer c in vdbQuery)
    {
        customers.Add(c);
    }
    return customers;
}

Tests run in Visual Studio 2010

Test results in VS 2010

Some tests were not implemented, we could not get them to compile against the provided EF model at all.

Test Results

EF Comparison Results

Provider

Pass

Fail

Not Impl

VistaDB 4

77

4

9

SQL Server 2008 R2

71

10

9

The results surprised us quite a bit.  VistaDB had 6 fewer failures than SQL Server 2008 R2 using the same Entity Framework model.  And every test VistaDB failed, SQL Server also failed. 

There were no tests that VistaDB had incorrect or different results than SQL Server 2008 R2.

Next Steps

We will continue to implement more tests, and more importantly find out why they don’t work in SQL Server.  I took all of the samples that failed in SQL Server and ran them in LINQPad also.  They failed there as well, so it was not our model or harness.  I also generated a new EF model specifically for SQL Server and made sure it was not something in our changing of the model to work with both VistaDB and SQL Server.  In every case the tests that failed in our sample also failed in LINQPad and the other model.

I would love to get this same test to work with other providers, but because their types don’t match SQL Server the way VistaDB does it would be a lot of work.  Perhaps one of these days we will have the time to attempt it.

SQL CE couldn’t generate the model

I tried to then take all the same sample and run it against SQL CE.  The model would not even load against SQL CE.  The SQL CE provider couldn’t handle the complexity of the model as it appears it doesn’t handle dbo schema correctly and tries to put all the objects in the same collection.  This results in a duplicate column on things like ID that are used in multiple tables.  I tried this in Visual Studio 2010 with a new model generated just for SQL CE with a database migrated directly out of SQL Server 2008.

 

Shout it kick it on DotNetKicks.com

Comments (8) -

21/05/2010 03:37 #

Matthew McDonald

I would have really loved to see the result from these tests against SQL CE since VistaDB is more commonly compared to it instead of SQL Server. Maybe next time..

Matthew McDonald

24/05/2010 12:07 #

Erik Ejlskov Jensen

I have generated a SQL Compact script and database (NWEF.sql and Northwind.sdf) using Export2SqlCe from http://exportsqlce.codeplex.com.

I have also successfully generated a EF Model based on this SDF file.

Hope this will enable you to test with SQL CE.

Erik Ejlskov Jensen

24/05/2010 03:35 #

js_vistadb

Hi Erik, that is not the same database as the one used by the original test.  If you download the one from the adonet samples on codeplex it does migrate to SQL CE.  But the SQL CE model fails (under .Net 4) with errors in the EF provider.

If we get enough interest around the SQL CE side of things maybe I can document the process we went through with it and post that as well.

js_vistadb

24/05/2010 05:25 #

Erik Ejlskov Jensen

Not sure waht you mean by &quot;not the same database&quot; - I downloaded and attached the NorthwindEF database, and then did the script and ran it against a new SQL Compact database file.

Erik Ejlskov Jensen

24/05/2010 09:44 #

js_vistadb

Tried to download your link - says it is broken.  We took the database in the sample project migrated it to SQL CE and then generated a model off of it.

The errors had to do with columns named the same as other columns.  You can't change any of the column names and have the same EF model work.  

&quot;
Unable to generate the model because of the following exception: 'The item with identity 'ProductID' already exists in the metadata collection.
Parameter name: item
&quot;

There are a lot of tables that have the name ProductID in them.  When I looked at the raw EF gen syntax there were no schema specifiers in the EF model (they were null) so all came back in the same object and failed.

Screencast of the error:  http://screencast.com/t/NDY4ZTBlOTEt

Maybe the tool we used to migrate did something different than the tool you used?  

Screenshot of two of the tables that have ProductID in them.  Note this is not how Northwind normally looks, but the one that came with the tests did so that is what we used.

http://screencast.com/t/NzM3NDA5Zj

js_vistadb

25/05/2010 03:45 #

Erik Ejlskov Jensen

I generated this sdf based on the NorthwindEF.mdf SQL Server database in the \EFBeta3Samples\NorthwindEFModel folder in the EFBeta3Samples.zip? Is that the correct database?

I was able to create a model based on this SQL Compact file using VS2010 and EF4...

Erik Ejlskov Jensen

25/05/2010 04:56 #

js_vistadb

No, that is not the same model.  Look at the database in that archive from codeplex.  There are a bunch of tables present (you can see them in the screenshot I posted).  The tables are derived from the normal northwind, but they are not the same.

They customized the tables to demonstrate certain linq / ef concepts that were not present in the normal version of northwind.

http://screencast.com/t/MmUwMjll

The top database in the screenshot is yours ( 12 tables ).  The bottom one is the one that is used in that EF test ( 21 tables ).

js_vistadb

25/05/2010 05:09 #

Erik Ejlskov Jensen

OK, the zip file contains 2 MDF files. The &quot;other&quot; one (the correct one, that you have been using) is named NORTHWND.MDF. I will test with that one!

Erik Ejlskov Jensen

Comments are closed