Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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.
Follow Along
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
Code
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
Additional Learning
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up