1 00:00:00,320 --> 00:00:01,850 In most situations, 2 00:00:01,850 --> 00:00:06,500 the migrations that EF generates force will work without any modification. 3 00:00:06,500 --> 00:00:07,650 That being said, 4 00:00:07,650 --> 00:00:13,120 there are situations that arise when EF isn't able to do all the work for us. 5 00:00:13,120 --> 00:00:14,990 Let's take a look at one of those situations. 6 00:00:16,280 --> 00:00:21,570 Currently, our model supports a single average rating value for each comic book. 7 00:00:21,570 --> 00:00:24,140 Recently, a number of users have requested 8 00:00:24,140 --> 00:00:28,710 to be able to see how a comic book's average rating changes over time. 9 00:00:28,710 --> 00:00:32,390 In order to implement this feature, our model needs to support one or 10 00:00:32,390 --> 00:00:35,280 more average rating values per comic book. 11 00:00:35,280 --> 00:00:39,639 To do this, we'll move the ComicBook Average Rating property to its own 12 00:00:39,639 --> 00:00:42,350 ComicBookAverage Rating entity. 13 00:00:42,350 --> 00:00:44,990 That entity will also have a Date property. 14 00:00:44,990 --> 00:00:48,160 So, we'll know when an average rating was calculated. 15 00:00:48,160 --> 00:00:53,810 Then, we'll configure a one to many relationship between the ComicBook and 16 00:00:53,810 --> 00:00:56,650 ComicBookAverageRating entities. 17 00:00:56,650 --> 00:01:00,294 When changing the cardinality of data in our model, 18 00:01:00,294 --> 00:01:05,226 migrations is unable to generate the code that is necessary to transform existing 19 00:01:05,226 --> 00:01:07,662 data in our database into the new model. 20 00:01:07,662 --> 00:01:11,076 But see how we can manually update the generated migration to work 21 00:01:11,076 --> 00:01:12,475 around this limitation. 22 00:01:12,475 --> 00:01:15,570 Right-click on the model's folder and 23 00:01:15,570 --> 00:01:20,910 select Add, Class. 24 00:01:20,910 --> 00:01:26,528 I'll name the new entity class ComicBookAverageRating and 25 00:01:26,528 --> 00:01:30,280 press Enter to finish adding the class. 26 00:01:37,174 --> 00:01:40,920 Add the Public Access modifier and the following properties. 27 00:01:42,310 --> 00:01:44,030 An int property named Id. 28 00:01:45,720 --> 00:01:48,030 This property will contain a unique value for 29 00:01:48,030 --> 00:01:50,330 each ComicBookAverageRating in our system. 30 00:01:52,510 --> 00:01:55,665 An int property named ComicBookId. 31 00:01:55,665 --> 00:01:59,634 This is a foreign key property to the comic book that we're 32 00:01:59,634 --> 00:02:01,740 adding an average rating for. 33 00:02:03,640 --> 00:02:06,190 A decimal property named AverageRating. 34 00:02:09,430 --> 00:02:13,820 This property, taken from the comic book entity, will store the average value 35 00:02:13,820 --> 00:02:17,180 of all of the ratings that the associated comic book has received. 36 00:02:20,650 --> 00:02:23,070 A DateTime property named Date. 37 00:02:24,560 --> 00:02:27,960 This property is the date that the average rating was calculated on. 38 00:02:29,500 --> 00:02:32,977 Let's also add a navigation property to the related comic book. 39 00:02:38,008 --> 00:02:39,122 ComicBook. 40 00:02:43,847 --> 00:02:48,010 In the ComicBook entity class, we can now remove the average rating property. 41 00:02:52,096 --> 00:02:56,460 We also need to add a new average ratings navigation collection property. 42 00:03:00,521 --> 00:03:04,840 ICollection of type ComicBookAverageRating. 43 00:03:06,850 --> 00:03:07,580 AverageRatings. 44 00:03:11,390 --> 00:03:14,291 And update the default constructor to initialize the collection. 45 00:03:19,455 --> 00:03:24,594 AverageRatings = new List(); 46 00:03:32,707 --> 00:03:34,031 >> In the context classes 47 00:03:34,031 --> 00:03:37,165 on model creating method, we need to update our fluid 48 00:03:37,165 --> 00:03:41,415 API calls that we're making to configure the coming book average rating 49 00:03:41,415 --> 00:03:46,310 property to configure the new comic book average rating average rating property. 50 00:03:49,610 --> 00:03:54,596 We can do that by just changing the entity methods generic type parameter from 51 00:03:54,596 --> 00:03:57,289 ComicBook to ComicBookAverageRating. 52 00:04:02,460 --> 00:04:06,496 Let's also temporarily remove our test data from the configuration class. 53 00:04:13,125 --> 00:04:13,851 To do that, 54 00:04:13,851 --> 00:04:18,678 we can just comment out all of the code between our if preprocessor directive. 55 00:04:18,678 --> 00:04:24,960 Once you selected the code, press Ctrl + K, Ctrl + C to comment it out. 56 00:04:26,947 --> 00:04:29,969 Our database already contains our test data, so, 57 00:04:29,969 --> 00:04:34,327 removing it from the seed method will allow us to imagine that it's actual 58 00:04:34,327 --> 00:04:38,350 production data that we need to migrate to our new model. 59 00:04:38,350 --> 00:04:42,690 Press Ctrl + Shift + B to make sure that the project is still building. 60 00:04:42,690 --> 00:04:44,700 Great, in the Output window, 61 00:04:44,700 --> 00:04:49,300 we can see that one project was successfully built and zero failed. 62 00:04:49,300 --> 00:04:52,380 Now that we've made our model changes, we need to add the migration. 63 00:05:00,567 --> 00:05:04,571 Add-migration 64 00:05:04,571 --> 00:05:14,590 AddComicBookAverageRatingntEntity. 65 00:05:14,590 --> 00:05:17,020 Let's review the generated migration class. 66 00:05:22,269 --> 00:05:25,434 In the up method, we can see the CreateTable method call for 67 00:05:25,434 --> 00:05:28,680 creating the new comic book average rating database table. 68 00:05:30,250 --> 00:05:32,730 And here's the DropColumn method call 69 00:05:32,730 --> 00:05:35,490 to drop the average rating column from the comic book table. 70 00:05:39,440 --> 00:05:42,580 In the down method, we can see the AddColumn method call for 71 00:05:42,580 --> 00:05:47,250 adding the average rating column back to the comic book table. 72 00:05:47,250 --> 00:05:51,710 And here's the method calls to remove the ComicBookAverageRating table and 73 00:05:51,710 --> 00:05:53,370 its related foreign key and index. 74 00:05:58,997 --> 00:06:02,454 What's missing in this migration is a method call or 75 00:06:02,454 --> 00:06:07,721 operation to migrate any existing average rating data in the ComicBook table to 76 00:06:07,721 --> 00:06:13,650 the new ComicBookAverageRating table when we're upgrading the database. 77 00:06:13,650 --> 00:06:15,910 We're also missing a method call or 78 00:06:15,910 --> 00:06:20,660 operation to migrate the data from the ComicBookAverageRating table 79 00:06:20,660 --> 00:06:25,570 back to the ComicBook table if we were to downgrade the database. 80 00:06:25,570 --> 00:06:27,598 Let's start with updating the Up method. 81 00:06:27,598 --> 00:06:32,661 We need to populate the ComicBookAverageRating table just after 82 00:06:32,661 --> 00:06:38,469 it's created but before the ComicBookAverageRating column is dropped. 83 00:06:42,583 --> 00:06:46,610 Running the SQL statement right after the Create method call will do the trick. 84 00:06:47,893 --> 00:06:51,779 / / Populate the ComicBookAverageRating table. 85 00:06:51,779 --> 00:06:56,976 The migration fluid API provides a SQLmethod that 86 00:06:56,976 --> 00:07:03,422 we can use to execute a SQL statement as part of the migration. 87 00:07:03,422 --> 00:07:07,172 The first and only parameter that will supply is 88 00:07:07,172 --> 00:07:11,400 a string representing the SQL statement to execute. 89 00:07:15,689 --> 00:07:20,234 Place an @ symbol before the pair of quotes in order to define a verbatim 90 00:07:20,234 --> 00:07:22,020 string literal. 91 00:07:22,020 --> 00:07:24,730 This will allow us to include line breaks in the string, 92 00:07:24,730 --> 00:07:26,140 which will make it easier to read. 93 00:07:30,818 --> 00:07:31,883 To save us some time, 94 00:07:31,883 --> 00:07:35,220 I've already written in Notepad the SQL statements that we need. 95 00:07:44,736 --> 00:07:48,155 If you're following along, see the teacher's notes for this code. 96 00:07:48,155 --> 00:07:54,070 This SQL statement inserts a row into the ComicBookAverageRating table for 97 00:07:54,070 --> 00:07:56,050 each row in the ComicBook table. 98 00:07:56,050 --> 00:07:58,470 That has an average rating column value. 99 00:08:03,611 --> 00:08:08,604 In the Down method, we need to populate the ComicBookAverageRating column values 100 00:08:08,604 --> 00:08:12,060 after the column is added to the ComicBook table. 101 00:08:12,060 --> 00:08:16,280 But before the ComicBookAverageRating table is dropped, 102 00:08:16,280 --> 00:08:20,190 writing a SQL statement right after the AddColumn method will do the trick. 103 00:08:26,696 --> 00:08:29,264 / / Populate 104 00:08:29,264 --> 00:08:36,758 the ComicBook.AverageRating column. 105 00:08:41,490 --> 00:08:46,010 Just like with the Up method, I've already written the SQL statement in Notepad. 106 00:08:57,500 --> 00:09:02,771 Writing the SQL statement to migrate our data from the ComicBookAverageRating 107 00:09:02,771 --> 00:09:06,910 table to the ComicBook table is more complicated. 108 00:09:06,910 --> 00:09:11,740 We'll use an update statement to set the average rating column values, but 109 00:09:11,740 --> 00:09:16,040 we need a way to retrieve the latest average rating value for each comic book. 110 00:09:17,130 --> 00:09:21,851 Cross apply allows us to evaluate a table valued expression for 111 00:09:21,851 --> 00:09:24,354 each row in the ComicBook table. 112 00:09:24,354 --> 00:09:29,497 In our case, that table valued expression is a subquery that returns 113 00:09:29,497 --> 00:09:35,640 the top one average rating column value from the ComicBookAverageRating table. 114 00:09:35,640 --> 00:09:39,597 For each ComicBookId column value in the ComicBook table. 115 00:09:41,030 --> 00:09:45,410 Notice that we're sorting the results of the subquery by the ComicBookAverageRating 116 00:09:45,410 --> 00:09:48,070 date column in descending order. 117 00:09:48,070 --> 00:09:52,140 This ensures that we'll get the latest average rating column value for 118 00:09:52,140 --> 00:09:54,834 each comic book. 119 00:09:58,699 --> 00:10:01,338 Now, let's update our database. 120 00:10:01,338 --> 00:10:04,954 update-database. 121 00:10:13,328 --> 00:10:17,615 Let's review the data in the ComicBook and ComicBookAverageRating tables. 122 00:10:23,466 --> 00:10:24,815 In the ComicBook table, 123 00:10:24,815 --> 00:10:29,455 here's the rows for our three comic books without the average rating column values. 124 00:10:35,597 --> 00:10:40,480 In the ComicBookAverageRating table, we have three rows. 125 00:10:40,480 --> 00:10:43,120 One row for each of our comic books. 126 00:10:43,120 --> 00:10:47,650 This verifies that our upgrade migration worked as expected. 127 00:10:47,650 --> 00:10:51,664 Let's imagine that over time one of our comic books receives some additional 128 00:10:51,664 --> 00:10:52,969 average rating values. 129 00:10:52,969 --> 00:10:56,730 I'll just manually enter two additional rows for our first comic book. 130 00:10:57,850 --> 00:11:02,745 ComicBookId 1 with a rating of 131 00:11:02,745 --> 00:11:08,023 7.2 on 1/14/2017. 132 00:11:08,023 --> 00:11:12,378 And ComicBookId with a rating of 133 00:11:12,378 --> 00:11:17,700 7.3 on 1/15/2017. 134 00:11:17,700 --> 00:11:23,510 So, now, the comic book with an ID value of one has three average rating values. 135 00:11:24,790 --> 00:11:27,570 Let's downgrade the database to the previous migration. 136 00:11:38,417 --> 00:11:46,018 Update-database -targetmigration 137 00:11:46,018 --> 00:11:51,548 AddBioPropertyToArtist. 138 00:11:55,527 --> 00:11:59,250 Refresh the table list and view the ComicBook table data. 139 00:12:04,578 --> 00:12:06,678 The average rating column is back, 140 00:12:06,678 --> 00:12:11,310 and it's populated with the latest average rating value for each comic book. 141 00:12:12,696 --> 00:12:17,430 And the ComicBookAverageRating table has been dropped from the database. 142 00:12:19,960 --> 00:12:23,570 It's important to ensure that your migrations don't destroy 143 00:12:23,570 --> 00:12:25,060 any production data. 144 00:12:25,060 --> 00:12:29,690 Your users will appreciate your extra effort and attention to detail. 145 00:12:29,690 --> 00:12:32,250 Whenever you add a migration to your project, 146 00:12:32,250 --> 00:12:35,980 it's also important to properly test the effects of that migration 147 00:12:35,980 --> 00:12:39,070 before you apply it to your production environment. 148 00:12:39,070 --> 00:12:43,730 In the next video, we'll take a look at an example development, testing, and 149 00:12:43,730 --> 00:12:47,110 deployment workflow that utilizes multiple environments.