Introduction Link to heading
There’s a description of database migrations in general here, but the devil is in the details; sometimes you need direction on how to go about actually doing the thing. So that’s what this is.
By necessity, this is going to be technology-specific. Because I’m working primarily in C# right now, I’m going to cover that ecosystem exclusively. The basic ideas are fairly portable assuming you’re using a decent migrations tool, though.
About ORMS Link to heading
Object-Relational Mappers (ORMs) exist to map data from one schema (tables, used by relational databases) to another (objects, used by OOP programming languages). They’re a boon in the startup phase of a project, because they minimize the number of different paradigms a developer has to juggle. Indeed, between Blazor and Entity Framework Core it’s possible for a developer to build a full-stack application without using any language other than C#.
But Will It Scale? Link to heading
The problem is that EF Core is an abstraction layer for multiple possible SQL server backends. Like all abstraction layers it introduces latency as it has to translate your LINQ query into T-SQL, and that translation often results in extremely inefficient T-SQL queries. There’s also the fact that if your data model changes frequently, so will the auto-generated SQL; this prevents Azure SQL’s Query Optimizer from consistently tuning your queries across code deployments. And don’t even get me started on indexing.
EF Core has made some great strides in recent versions towards better performance, from precompiled queries to better auto-generation to additions to LINQ. All of those come at the cost of making the abstraction increasingly leaky. When you’re carefully hand-tuning your LINQ to avoid auto-generated N+1 queries in the resulting T-SQL…at that point, just write the T-SQL code directly. The abstraction layer is no longer helping you; in fact, it’s making things worse by introducing a performance hit for no benefit in ease of programming.
This is why most performance-critical C# applications eventually drop EF Core in favour of something like Dapper or just raw SQL.
Note that this isn’t an either-or decision; using EF Core for infrequent writes and Dapper for high-performance reads is common
What’s My Point? Link to heading
My point is that if you’re not careful, EF Core can end up being a crutch. Your SQL database and your migration method can become magical black boxes that Just Do Things. EF Core becomes a Golden Hammer that prevents you from executing more effectively.
That’s my segue into the next bit.
Learn Your Tools Link to heading
If your stack is based on EF Core, EF Core-exported SQL, grate to apply the migrations, and Azure SQL as the backend
database, you need to know how all of those things work. That means reading the docs and understanding what each of
those tools do, how they’re intended to do it, and what their full feature set is. Nothing causes more accidental
complexity than trying to chain a bunch of disparate tools together to do something that’s an out-of-the-box feature in
the correct tool.
