Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2014
    Posts
    272

    Entity Framework - rolling back a change with a pivot table

    When I was doing the MMO course the first ever migration had a pivot table in it.
    Subsequent changes to the database never involved a pivot table, just modifying a table or adding a new table.

    Here's the problem - I get an error trying to rollback any change that involves a pivot table. Never have a problem with just a table mod or a new table.

    Here's an example of the Down method from the very first commit in the MMO course:
    Code:
            public override void Down()
            {
                DropForeignKey("dbo.UserRoles", "Role_Id", "dbo.Roles");
                DropForeignKey("dbo.UserRoles", "User_Id", "dbo.Users");
                DropIndex("dbo.UserRoles", new[] { "Role_Id" });
                DropIndex("dbo.UserRoles", new[] { "User_Id" });
                DropTable("dbo.UserRoles");
                DropTable("dbo.Users");
                DropTable("dbo.Roles");
            }
    When rolling back the database with

    Code:
    update-database -TargetMigration:0 -verbose
    I get an error that according to all the google searches seems to be a fairly vague error caused by any number of things.

    Code:
    alter table `dbo.UserRoles` drop foreign key `FK_dbo.UserRoles_dbo.Roles_Role_Id`
    System.Runtime.Serialization.SerializationException: Type is not resolved for member 'MySql.Data.MySqlClient.MySqlException,MySql.Data, Version=6.8.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d'.
       at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
       at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
       at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
       at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
       at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
    Type is not resolved for member 'MySql.Data.MySqlClient.MySqlException,MySql.Data, Version=6.8.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d'.
    In other words it falls at the first hurdle with a 'type is not resolved' error.

    Now it never mattered in the MMO course, but I am currently adding the Post and Tag tables from the SimpleBlog series and I really hate to have a migration that I cannot rollback.

    Tried a lot of things, but nothing's worked so far. Since I'm running out of ideas I thought I would post here just in case there is something simple.

    By the way, deleting the foreign keys, indexes and tables work fine in MySQL workbench.
    Code:
    ALTER TABLE `dev_buzzmmo`.`userroles` 
    DROP FOREIGN KEY `FK_UserRoles_Roles_Role_Id`;
    Last edited by oldngrey; 02-23-2017 at 11:10 PM.

  2. #2
    Join Date
    Feb 2014
    Posts
    272
    And to be totally confusing and contrary to things I've read, if you modify the Down() method and simply delete the lines for DropForeignKey and DropIndex, it will let you delete the tables.
    I thought sql would prevent you deleting a table with foreign key.

    Here's the new Down method that seems to work.

    Code:
            public override void Down()
            {
                //DropForeignKey("dbo.UserRoles", "Role_Id", "dbo.Roles");
                //DropForeignKey("dbo.UserRoles", "User_Id", "dbo.Users");
                //DropIndex("userroles", new[] { "Role_Id" });
                //DropIndex("dbo.UserRoles", new[] { "User_Id" });
                DropTable("dbo.UserRoles");
                DropTable("dbo.Users");
                DropTable("dbo.Roles");
            }

  3. #3
    Join Date
    Mar 2017
    Posts
    0

  4. #4
    Join Date
    Jan 2014
    Location
    Lynnwood, WA
    Posts
    46
    Hi OldnGrey,

    You've solved your issue, but I thought I'd clarify in the event someone else has issues:

    You can't delete a table that has dependencies. E.g. You could not delete users, because userroles is dependent on users with a foreign key. So in the Down() you have to delete userroles first. At this point, no table is dependent on anyone else, so it works fine. Since the end goal is simply removing the three tables, there is no need to modify the userroles prior to deletion (first four commented lines).

    The reason behind this restriction (prevention deletion of parent tables) is that lets say Users was deleted, but Roles & UserRoles were not. When a Join is later attempted, the FK to Users from UserRoles goes nowhere which would cause an exception (in this instance, the relationship was preserved to data that no longer exists). In other instances you could end up with data that is stranded or improperly related, which effectively breaks down the relationships within the database, corrupting it. Since data integrity & relationships are the prime role of a relational database, the order of deletion has to be controlled to maintain integrity.

  5. #5
    Join Date
    Feb 2014
    Posts
    272
    Well, sort of. I was able to get the job done, but it left me with an unanswered question.
    I was fully aware of dependencies, but if you look at the code snippet you will see that I got a 'type is not resolved' error when deleting a foreign key, something I could happily delete from within MySQL workbench.

    I didn't create the delete script either. It was added to the down method automatically when using Entity Framework to create the migration that makes a pivot table and its relationships.

    To put it in slightly different words, it was the automatically generated down method that could not delete a fk from a pivot table relationship.

    'type is not resolved' errors can be caused by multiple things, but I eliminated as many causes as I could. I was simply wondering if anybody else had ever had that sort of problem. No biggie. It's not like I need the answer to continue a project.


    EDIT:
    Nelson comes across this one in the MMO video near the end called "Game Server 09 - Client Authentication"
    Take away the dbo. from the Up() and Down() methods.
    Thanks Microsoft for making Entity Framework only really MSSql compatible.
    Last edited by oldngrey; 05-14-2017 at 08:22 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •