Modifying Migration Classes12:47 with James Churchill
Let's look at example of a model change that will require us to modify the generated migration in order for it to successfully upgrade and downgrade the database.
To follow along commiting your changes to this course, you'll need to fork the dotnet-ef-migrations repo. Then you can clone, commit, and push your changes to your fork like this:
git clone <your-fork> cd dotnet-ef-migrations git checkout tags/v2.2 -b modifying-migration-classes
Here are the SQL statements to migration average rating data from the ComicBook table to the ComicBookAverageRating table and back again.
insert into ComicBookAverageRating select Id, AverageRating, getdate() from ComicBook where AverageRating is not null update cb set cb.AverageRating = cbar.AverageRating from ComicBook cb cross apply ( select top 1 AverageRating, Date from ComicBookAverageRating where ComicBookId = cb.Id order by Date desc ) as cbar
In most situations, 0:00 the migrations that EF generates force will work without any modification. 0:01 That being said, 0:06 there are situations that arise when EF isn't able to do all the work for us. 0:07 Let's take a look at one of those situations. 0:13 Currently, our model supports a single average rating value for each comic book. 0:16 Recently, a number of users have requested 0:21 to be able to see how a comic book's average rating changes over time. 0:24 In order to implement this feature, our model needs to support one or 0:28 more average rating values per comic book. 0:32 To do this, we'll move the ComicBook Average Rating property to its own 0:35 ComicBookAverage Rating entity. 0:39 That entity will also have a Date property. 0:42 So, we'll know when an average rating was calculated. 0:44 Then, we'll configure a one to many relationship between the ComicBook and 0:48 ComicBookAverageRating entities. 0:53 When changing the cardinality of data in our model, 0:56 migrations is unable to generate the code that is necessary to transform existing 1:00 data in our database into the new model. 1:05 But see how we can manually update the generated migration to work 1:07 around this limitation. 1:11 Right-click on the model's folder and 1:12 select Add, Class. 1:15 I'll name the new entity class ComicBookAverageRating and 1:20 press Enter to finish adding the class. 1:26 Add the Public Access modifier and the following properties. 1:37 An int property named Id. 1:42 This property will contain a unique value for 1:45 each ComicBookAverageRating in our system. 1:48 An int property named ComicBookId. 1:52 This is a foreign key property to the comic book that we're 1:55 adding an average rating for. 1:59 A decimal property named AverageRating. 2:03 This property, taken from the comic book entity, will store the average value 2:09 of all of the ratings that the associated comic book has received. 2:13 A DateTime property named Date. 2:20 This property is the date that the average rating was calculated on. 2:24 Let's also add a navigation property to the related comic book. 2:29 ComicBook. 2:38 In the ComicBook entity class, we can now remove the average rating property. 2:43 We also need to add a new average ratings navigation collection property. 2:52 ICollection of type ComicBookAverageRating. 3:00 AverageRatings. 3:06 And update the default constructor to initialize the collection. 3:11 AverageRatings = new List<ComicBookAverageRating>(); 3:19 >> In the context classes 3:32 on model creating method, we need to update our fluid 3:34 API calls that we're making to configure the coming book average rating 3:37 property to configure the new comic book average rating average rating property. 3:41 We can do that by just changing the entity methods generic type parameter from 3:49 ComicBook to ComicBookAverageRating. 3:54 Let's also temporarily remove our test data from the configuration class. 4:02 To do that, 4:13 we can just comment out all of the code between our if preprocessor directive. 4:13 Once you selected the code, press Ctrl + K, Ctrl + C to comment it out. 4:18 Our database already contains our test data, so, 4:26 removing it from the seed method will allow us to imagine that it's actual 4:29 production data that we need to migrate to our new model. 4:34 Press Ctrl + Shift + B to make sure that the project is still building. 4:38 Great, in the Output window, 4:42 we can see that one project was successfully built and zero failed. 4:44 Now that we've made our model changes, we need to add the migration. 4:49 Add-migration 5:00 AddComicBookAverageRatingntEntity. 5:04 Let's review the generated migration class. 5:14 In the up method, we can see the CreateTable method call for 5:22 creating the new comic book average rating database table. 5:25 And here's the DropColumn method call 5:30 to drop the average rating column from the comic book table. 5:32 In the down method, we can see the AddColumn method call for 5:39 adding the average rating column back to the comic book table. 5:42 And here's the method calls to remove the ComicBookAverageRating table and 5:47 its related foreign key and index. 5:51 What's missing in this migration is a method call or 5:58 operation to migrate any existing average rating data in the ComicBook table to 6:02 the new ComicBookAverageRating table when we're upgrading the database. 6:07 We're also missing a method call or 6:13 operation to migrate the data from the ComicBookAverageRating table 6:15 back to the ComicBook table if we were to downgrade the database. 6:20 Let's start with updating the Up method. 6:25 We need to populate the ComicBookAverageRating table just after 6:27 it's created but before the ComicBookAverageRating column is dropped. 6:32 Running the SQL statement right after the Create method call will do the trick. 6:42 / / Populate the ComicBookAverageRating table. 6:47 The migration fluid API provides a SQLmethod that 6:51 we can use to execute a SQL statement as part of the migration. 6:56 The first and only parameter that will supply is 7:03 a string representing the SQL statement to execute. 7:07 Place an @ symbol before the pair of quotes in order to define a verbatim 7:15 string literal. 7:20 This will allow us to include line breaks in the string, 7:22 which will make it easier to read. 7:24 To save us some time, 7:30 I've already written in Notepad the SQL statements that we need. 7:31 If you're following along, see the teacher's notes for this code. 7:44 This SQL statement inserts a row into the ComicBookAverageRating table for 7:48 each row in the ComicBook table. 7:54 That has an average rating column value. 7:56 In the Down method, we need to populate the ComicBookAverageRating column values 8:03 after the column is added to the ComicBook table. 8:08 But before the ComicBookAverageRating table is dropped, 8:12 writing a SQL statement right after the AddColumn method will do the trick. 8:16 / / Populate 8:26 the ComicBook.AverageRating column. 8:29 Just like with the Up method, I've already written the SQL statement in Notepad. 8:41 Writing the SQL statement to migrate our data from the ComicBookAverageRating 8:57 table to the ComicBook table is more complicated. 9:02 We'll use an update statement to set the average rating column values, but 9:06 we need a way to retrieve the latest average rating value for each comic book. 9:11 Cross apply allows us to evaluate a table valued expression for 9:17 each row in the ComicBook table. 9:21 In our case, that table valued expression is a subquery that returns 9:24 the top one average rating column value from the ComicBookAverageRating table. 9:29 For each ComicBookId column value in the ComicBook table. 9:35 Notice that we're sorting the results of the subquery by the ComicBookAverageRating 9:41 date column in descending order. 9:45 This ensures that we'll get the latest average rating column value for 9:48 each comic book. 9:52 Now, let's update our database. 9:58 update-database. 10:01 Let's review the data in the ComicBook and ComicBookAverageRating tables. 10:13 In the ComicBook table, 10:23 here's the rows for our three comic books without the average rating column values. 10:24 In the ComicBookAverageRating table, we have three rows. 10:35 One row for each of our comic books. 10:40 This verifies that our upgrade migration worked as expected. 10:43 Let's imagine that over time one of our comic books receives some additional 10:47 average rating values. 10:51 I'll just manually enter two additional rows for our first comic book. 10:52 ComicBookId 1 with a rating of 10:57 7.2 on 1/14/2017. 11:02 And ComicBookId with a rating of 11:08 7.3 on 1/15/2017. 11:12 So, now, the comic book with an ID value of one has three average rating values. 11:17 Let's downgrade the database to the previous migration. 11:24 Update-database -targetmigration 11:38 AddBioPropertyToArtist. 11:46 Refresh the table list and view the ComicBook table data. 11:55 The average rating column is back, 12:04 and it's populated with the latest average rating value for each comic book. 12:06 And the ComicBookAverageRating table has been dropped from the database. 12:12 It's important to ensure that your migrations don't destroy 12:19 any production data. 12:23 Your users will appreciate your extra effort and attention to detail. 12:25 Whenever you add a migration to your project, 12:29 it's also important to properly test the effects of that migration 12:32 before you apply it to your production environment. 12:35 In the next video, we'll take a look at an example development, testing, and 12:39 deployment workflow that utilizes multiple environments. 12:43
You need to sign up for Treehouse in order to download course files.Sign up