Entity framework 意外删除了表，如何在不影响其它表的情况下恢复回来
关于EntityFramework数据迁移原理
查询数据库的表"__MigrationHistory"，遍历代码库的Migrations文件夹下的所有文件，如果文件不在__MigrationHistory表内，那么就执行迁移。
有了上面的原理之后，我们来看一下如果我们不小心手动删除了一个表，如何在不影响其它表的情况下来恢复你删除的表：
方法一：
关于Model 以及 DBContext如下：
public class Blog
{
public int BlogId { get; set; }
public string Name { get; set; }
public string Url { get; set; }
public virtual List<Post> Posts { get; set; }
}
public class User
{
[Key]
public int UserId { get; set; }
public string Username { get; set; }
public string DisplayName { get; set; }
//public int? age { get; set; }
//public string interest { get; set; }
}
public class School
{
public int SchoolId { get; set; }
public string SchoolName { get; set; }
public int SchoolLevel { get; set; }
}
public class Teacher
{
[Key]
public int TecherId { get; set; }
public string TeacherName { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public virtual Blog Blog { get; set; }
}
public class Tutorial
{
[Key]
public int Id { get; set; }
public int Name { get; set; }
}
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
public DbSet<User> Users { get; set; }
public DbSet<Tutorial> Tutorials { get; set; }
public DbSet<School> Schools { get; set; }
public DbSet<Teacher> Teachers { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<User>().Property(u => u.DisplayName).HasColumnName("display_name");
modelBuilder.Entity<User>().Property(u => u.Username).HasColumnName("user_name");
}
}
migrations 文件夹下的文件如下：
对应数据库中的记录为：
假如此时我们手动删除了schools表，此时我们应该找到schools表的创建与修改的迁移code是在哪个Migrations文件夹下面的哪个文件，找到对应的文件
我们这里是在201503190341085_addmodels.cs中，
public partial class addmodels : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Blogs",
c => new
{
BlogId = c.Int(nullable: false, identity: true),
Name = c.String(),
Url = c.String(),
})
.PrimaryKey(t => t.BlogId);
CreateTable(
"dbo.Posts",
c => new
{
PostId = c.Int(nullable: false, identity: true),
Title = c.String(),
Content = c.String(),
BlogId = c.Int(nullable: false),
})
.PrimaryKey(t => t.PostId)
.ForeignKey("dbo.Blogs", t => t.BlogId, cascadeDelete: true)
.Index(t => t.BlogId);
CreateTable(
"dbo.Schools",
c => new
{
SchoolId = c.Int(nullable: false, identity: true),
SchoolName = c.String(),
SchoolLevel = c.Int(nullable: false),
})
.PrimaryKey(t => t.SchoolId);
CreateTable(
"dbo.Tutorials",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id);
CreateTable(
"dbo.Users",
c => new
{
UserId = c.Int(nullable: false, identity: true),
user_name = c.String(),
display_name = c.String(),
})
.PrimaryKey(t => t.UserId);
}
public override void Down()
{
DropForeignKey("dbo.Posts", "BlogId", "dbo.Blogs");
DropIndex("dbo.Posts", new[] { "BlogId" });
DropTable("dbo.Users");
DropTable("dbo.Tutorials");
DropTable("dbo.Schools");
DropTable("dbo.Posts");
DropTable("dbo.Blogs");
}
}
此时我们要做的只要把这个文件名201503190341085_addmodels.cs对应在数据迁移表中的记录删除，
delete __MigrationHistory where MigrationId = '201503190341085_addmodels' ,然后我们在201503190341085_addmodels.cs注释掉其它的迁移数据，只留下创建shools表的数据
public partial class addmodels : DbMigration
{
public override void Up()
{
//CreateTable(
// "dbo.Blogs",
// c => new
// {
// BlogId = c.Int(nullable: false, identity: true),
// Name = c.String(),
// Url = c.String(),
// })
// .PrimaryKey(t => t.BlogId);
//CreateTable(
// "dbo.Posts",
// c => new
// {
// PostId = c.Int(nullable: false, identity: true),
// Title = c.String(),
// Content = c.String(),
// BlogId = c.Int(nullable: false),
// })
// .PrimaryKey(t => t.PostId)
// .ForeignKey("dbo.Blogs", t => t.BlogId, cascadeDelete: true)
// .Index(t => t.BlogId);
CreateTable(
"dbo.Schools",
c => new
{
SchoolId = c.Int(nullable: false, identity: true),
SchoolName = c.String(),
SchoolLevel = c.Int(nullable: false),
})
.PrimaryKey(t => t.SchoolId);
//CreateTable(
// "dbo.Tutorials",
// c => new
// {
// Id = c.Int(nullable: false, identity: true),
// Name = c.Int(nullable: false),
// })
// .PrimaryKey(t => t.Id);
//CreateTable(
// "dbo.Users",
// c => new
// {
// UserId = c.Int(nullable: false, identity: true),
// user_name = c.String(),
// display_name = c.String(),
// })
// .PrimaryKey(t => t.UserId);
}
public override void Down()
{
//DropForeignKey("dbo.Posts", "BlogId", "dbo.Blogs");
//DropIndex("dbo.Posts", new[] { "BlogId" });
//DropTable("dbo.Users");
//DropTable("dbo.Tutorials");
DropTable("dbo.Schools");
//DropTable("dbo.Posts");
//DropTable("dbo.Blogs");
}
}
此时如果你在package-manager console 中执行update-database 即可完成schools表的恢复
方法二：
利用 Update-Database -Script -SourceMigration $InitialDatabase （这是一个变量不需要你去改成你自己的数据库名字） 或者
Update-Database -Script -SourceMigration Second -TargetMigration First (用这两个中的哪个是视情况而定的)
(Second First 就是你的migrations文件夹下的文件名字的后面那个 比喻 201503190906406_addmodels 你就输入 Update-Database -Script -SourceMigration addmodels)
这样会产生一个脚本文件，你在那个脚本文件中找出你恢复的表的一些创建或者修改信息
我找到了Schools的创建SQL , 在数据库中执行即可：
CREATE TABLE [dbo].[Schools] ( [SchoolId] [int] NOT NULL IDENTITY, [SchoolName] [nvarchar](max), [SchoolLevel] [int] NOT NULL, CONSTRAINT [PK_dbo.Schools] PRIMARY KEY ([SchoolId]) )
这样你就恢复好了
