OnModelCreating() doesn't fire when creating DbContext with different attached database

[bairog]

Hello.
I'm working with SQLite database using EF Code-First approach.
My first DbContext works with three physical databases (DataSource=@"D:\Users.db" and Attach=@"D:\UserTypes.db;D:\Messages.db").
I map different DbSets to different physical databases inside OnModelCreating event:

Code: Select all

...
        public DbSet<Message> Messages { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<UserType> UserTypes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

            var sb = new SQLiteConnectionStringBuilder(Database.Connection.ConnectionString);
            var attach = sb.Attach.Split(';');
            
            var firstAttachedDbSchemaName = Path.GetFileNameWithoutExtension(attach[0]);
            
            var secondAttachedDbSchemaName = Path.GetFileNameWithoutExtension(attach[1]);

            modelBuilder.Entity<UserType>().ToTable("UserType", firstAttachedDbSchemaName);            

            modelBuilder.Entity<Message>().ToTable("Message", secondAttachedDbSchemaName);
            ...

After that i make a copy of D:\Messages.db (D:\Messages_Copy.db) and create new DbContext which works with "D:\Users.db", "D:\UserTypes.db" and "D:\Messages_Copy.db".
But when I'm trying to read some data via that second context I have an error:

SQLite error
no such table: Messages.Message

Looks like second DbContext is trying to work with D:\Messages.db despite of connectionString - why?
Second stange thing is that OnModelCreating fires only once - when I open connection of my first DbContext.

P.S. I'm using dotConnect for SQLite 5.3.583.

I've uploaded sample project here. Click AddUserTypeUsersAndMessages() button to create databases (on disk D:\), CreateMessagesCopy() button to copy database and SelectSecondContextAndGetMessages() button to get an error.

[bairog]

I investigated further:
if I make a copy of "DataSource" database (make D:\Users_Copy.db) and create new DbContext which works with "D:\Users_Copy.db", "D:\UserTypes.db" and "D:\Messages.db" - it works like a charm!
Looks like error appears only if I work with copied "attached" database.


P.S. And one quick question - after I call

Code: Select all

((IObjectContextAdapter)DbContext).ObjectContext.Connection.Close();

all three database files remains blocked by my application (i cannot rename them for example). Why?
How can I free that files and perform some actions over them before I will call

Code: Select all

((IObjectContextAdapter)DbContext).ObjectContext.Connection.Open();

next time?

[bairog]

Hello!
Anybody here?
4 days without any reply - does anybody from Deart Team read this forum?

[Shalex]

Sorry for the delay.

Looks like second DbContext is trying to work with D:\Messages.db despite of connectionString - why?
Second stange thing is that OnModelCreating fires only once - when I open connection of my first DbContext.

You have identified the reason of the issue correctly: OnModelCreating fires only once and, as a result, the second instance of the context uses a mapping created for the first context instance. This behaviour is designed by Microsoft: https://msdn.microsoft.com/en-us/librar ... 13%29.aspx > the Remarks section.

P.S. And one quick question - after I call

Code: Select all

((IObjectContextAdapter)DbContext).ObjectContext.Connection.Close();

all three database files remains blocked by my application (i cannot rename them for example). Why?
How can I free that files and perform some actions over them before I will call

Code: Select all

((IObjectContextAdapter)DbContext).ObjectContext.Connection.Open();

next time?

Looks like the issue is caused by pooling: https://www.devart.com/dotconnect/sqlite/docs/?faq.html > the Connection pooling section. You can turn off pooling by adding "Pooling=false;" to your connection string.

[bairog]

This behaviour is designed by Microsoft: https://msdn.microsoft.com/en-us/librar ... 13%29.aspx > the Remarks section.

MSDN says

This caching can be disabled by setting the ModelCaching property on the given ModelBuidler, but note that this can seriously degrade performance. More control over caching is provided through use of the DbModelBuilder and DbContextFactory classes directly.

The problem is the DbModelBuilder does not contain any property named ModelCaching.
How it is possible to disable the model caching inside OnModelCreating event of even in a run-time?

[Shalex]

The issue was discussed at http://stackoverflow.com/questions/3909 ... delcaching.

JIC:

• https://msdn.microsoft.com/en-us/librar ... .113).aspx
  DbModelBuilder is typically used to configure a model by overriding OnModelCreating . You can also use DbModelBuilder independently of DbContext to build a model and then construct a DbContext or ObjectContext.
• DbModelBuilder.Build Method
  https://msdn.microsoft.com/en-us/librar ... onnection)
• DbModel.Compile Method
  https://msdn.microsoft.com/en-us/librar ... el.Compile
• DbContext has constructors with the DbCompiledModel parameter:
  https://msdn.microsoft.com/en-us/librar ... .113).aspx

[bairog]

I tried to implement this pattern: I moved all OnModelCreating code to a function that returns DbCompiledModel and added new DbContext constructor with the DbCompiledModel parameter.

But I faced a problem with initial migration (that creates databases for the first time): it creates all three tables in "DataSource" database despite of my DbModelBuilder.Entity<T>.ToTable(tableName, schemaName) instructions. And therefore when I try to write some data to database i receive an error:

SQLite error: no such table UserTypes.UserType

I've uploaded sample project here. Click AddUserTypeUsersAndMessages() button to create databases (on disk D:\) and receive an error.

[Shalex]

Thank you for the test project. We have reproduced the error and are investigating the issue.

[Shalex]

You have encountered a peculiarity of Entity Framework behaviour. When creating database which occurs implicitly in scope of initialization strategy, internal logic of EF creates again the instance of DbContext with a default constructor. I.e. mapping of the existing object is not used.

Here are the advices to make your application cope with this problem (although this exceeds the goals of our support):

1. Remove the parameterless constructor MyDbContext() of the MyDbContext class.

2. Store mapping and connection string.

2.1. Add these lines in your DbAccess class:

Code: Select all

internal static DbCompiledModel ActiveModel;
internal static string ActiveConnectionString;
private static readonly List<DbCompiledModel> AllModels = new List<DbCompiledModel>();
private static readonly List<string> AllConnectionStrings = new List<string>();

2.2. In the AddConnection method of the DbAccess class replace

Code: Select all

AllContexts.Add(new MyDbContext(GetDbCompiledModel(CreateConnection(connectionString)), connectionString));

with

Code: Select all

var model = GetDbCompiledModel(CreateConnection(connectionString));
AllModels.Add(model);
AllContexts.Add(new MyDbContext(model, connectionString));
AllConnectionStrings.Add(connectionString);

2.3. In the ChangeActiveConnection method of the DbAccess class add the following lines:

Code: Select all

ActiveModel = AllModels[index];
ActiveConnectionString = AllConnectionStrings[index];

3. Add the class, which implements IDbContextFactory, to your project:

Code: Select all

  internal class MigrationsContextFactory: IDbContextFactory<MyDbContext> {

    public MyDbContext Create() {

      return new MyDbContext(DbAccess.ActiveModel, DbAccess.ActiveConnectionString);
    }
  }

Now the first button on your form works.

4. Additionally, we recommend you to specify the CreateDatabaseIfNotExists initialization strategy explicitly. For example, you can do that by adding a static constructor to the MyDbContext class:

Code: Select all

static MyDbContext() {
  Database.SetInitializer(new CreateDatabaseIfNotExists<DbContext>());
}