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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Tue 15 Nov 2016 11:28

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.
Last edited by bairog on Wed 16 Nov 2016 08:18, edited 1 time in total.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Wed 16 Nov 2016 08:18

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
Posts: 120
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Sat 19 Nov 2016 06:25

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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Mon 21 Nov 2016 08:24

Sorry for the delay.
bairog wrote: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.
bairog wrote: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
Posts: 120
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Mon 21 Nov 2016 09:23

Shalex wrote: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
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Tue 22 Nov 2016 14:22

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

JIC:

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Wed 23 Nov 2016 06:12

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
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Fri 25 Nov 2016 18:42

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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Mon 28 Nov 2016 15:42

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>());
}

Post Reply