EF Core Migrations Deep Dive, Applying Migration, SQL Scripts

Get the source code for this video: / milanjovanovic
Accelerate your Clean Architecture skills: bit.ly/3PupkOJ
Master the Modular Monolith Architecture: bit.ly/3SXlzSt
Managing database schemas as your applications grow can quickly become a headache. Manual changes are error-prone and time-consuming, which can easily lead to inconsistencies between development and production environments. I've seen these issues firsthand on countless projects, and they're not pretty. How can we do better?
Enter Entity Framework (EF) Migrations, a powerful tool that lets you version your database schemas.
Imagine this: Instead of writing SQL scripts, you define your changes in code. Need to add a column? Rename a table? No problem - EF Migrations has you covered. It tracks every modification to the data model. You can review, test, and apply changes confidently, even across different environments.
EF Core Migrations: A Detailed Guide
www.milanjovanovic.tech/blog/...
Migration tooling:
- FluentMigrator: fluentmigrator.github.io/
- DbUp: dbup.readthedocs.io/en/latest/
- Grate: erikbra.github.io/grate/
- Flyway: flywaydb.org/
Join my weekly .NET newsletter:
www.milanjovanovic.tech
Read my Blog here:
www.milanjovanovic.tech/blog
Chapters
0:00 What are EF Core migrations?
2:13 Configuring an EF Entity
5:18 Creating a database migration
6:27 Creating another database migration
7:55 Applying EF Core database migrations
10:01 Generating SQL Scripts for EF migrations
12:11 Writing custom SQL in EF migrations
13:00 Additional database versioning tools
14:15 EF Core migrations best practices

[@MilanJovanovicTech]

Master the Clean Architecture with PCA: bit.ly/3PupkOJ

[@user-fd3yr5eo4g]

I learned so much concepts from you. Following you from some months, sometimes I go to your channel and watch complete play lists. Thank you for doing so much for the community.

[@MilanJovanovicTech]

Thank you very much, I appreciate that! :) And I'm glad you find the content helpful, that's all I care about.

[@s1kky]

I swear it feels like u have access to my company's repos, it's like you read my mind with these videos😂

[@MilanJovanovicTech]

Maybe there's something to it 😁

[@lashuti31]

Bro for real. Whenever im interested in some topic, Milan drops a video for it 😄😄

[@chamikagoonetilaka4026]

I use EFCore migrations with DbUp. One of the key advantages of this approach is you have the full control over the SQL scripts that are getting executed on the database, while enjoying the EFCore's ability to automatically generate SQL for you. Sometimes, you will need to add custom SQL scripts in-between the generated scripts to populate seed data and to do data migrations.

[@MilanJovanovicTech]

This is an excellent approach 👌

[@cas818028]

I remember just cracking open red gate back in the day. Asking it to do a db diff and generate the script for me. Ran that in production without worry, hassle, or headache

[@MilanJovanovicTech]

Those were the days

[@WantMore-mc8dx]

My approach is to handle all DB stuff within SQL and not use migrations at all. Generated SQL-changes might be ok if it's smaller DB and you don't have to consider optimizations.

[@akeemaweda1716]

Exactly my approach too. And this has made me not to even worry about destructive commands.

[@pazzuto]

I do this as well. In fact, I have a SQL project in the solution where the design happens and get a DACPAC that I can validate with the DBA. It's also deployed to CI/CD for all or none approach. I like this better than magic migrations.

[@MilanJovanovicTech]

That's perfectly fine 👌 I've done it on some projects. But my SQL DDL skills aren't that sharp.

[@clebersondot-net]

I love working with Migrations! Thank you for more videos like this, Milan!

[@MilanJovanovicTech]

Most welcome!

[@rafekemmis3097]

Tip #5 - we set the snapshot file to be binary in our git config so that they don’t automatically merge in the case where a pr created a migration without the latest migration snapshot. It’s not a magic bullet but it can at least throw a merge conflict on the pr and indicate that something is wrong.

[@MilanJovanovicTech]

That's interesting!

[@antonmartyniuk]

The approach with generating SQL scripts from migrations is great! In one of my company's products we have a lot of microservices and each microservice upgrades itself executing fluent migrations at a application startup. We're using fluent migrator because our product runs on different SQL databases depending on our clients. And this approach simplifies a lot and is perfectly fine. I can't remember a single time when we had to downgrade the database in production after a bad update.

[@MilanJovanovicTech]

It looks like you also take great care of generating "good" migration scripts, so it's easier to control issues

[@curtmantle7486]

The hardest part is managing multiple PRs with migrations. When one completes the next ideally needs to Pull and rerun migrations and it can be a pain to manage code reviews.

[@MilanJovanovicTech]

It's more about discipline there, but doable

[@ZoneInOn]

Hey Milan, great video. Sometimes my gripe with some of your videos is not showcasing the full extent of how things work, with practical examples (understandably because of time constraints IRL and for video length). This video isn't one of those, you show many ways of doing it, how you do it, concerns to be aware of, actually implement it e.t.c - great video!

[@MilanJovanovicTech]

It's a bit difficult discussing every concept from scratch in every video, so that's a valid critique. With these newer videos, I'm trying better to include context for pros/cons and various approaches you could take

[@jarkal82]

Dziękujemy.

[@MilanJovanovicTech]

