Unable to use existing database in unit tests with Effort framework

Question

I am trying to write test using a database, hosted in Azure SQL, with Effort framework on Entity Framework 6.

When executing the following code, an exception is thrown:

[ClassInitialize]
public static void ClassInitialize(TestContext context)
{
    EffortProviderConfiguration.RegisterProvider();
}

[TestMethod]
public void TestMethod1()
{
    const string connectionString = "Data Source=***;Initial Catalog=my_catalog;User ID=user;Password=password;provider=System.Data.SqlClient";
    IDataLoader loader = new EntityDataLoader(connectionString);
    using (var ctx = new UsersDbContext(Effort.DbConnectionFactory.CreatePersistent("cool", loader)))
    {
        var usersCount = ctx.Users.Count();
    }
}

Exception thrown in Count() execution:

Effort.Exceptions.EffortException: Unhandled exception while trying to initialize the content of 'Table' table ---> System.ArgumentException: Keyword not supported: 'data source'.

The same exception is thrown when replacing EffortProviderConfiguration.RegisterProvider() with app.config settings.

When using exactly the same connection string for creation of the UsersDbContext it succeeds and the data is accessible. In addition, creating context with Effort persistent or transient mode, without connection string, works well too.

What should be done to initialize a connection with existing data from a real DB?

Answer 1

If like me you're confused as to why you have to give Effort a connection string at all (since it works off an in-memory database, and you provide your context a connection directly), the documentation makes it a bit clearer - it's only required if you're using database-first or model-first variants of the Entity Framework, because the Entity connection string provides the information necessary for Effort to locate your model so that it can build a schema from it!! So you can safely fill the server/database/user id/password portions of the connection string with dummy names.

This also makes it clear that the custom default DbConnectionFactory approach only works for code-first, which explains the first few hours of errors I was getting... For model first or database first, you have to inject an Entity Connection into your entity class, as described here.

A useful tip - because your generated entity model class is a partial class, you can create another code file in the same assembly, give it the same namespace and make it also a partial class, and you can add the second constructor necessary for setting the EntityConnection to that code file instead, that way when you modify/recreate your entity model, the code with the custom constructor won't get deleted by the t4 template.

Answer 2

You are specifying connection string in wrong format. You are using ADO.NET/Linq2SQL connection string format, whenever EntityDataLoader requires connection string that will fit EntityConnection (class from EntityFramework). You can read about connection strings for EF here: http://msdn.microsoft.com/en-us/library/system.data.entityclient.entityconnection.connectionstring(v=vs.110).aspx

Saying it short, your connection string should look like:

"Provider=System.Data.SqlClient; 
 Metadata=c:\metadata|c:\Metadata\Sql; 
 Provider Connection String='Data Source=localhost; Initial Catalog=AdventureWorks;Integrated Security=True;Connection Timeout=60' "

Right now in your code you are specifying only Provider Connection String part.