ASP.NET Core创建Web Api项目使用EF自动迁移多表数据库

2019-08-23 11:07:09

1. 项目创建

1.1 安装

　　下载.NETCORE SDK 进行安装

　　下载NETCORE RUNTIME进行安装.

　　下载Runtime & Hosting Bundle进行安装

　　下载地址:https://dotnet.microsoft.com/download/dotnet-core

　　微软的安装在windows方面很简单，基本就是一键安装.

1.2 开发工具

VisualStudio 2019

1.3建立项目工程

　　点击文件创建项目，出现如下图示

　　选择画红圈部分，接着出现如下图示

1.4 新建Models文件夹，创建数据库实体类

1.5 创建数据库上下文类

　　创建继承DbContext的上下文类，将数据库实体添加到DbSet中，重写OnModelCreating方

 1 public class SchoolContext : DbContext
 2     {
 3         public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
 4         {
 5         }
 6 
 7         public DbSet<Course> Courses { get; set; }
 8         public DbSet<Enrollment> Enrollments { get; set; }
 9         public DbSet<Student> Students { get; set; }
10         public DbSet<Department> Departments { get; set; }
11         public DbSet<Instructor> Instructors { get; set; }
12         public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
13         public DbSet<CourseAssignment> CourseAssignments { get; set; }
14 
15         protected override void OnModelCreating(ModelBuilder modelBuilder)
16         {
17             modelBuilder.Entity<Course>().ToTable("Course");
18             modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
19             modelBuilder.Entity<Student>().ToTable("Student");
20             modelBuilder.Entity<Department>().ToTable("Department");
21             modelBuilder.Entity<Instructor>().ToTable("Instructor");
22             modelBuilder.Entity<OfficeAssignment>().ToTable("OfficeAssignment");
23             modelBuilder.Entity<CourseAssignment>().ToTable("CourseAssignment");
24 
25             modelBuilder.Entity<CourseAssignment>()
26                 .HasKey(c => new { c.CourseID, c.InstructorID });
27         }
28     }

