Howto perform 2-phase commit (global transaction or savepoint)?

[bairog]

Hello again.
I have some problems with automatic migrations (in "data source" database+2 "attached" databases approach).
For testing I've removed some fields: Privilege from UserTypes.db, Age from Users.db, IsPrivate from Messages.db.
And I expect automatic migrations to create that fields (according to classes in Entites.cs).
But when I click AddUserTypeUsersAndMessages() button - I get an exception

System.Data.Entity.Infrastructure,DbUpdateException:
An error occurred while updating the entites, See inner exception for details.
SQLite error: table UserTypes.UserType has no column named Privilege.

I've uploaded my project here (databases should be placed to D:\)

[Shalex]

For testing I've removed some fields: Privilege from UserTypes.db, Age from Users.db, IsPrivate from Messages.db.
And I expect automatic migrations to create that fields (according to classes in Entites.cs).

1. How had you removed that fields? They should be removed via EF code so that the __MigrationHistory table would know about this change, and EF logic should update the database basing on the content of __MigrationHistory. But if you dropped columns via some database management tool, the EF engine would try to execute the SQL statements assuming that no changes were applied.

I've uploaded my project here (databases should be placed to D:\)

2. The databases files uploaded with your project have "incorrect" structure:

• Messages
  • __MigrationHistory
  • Message
  • User
  • UserType
• Users
  • __MigrationHistory
  • Message
  • User
  • UserType
• UserTypes
  • __MigrationHistory
  • Message
  • User
  • UserType

If you delete these files, set sb.FailIfMissing = false; in \DevartTest\DataAccessLayer2\StorageSystem.cs, and run your code, as a result, the database files with a "correct" structure will be created:

• Messages
  • Message
• Users
  • __MigrationHistory
  • User
• UserTypes
  • UserType

[bairog]

But if you dropped columns via some database management tool, the EF engine would try to execute the SQL statements assuming that no changes were applied.

I've understanded my mistake - I've dropped columns via tool.

If you delete these files, set sb.FailIfMissing = false; in \DevartTest\DataAccessLayer2\StorageSystem.cs, and run your code, as a result, the database files with a "correct" structure will be created:

This worked for me.
So your statement in previous post that

This approach can be used for predefined (existing) databases

is not correct.
Creating databases in "data source+attached database" approach is also supported. Great!

[Shalex]

If you delete these files, set sb.FailIfMissing = false; in \DevartTest\DataAccessLayer2\StorageSystem.cs, and run your code, as a result, the database files with a "correct" structure will be created:

This worked for me.
So your statement in previous post that

This approach can be used for predefined (existing) databases

is not correct.
Creating databases in "data source+attached database" approach is also supported. Great!

You are right. Sorry for the misleading message (corrected it with new comments).

[bairog]

One more question. Lets say I have a connection with one "data source" database and two "attached":

Code: Select all

var sb = new SQLiteConnectionStringBuilder();
sb.DataSource = @"D:\Users.db";
sb.FailIfMissing = false;
sb.Attach = @"D:\UserTypes.db;D:\Messages.db";

To map entities to correct databases I use:

Code: Select all

modelBuilder.Entity<UserType>().ToTable("UserType", "UserTypes");
modelBuilder.Entity<Message>().ToTable("Message", "Messages");

But is situation when database names are passed from outside how can I get schemaName of attached databases?
Now I'm using the following code:

Code: Select all

var sb = new SQLiteConnectionStringBuilder(Database.Connection.ConnectionString);
var attach = sb.Attach.Split(';');

modelBuilder.Entity<UserType>().ToTable("UserType", Path.GetFileNameWithoutExtension(attach[0]));
modelBuilder.Entity<Message>().ToTable("Message", Path.GetFileNameWithoutExtension(attach[1]));

But it looks ugly for me. Is there some better solution - GetSchemaNameForDatabase() or something similar?

[Shalex]

Your code looks OK.