0

I'm using Entity Frameowrk Core to handle different database in a .NET Core 3.0 WPF application. I'm using different SQLite DB files for different users, so the connection string change for every user that log in.

I have to be able to use migrations, but when i run dotnet ef migrations add V2 for example, it fail because it cannot find the DB.

How can I generate migrations in this situation?

This is my code for the context:

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
      var connectionString = string.Format("Data Source={0}", GetDbPath(this.username));

      this.connection = new SqliteConnection(connectionString);
      this.connection.Open();

      options.UseSqlite(this.connection)
}
materight
  • 527
  • 8
  • 22
  • 1
    is there a particular reason for this? I mean.. having one database per user seems a bit strange at first. – jpgrassi Apr 14 '19 at 16:26
  • Primarily to avoid having composite keys for every table with the user id. Also in this way we have different files for different users, so it's easier to handle separate backups. – materight Apr 15 '19 at 10:04
  • But is this efficient? I mean.. what if there are 1 million users? Might be a bit of a stretch but.. definitely looks better to have userId in the tables. This is more or less similar to a multi-tenant application, so I really don't see why to create separate files for each user. – jpgrassi Apr 15 '19 at 10:06
  • I'm doing this in a desktop application, probably there will be a single user for each machine. Having two user using the software in the same machine it's already an edge case... Also the application create a new database file only when the user log in for the first time. I agree that's not the best design, but for our case it's good enough. – materight Apr 15 '19 at 10:20
  • Okay fair enough. So, did you try calling `myDbContext.Database.Migrate()` at runtime? – jpgrassi Apr 15 '19 at 10:26
  • Yes, and it's working. The problem is when I try to add a new migration – materight Apr 15 '19 at 10:28
  • Why do you open the sql connection at that moment though? Is it open for the rest of the app? To generate migrations, you just need a "dummy" connection string.. you don't need the actual user db. Maybe take a look at this: https://stackoverflow.com/questions/48363173/how-to-allow-migration-for-a-console-application – jpgrassi Apr 15 '19 at 11:05
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191846/discussion-between-jpgrassi-and-matteo-destro). – jpgrassi Apr 15 '19 at 11:31

1 Answers1

0

At the end I found a workaround by temporarily using a fixed local DB path when generating the migrations, so there is no runtime error for missing variables:

// var dbPath = GetDbPath(this.username);
var dbPath = "C:\\DB.db";
var connectionString = string.Format("Data Source={0}", dbPath);
...
materight
  • 527
  • 8
  • 22