1.6使用测试数据设定数据库种子

  1 using System;
  2 using System.Linq;
  3 using Microsoft.EntityFrameworkCore;
  4 using Microsoft.Extensions.DependencyInjection;
  5 using ContosoUniversity.Models;
  6 
  7 namespace ContosoUniversity.Data
  8 {
  9     public static class DbInitializer
 10     {
 11         public static void Initialize(SchoolContext context)
 12         {
 13             //context.Database.EnsureCreated();
 14 
 15             // Look for any students.
 16             if (context.Students.Any())
 17             {
 18                 return;   // DB has been seeded
 19             }
 20 
 21             var students = new Student[]
 22             {
 23                 new Student { FirstMidName = "Carson",   LastName = "Alexander",
 24                     EnrollmentDate = DateTime.Parse("2010-09-01") },
 25                 new Student { FirstMidName = "Meredith", LastName = "Alonso",
 26                     EnrollmentDate = DateTime.Parse("2012-09-01") },
 27                 new Student { FirstMidName = "Arturo",   LastName = "Anand",
 28                     EnrollmentDate = DateTime.Parse("2013-09-01") },
 29                 new Student { FirstMidName = "Gytis",    LastName = "Barzdukas",
 30                     EnrollmentDate = DateTime.Parse("2012-09-01") },
 31                 new Student { FirstMidName = "Yan",      LastName = "Li",
 32                     EnrollmentDate = DateTime.Parse("2012-09-01") },
 33                 new Student { FirstMidName = "Peggy",    LastName = "Justice",
 34                     EnrollmentDate = DateTime.Parse("2011-09-01") },
 35                 new Student { FirstMidName = "Laura",    LastName = "Norman",
 36                     EnrollmentDate = DateTime.Parse("2013-09-01") },
 37                 new Student { FirstMidName = "Nino",     LastName = "Olivetto",
 38                     EnrollmentDate = DateTime.Parse("2005-09-01") }
 39             };
 40 
 41             foreach (Student s in students)
 42             {
 43                 context.Students.Add(s);
 44             }
 45             context.SaveChanges();
 46 
 47             var instructors = new Instructor[]
 48             {
 49                 new Instructor { FirstMidName = "Kim",     LastName = "Abercrombie",
 50                     HireDate = DateTime.Parse("1995-03-11") },
 51                 new Instructor { FirstMidName = "Fadi",    LastName = "Fakhouri",
 52                     HireDate = DateTime.Parse("2002-07-06") },
 53                 new Instructor { FirstMidName = "Roger",   LastName = "Harui",
 54                     HireDate = DateTime.Parse("1998-07-01") },
 55                 new Instructor { FirstMidName = "Candace", LastName = "Kapoor",
 56                     HireDate = DateTime.Parse("2001-01-15") },
 57                 new Instructor { FirstMidName = "Roger",   LastName = "Zheng",
 58                     HireDate = DateTime.Parse("2004-02-12") }
 59             };
 60 
 61             foreach (Instructor i in instructors)
 62             {
 63                 context.Instructors.Add(i);
 64             }
 65             context.SaveChanges();
 66 
 67             var departments = new Department[]
 68             {
 69                 new Department { Name = "English",     Budget = 350000,
 70                     StartDate = DateTime.Parse("2007-09-01"),
 71                     InstructorID  = instructors.Single( i => i.LastName == "Abercrombie").ID },
 72                 new Department { Name = "Mathematics", Budget = 100000,
 73                     StartDate = DateTime.Parse("2007-09-01"),
 74                     InstructorID  = instructors.Single( i => i.LastName == "Fakhouri").ID },
 75                 new Department { Name = "Engineering", Budget = 350000,
 76                     StartDate = DateTime.Parse("2007-09-01"),
 77                     InstructorID  = instructors.Single( i => i.LastName == "Harui").ID },
 78                 new Department { Name = "Economics",   Budget = 100000,
 79                     StartDate = DateTime.Parse("2007-09-01"),
 80                     InstructorID  = instructors.Single( i => i.LastName == "Kapoor").ID }
 81             };
 82 
 83             foreach (Department d in departments)
 84             {
 85                 context.Departments.Add(d);
 86             }
 87             context.SaveChanges();
 88 
 89             var courses = new Course[]
 90             {
 91                 new Course {CourseID = 1050, Title = "Chemistry",      Credits = 3,
 92                     DepartmentID = departments.Single( s => s.Name == "Engineering").DepartmentID
 93                 },
 94                 new Course {CourseID = 4022, Title = "Microeconomics", Credits = 3,
 95                     DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID
 96                 },
 97                 new Course {CourseID = 4041, Title = "Macroeconomics", Credits = 3,
 98                     DepartmentID = departments.Single( s => s.Name == "Economics").DepartmentID
 99                 },
100                 new Course {CourseID = 1045, Title = "Calculus",       Credits = 4,
101                     DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID
102                 },
103                 new Course {CourseID = 3141, Title = "Trigonometry",   Credits = 4,
104                     DepartmentID = departments.Single( s => s.Name == "Mathematics").DepartmentID
105                 },
106                 new Course {CourseID = 2021, Title = "Composition",    Credits = 3,
107                     DepartmentID = departments.Single( s => s.Name == "English").DepartmentID
108                 },
109                 new Course {CourseID = 2042, Title = "Literature",     Credits = 4,
110                     DepartmentID = departments.Single( s => s.Name == "English").DepartmentID
111                 },
112             };
113 
114             foreach (Course c in courses)
115             {
116                 context.Courses.Add(c);
117             }
118             context.SaveChanges();
119 
120             var officeAssignments = new OfficeAssignment[]
121             {
122                 new OfficeAssignment {
123                     InstructorID = instructors.Single( i => i.LastName == "Fakhouri").ID,
124                     Location = "Smith 17" },
125                 new OfficeAssignment {
126                     InstructorID = instructors.Single( i => i.LastName == "Harui").ID,
127                     Location = "Gowan 27" },
128                 new OfficeAssignment {
129                     InstructorID = instructors.Single( i => i.LastName == "Kapoor").ID,
130                     Location = "Thompson 304" },
131             };
132 
133             foreach (OfficeAssignment o in officeAssignments)
134             {
135                 context.OfficeAssignments.Add(o);
136             }
137             context.SaveChanges();
138 
139             var courseInstructors = new CourseAssignment[]
140             {
141                 new CourseAssignment {
142                     CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
143                     InstructorID = instructors.Single(i => i.LastName == "Kapoor").ID
144                     },
145                 new CourseAssignment {
146                     CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
147                     InstructorID = instructors.Single(i => i.LastName == "Harui").ID
148                     },
149                 new CourseAssignment {
150                     CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID,
151                     InstructorID = instructors.Single(i => i.LastName == "Zheng").ID
152                     },
153                 new CourseAssignment {
154                     CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID,
155                     InstructorID = instructors.Single(i => i.LastName == "Zheng").ID
156                     },
157                 new CourseAssignment {
158                     CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID,
159                     InstructorID = instructors.Single(i => i.LastName == "Fakhouri").ID
160                     },
161                 new CourseAssignment {
162                     CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID,
163                     InstructorID = instructors.Single(i => i.LastName == "Harui").ID
164                     },
165                 new CourseAssignment {
166                     CourseID = courses.Single(c => c.Title == "Composition" ).CourseID,
167                     InstructorID = instructors.Single(i => i.LastName == "Abercrombie").ID
168                     },
169                 new CourseAssignment {
170                     CourseID = courses.Single(c => c.Title == "Literature" ).CourseID,
171                     InstructorID = instructors.Single(i => i.LastName == "Abercrombie").ID
172                     },
173             };
174 
175             foreach (CourseAssignment ci in courseInstructors)
176             {
177                 context.CourseAssignments.Add(ci);
178             }
179             context.SaveChanges();
180 
181             var enrollments = new Enrollment[]
182             {
183                 new Enrollment {
184                     StudentID = students.Single(s => s.LastName == "Alexander").ID,
185                     CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID,
186                     Grade = Grade.A
187                 },
188                     new Enrollment {
189                     StudentID = students.Single(s => s.LastName == "Alexander").ID,
190                     CourseID = courses.Single(c => c.Title == "Microeconomics" ).CourseID,
191                     Grade = Grade.C
192                     },
193                     new Enrollment {
194                     StudentID = students.Single(s => s.LastName == "Alexander").ID,
195                     CourseID = courses.Single(c => c.Title == "Macroeconomics" ).CourseID,
196                     Grade = Grade.B
197                     },
198                     new Enrollment {
199                         StudentID = students.Single(s => s.LastName == "Alonso").ID,
200                     CourseID = courses.Single(c => c.Title == "Calculus" ).CourseID,
201                     Grade = Grade.B
202                     },
203                     new Enrollment {
204                         StudentID = students.Single(s => s.LastName == "Alonso").ID,
205                     CourseID = courses.Single(c => c.Title == "Trigonometry" ).CourseID,
206                     Grade = Grade.B
207                     },
208                     new Enrollment {
209                     StudentID = students.Single(s => s.LastName == "Alonso").ID,
210                     CourseID = courses.Single(c => c.Title == "Composition" ).CourseID,
211                     Grade = Grade.B
212                     },
213                     new Enrollment {
214                     StudentID = students.Single(s => s.LastName == "Anand").ID,
215                     CourseID = courses.Single(c => c.Title == "Chemistry" ).CourseID
216                     },
217                     new Enrollment {
218                     StudentID = students.Single(s => s.LastName == "Anand").ID,
219                     CourseID = courses.Single(c => c.Title == "Microeconomics").CourseID,
220                     Grade = Grade.B
221                     },
222                 new Enrollment {
223                     StudentID = students.Single(s => s.LastName == "Barzdukas").ID,
224                     CourseID = courses.Single(c => c.Title == "Chemistry").CourseID,
225                     Grade = Grade.B
226                     },
227                     new Enrollment {
228                     StudentID = students.Single(s => s.LastName == "Li").ID,
229                     CourseID = courses.Single(c => c.Title == "Composition").CourseID,
230                     Grade = Grade.B
231                     },
232                     new Enrollment {
233                     StudentID = students.Single(s => s.LastName == "Justice").ID,
234                     CourseID = courses.Single(c => c.Title == "Literature").CourseID,
235                     Grade = Grade.B
236                     }
237             };
238 
239             foreach (Enrollment e in enrollments)
240             {
241                 var enrollmentInDataBase = context.Enrollments.Where(
242                     s =>
243                             s.Student.ID == e.StudentID &&
244                             s.Course.CourseID == e.CourseID).SingleOrDefault();
245                 if (enrollmentInDataBase == null)
246                 {
247                     context.Enrollments.Add(e);
248                 }
249             }
250             context.SaveChanges();
251         }
252     }
253 }

