Can I have 2 different EntityFramework contexts share a single SqlServer Compact database?

Question

I have a SqlServer Compact Edition database that contains 2 tables.

For each of these tables, I have a context class derived from DbContext which is used by the application to access the table.

In order to keep the different components of the application decoupled, I can't have one context class that would have DbSet properties for both of the tables. Rather, I need to have 2 different context class, each of them has to be completely unaware of the other and its data.

I'm using the code-first approach, my code defines the entities and model, and I let Entity Framework create the database for me.

My problem is: how can I still let Entity Framework create the database and the tables for me automatically on context initialization, but still have the 2 contexts share the same database and connection?

Right now what I get is, the first context successfully creates the database and the table in it, but when I try to create the second context, I am getting, unsurprisingly the following error:

The model backing the 'SomeObjectContext' context has changed since the database 
was created. Either manually delete/update the database, or call Database.SetInitializer
with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges 
strategy will automatically delete and recreate the database, and optionally seed it with 
new data.

None of the available IDatabaseInitializer are appropriate for me, because I don't want the entire database to be dropped when the second context is created. I just want the second context's table to be created in the existing database.

Any advise on how to solve this would be appreciated.

Thanks

Answer 1

You can't use CodeFirst this way with only one database. CodeFirst has to check the model classes against the db structure to work properly (they have to be "in sync").

In order to keep the different components of the application decoupled, I can't have one context class that would have DbSet properties for both of the tables. Rather, I need to have 2 different context class, each of them has to be completely unaware of the other and its data.

If booth tables have no connections to each other and its a requirement for your application to have these tables completely separated, why don't you create two databases and have a context for each of them?

At all, I would go with one database, have an internal DbContext and two different public repository classes which encapsulate the access to the two tables. If all your code is within the same assembly, you can access your internal context from your repository class. Someone who needs access to the repository may access the repository for the table he needs to.

Answer 2

Yes, you can use multiple DB context pointed to same DB.

Arthur Vickers from Entity Framework team recommends the following pattern….

Instead of specifying separate connection strings in your app.config file for multiple context, just set one string and give it a generic name.

 <connectionStrings>
        <add name="MyDBContext" connectionString="Your conn string here" providerName="System.Data.SqlClient" />
    </connectionStrings>

Then create a base context class that will use the connection string:

using System.Data.Entity; 

namespace DataLayer
{
    public class BaseContext<TContext> : DbContext where TContext : DbContext
    {
        static BaseContext()
        {
            Database.SetInitializer<TContext>(null); 
        }
        protected BaseContext()
            : base("name=MyDBContext")
        {
        }

    }
}

Now, inherit this base behavior in each context (for instance):

namespace DataLayer.Models

    {
        public class OrderContext : BaseContext<OrderContext>

    …

    namespace DataLayer.Models
    {
        public class ProductContext : BaseContext<ProductContext>
    …