Zapraszamy! :) (hopefully translate didn't butchter it)

[@wolecharlesjob8255]

Great. Thanks Milan

[@MilanJovanovicTech]

You bet!

[@10Totti]

Excellent video, a note that few say, it is possible to do all this also from the Visual Studio UI.

[@MilanJovanovicTech]

Not sure what you mean? 🤔

[@10Totti]

@@MilanJovanovicTech You can do the same procedures directly from Visual Studio using a graphical interface

[@matejskerjanc7703]

Nice video as usual. I have a question though, consider someone used ensure created command and didn't use migrations, how would you go around creating continuing with migrations from that point on without losing data, I know a bit out of scope, just something thats waiting for me soon, I see few options but all look bad:)

[@MilanJovanovicTech]

They don't go with one another. If you call EnsureCreated, Migrate will fail. You can generate the SQL scripts and run them.

[@matejskerjanc7703]

@@MilanJovanovicTech so if I wanted new tables added I have to use scripts? Ooorrr continue working with ensure created that could also work, or just tear band aid off and have downtime in prod while proper steps are taken to have migrations setup and then restore backup, these were the options I see sadly

[@GregoryGrewe]

Typically, I use attributes in the Model, instead of the Fluent design, and use the Fluent design for more complicated implementation details. I believe this gives the developer one location to refer to when building out the application logic. Also, I use Apply Configuration and create Entity Builders, to minimize the code in the DbContext. Is there a reason that you did not reference SSMS as a DB Tool? Or how to execute SQL scripts directly from Visual Studio? Maybe you cover those in other videos?

[@10Totti]

Exact!

[@MilanJovanovicTech]

I don't like having attributes on entities, but that's a personal preference. Agree on the IEntityTypeConfiguration.

[@ekekw930]

At 2:42 Description is of type 'string?' but isnt a string always nullable?

[@10Totti]

No if you omit it will become property required.

[@MilanJovanovicTech]

This is nullable reference types in C#. In general, it can still be null if there's no value.

[@KyleStay]

When you have hundreds of migrations, are there best practices for consolidating them?

[@b0ysb3]

better reset it and scaffold from the latest conditions

[@MilanJovanovicTech]

Recreate them, pretty much, where you only end up with one migration

[@PelFox]

I generate a sql script during build and apply it in my CI pipeline. That way I can keep my app user limited to write and read.

[@MilanJovanovicTech]

Nice approach!

[@sinthonseng8981]

I am currently involved in a project that uses an existing SQL Server database, which includes over 300 views and over 500 stored procedures. I am planning to integrate EF Core into this project. Some of these procedures use user-defined tables as parameters and contain over 3000 lines of code. Is this approach feasible, or is there an alternative solution for this scenario?

[@MilanJovanovicTech]

SQL? 😅

[@haroldpepete]

great video, but i prefer use and external class to configure each entity, one file per entiy, imagine when you have more than 6 or 7 tables and all configuration is in the same place, that would be a nightmare, the class must have the decorator EntityTypeConfiguration. and you can check if there is any pending migration and run the service to run migrations, the app will not create a service in vain

[@MilanJovanovicTech]

That's fine, most people will end up doing it like that. The focus was more on the migrations here than EF config.,

[@assylzhanshabkhatov5763]

How to revert some specific applied migration correctly? (Not the latest) Let’s say 10th of 20

[@MilanJovanovicTech]

You can specify -From -To in the update command

[@DuQuels93]

I like to use liquibase instead of the migrations

[@MilanJovanovicTech]

Interesting

[@toyin5522]

Aint indexes by default unique?

[@MilanJovanovicTech]

Nope

[@baranacikgoz]

Using sql query approach to validate if scripts are correct? I am 100% sure that you can make mistakes in sql but an open sourced, Microsoft leading EFCore won't create a false script. There are no value in using this approach unless the company you work ensures high security like a bank and the connection string of the app does not have alter permissions etc.

[@vr77323]

Can you elaborate on this? I don't quite get it. Do you mean that generating a script from your migrations to be able to validate it adds no value and that EFCore will make a better migration than me? Because if that is what you are suggesting then that's not exactly the case. Just because microsoft is behind it and it's open source does not mean it will always get things correct. Simplest example 1: What he showed with older ef versions where a column rename will be executed as a drop-create instead of a rename. That's a very destructive command as if you apply this on a production db, it will delete all data in the column Simple example 2 (This was more on me for not configuring ef correctly at the time, but that is also a very plausable case): I had created some entities with FK between them, but ef couldnt figure out which property points to which one exactly, so what it did instead was create "shadow" FKs called Id1, Id2, etc... this is also not a very good production solution and I managed to catch and solve it by looking through the migration/script generated by EF. Remember, efcore, no matter who stands behind it, works on configurations and assumptions. Whatever it generates is based on your configuration and it's own assumptions. You can never be 100% sure it won't create something bad just because a big tech company is behind it. Everyone makes mistakes

[@MilanJovanovicTech]

EF scripts can sometimes do destructive operation, which you should absolutely double check before running it on your database.

[@johnsuckher3037]

reorganize structure and suddenly it's just dropping operations with no data movement

[@2PlayGaming]

No, thank you, too many type to coding and thinking, I'll use ef core power tools to generate a context in minutes...

[@MilanJovanovicTech]

But that only works with an existing database?

[@2PlayGaming]

@@MilanJovanovicTech exactly. Only if your database is created.

[@10Totti]

@@MilanJovanovicTech Yes.

[@blackpaw29]

@@MilanJovanovicTech True - but when developing now product we tend to do database first and scaffold from the db. Once its out there, then we start using migrations, but still scaffold from the development db

[@flygonfiasco9751]

@@MilanJovanovicTechI think they’re suggesting they prefer to create a database then scaffold the context. I think this is a nice approach for more sql-focused developers

[@mohamedadelaboel-dahab8865]

second

[@MilanJovanovicTech]

Almost made it