1.7在appsetting.json文件中配置本地数据库连接

1 "ConnectionStrings": {
2     "SchoolContext": "Server=.;Database=ApiDb;user id=sa;password=123456;"
3   }

1.8在Startup.cs文件中配置使用sql server

 1 public void ConfigureServices(IServiceCollection services)
 2 {
 3       services.AddDbContext<SchoolContext>(opt =>
 4         opt.UseSqlServer(Configuration.GetConnectionString("SchoolContext")));
 5       services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
 6       services.AddSwaggerGen(c =>
 7       {
 8         c.SwaggerDoc("v1", new OpenApiInfo { Title = "My API", Version = "v1" });
 9       });
10 }

1.9在Program.cs文件中调用测试数据

 1 public class Program
 2 {
 3     public static void Main(string[] args)
 4     {
 5         var host = CreateWebHostBuilder(args).Build();
 6 
 7         using (var scope = host.Services.CreateScope())
 8         {
 9             var services = scope.ServiceProvider;
10             try
11             {
12                 var context = services.GetRequiredService<SchoolContext>();
13                 DbInitializer.Initialize(context);
14             }
15             catch (Exception ex)
16             {
17                 var logger = services.GetRequiredService<ILogger<Program>>();
18                 logger.LogError(ex, "An error occurred while seeding the database.");
19             }
20         }
21 
22         host.Run();
23 }

2. EF数据库迁移

2.1首先在本地数据库，创建一个空的数据库

2.2执行数据库迁移

　　保存项目更改，生成项目。然后打开CMD命令窗口，切换到工程目录下，执行以下命令

dotnet ef migrations add ComplexDataModel

2.3更新数据库

　　更改数据库后或删除数据库后，在命令窗口运行以下命令

dotnet ef database update

2.4查看数据库

　　打开SQL Server Management，查看数据库表生成情况

2.5运行项目，生成测试数据

　　测试数据已自动生成