StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>We changed our code from:</p> <pre><code> dbMgConfig.AutomaticMigrationDataLossAllowed = false; var mg = new DbMigrator(dbMgConfig); mg.Update(null); </code></pre> <p>to </p> <pre><code> dbMgConfig.AutomaticMigrationDataLossAllowed = true; var mg = new DbMigrator(dbMgConfig); var scriptor = new MigratorScriptingDecorator(mg); string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null); throw new Exception(script); </code></pre> <p>so that we could observe what changes <code>DbMigrator</code> is attempting on the remote server.</p> <p>In the case outlined at the start of this question (i.e. colleague makes upload which creates database, followed by me making upload generated from the same source on a different machine), the following SQL statements are generated:</p> <pre><code>ALTER TABLE [GalleryImages] DROP CONSTRAINT [FK_GalleryImages_Galleries_Gallery_Id] ALTER TABLE [GalleryImages] DROP CONSTRAINT [FK_GalleryImages_Images_Image_Id] ALTER TABLE [UserLightboxes] DROP CONSTRAINT [FK_UserLightboxes_Users_User_Id] ALTER TABLE [UserLightboxes] DROP CONSTRAINT [FK_UserLightboxes_Lightboxes_Lightbox_Id] ALTER TABLE [ImageLightboxes] DROP CONSTRAINT [FK_ImageLightboxes_Images_Image_Id] ALTER TABLE [ImageLightboxes] DROP CONSTRAINT [FK_ImageLightboxes_Lightboxes_Lightbox_Id] DROP INDEX [IX_Gallery_Id] ON [GalleryImages] DROP INDEX [IX_Image_Id] ON [GalleryImages] DROP INDEX [IX_User_Id] ON [UserLightboxes] DROP INDEX [IX_Lightbox_Id] ON [UserLightboxes] DROP INDEX [IX_Image_Id] ON [ImageLightboxes] DROP INDEX [IX_Lightbox_Id] ON [ImageLightboxes] CREATE TABLE [ImageGalleries] ( [Image_Id] [int] NOT NULL, [Gallery_Id] [int] NOT NULL, CONSTRAINT [PK_ImageGalleries] PRIMARY KEY ([Image_Id], [Gallery_Id]) ) CREATE TABLE [LightboxImages] ( [Lightbox_Id] [int] NOT NULL, [Image_Id] [int] NOT NULL, CONSTRAINT [PK_LightboxImages] PRIMARY KEY ([Lightbox_Id], [Image_Id]) ) CREATE TABLE [LightboxUsers] ( [Lightbox_Id] [int] NOT NULL, [User_Id] [int] NOT NULL, CONSTRAINT [PK_LightboxUsers] PRIMARY KEY ([Lightbox_Id], [User_Id]) ) CREATE INDEX [IX_Image_Id] ON [ImageGalleries]([Image_Id]) CREATE INDEX [IX_Gallery_Id] ON [ImageGalleries]([Gallery_Id]) CREATE INDEX [IX_Lightbox_Id] ON [LightboxImages]([Lightbox_Id]) CREATE INDEX [IX_Image_Id] ON [LightboxImages]([Image_Id]) CREATE INDEX [IX_Lightbox_Id] ON [LightboxUsers]([Lightbox_Id]) CREATE INDEX [IX_User_Id] ON [LightboxUsers]([User_Id]) DROP TABLE [GalleryImages] DROP TABLE [UserLightboxes] DROP TABLE [ImageLightboxes] ALTER TABLE [ImageGalleries] ADD CONSTRAINT [FK_ImageGalleries_Images_Image_Id] FOREIGN KEY ([Image_Id]) REFERENCES [Images] ([Id]) ON DELETE CASCADE ALTER TABLE [ImageGalleries] ADD CONSTRAINT [FK_ImageGalleries_Galleries_Gallery_Id] FOREIGN KEY ([Gallery_Id]) REFERENCES [Galleries] ([Id]) ON DELETE CASCADE ALTER TABLE [LightboxImages] ADD CONSTRAINT [FK_LightboxImages_Lightboxes_Lightbox_Id] FOREIGN KEY ([Lightbox_Id]) REFERENCES [Lightboxes] ([Id]) ON DELETE CASCADE ALTER TABLE [LightboxImages] ADD CONSTRAINT [FK_LightboxImages_Images_Image_Id] FOREIGN KEY ([Image_Id]) REFERENCES [Images] ([Id]) ON DELETE CASCADE ALTER TABLE [LightboxUsers] ADD CONSTRAINT [FK_LightboxUsers_Lightboxes_Lightbox_Id] FOREIGN KEY ([Lightbox_Id]) REFERENCES [Lightboxes] ([Id]) ON DELETE CASCADE ALTER TABLE [LightboxUsers] ADD CONSTRAINT [FK_LightboxUsers_Users_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [Users] ([Id]) ON DELETE CASCADE CREATE TABLE [__MigrationHistory] ( [MigrationId] [nvarchar](255) NOT NULL, [CreatedOn] [datetime] NOT NULL, [Model] [varbinary](max) NOT NULL, [ProductVersion] [nvarchar](32) NOT NULL, CONSTRAINT [PK___MigrationHistory] PRIMARY KEY ([MigrationId]) ) BEGIN TRY EXEC sp_MS_marksystemobject '__MigrationHistory' END TRY BEGIN CATCH END CATCH INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201203030113082_AutomaticMigration', '2012-03-03T01:13:08.986Z', 0x[removedToShortenPost], '4.3.1') </code></pre> <p>As can be seen, the reason why <code>DbMigrator</code> is throwing is because it is attempting to rename 3 tables that are used for joining many2many relationships by inverting the names of tables that they bridge, eg <code>GalleryImages</code> to <code>ImageGalleries</code> or <code>UserLightboxes</code> to <code>LightboxUsers</code>.</p> <h2>A WORKAROUND</h2> <p>This looks like a <strong>bug</strong> in EF 4.3 where the naming of "association" tables appears to be of an indeterminate order. Given that the ordering of names for these sorts of tables appears to be undefined/indeterminate, we approached this from a different angle, using the fluent API to force EF to use the consistent naming across builds from different machines:</p> <pre><code> protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder .Entity<Gallery>() .HasMany(p => p.Images) .WithMany(p => p.Galleries) .Map(c => { c.MapLeftKey("Gallery_Id"); c.MapRightKey("Image_Id"); c.ToTable("GalleryImages"); }); modelBuilder .Entity<User>() .HasMany(p => p.Lightboxes) .WithMany(p => p.Users) .Map(c => { c.MapLeftKey("User_Id"); c.MapRightKey("Lightbox_Id"); c.ToTable("UserLightboxes"); }); modelBuilder .Entity<Image>() .HasMany(p => p.Lightboxes) .WithMany(p => p.Images) .Map(c => { c.MapLeftKey("Image_Id"); c.MapRightKey("Lightbox_Id"); c.ToTable("ImageLightboxes"); }); } </code></pre> <p>With this in place, the error now goes away.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload