How to loop through records in an ASP.net Core Entity Framework Migration?

Question

How to loop through records in an ASP.net Core Entity Framework Migration that uses the Code First approach?

That's my question but I'll give more details in case they help.

I'm using ASP.net Core 3.1 for a web application.
The database connection is through Entity Framework with the Code First approach.

Running a select and iterating over results is trivially simple in many places but I'm surprised how challenging it is to do this in an EF core migration. Looping like this is easy with Laravel's Eloquent ORM tool.

I can run simple update statements using modelBuilder.Sql but some updates aren't so simple to express.

I have some hierarchical data and it is very complicated to write an SQL update to do what I need. The hierarchy can be 10 to 20 levels deep which would be a crazy mess with joins.

One person suggested I add a stored procedure to the database but this sounds far worse than implementing this through c#. A stored procedure adds the SQL Server's stored procedure language to the skill set required to fully understand the code base. That also is a step away from the "Code First" approach since c# code and model classes ideally define all of the SQL database schema. A stored procedure also couples the project to Microsoft SQL Server when everything else could work with MySQL.

The closest article I could find is at:
https://stackoverflow.com/questions/38568475/how-to-do-data-migrations-inside-an-ef-core-code-first-migration
That is ASP.net Core 1.0 which is about 4 years older than 3.1, though. It also says that it wasn't possible back then.

Answer 1

Josh Greig Based on what I now think you're asking, I'll give you a few options to consider.

First, let me start by clearly stating that there are no built-in mechanisms within the EF Core Migrations API that involve querying data during a migration using the EF Models or any other method provided by the Entity Framework that I'm aware of. My earlier 3 part response covers this in detail.

With that out of the way, let's explore potential options that can be brought into EF Core Migrations.

1. SqlKata is a fluent SQL query builder for C#. This seems to be very similar to Laravel's fluent Query Builder interface.

See code samples and documentation links below:
- https://sqlkata.com/
- https://github.com/sqlkata/querybuilder
- https://www.nuget.org/packages/SqlKata/
- https://medium.com/@ahmadmoussawi/announcing-sqlkata-an-elegant-sql-query-builder-for-c-3d13a9ff7a6f

This link is for a comparison reference:
- https://laravel.com/docs/8.x/queries

NOTE: I've not worked with SqlKata simply because stored procs or raw SQL would be the preferred option for me and my development teams. But, this does look like a great solution for a strictly C# approach.

2. System.Data.SqlClient.SqlDataReader
While I believe the first option covers what you're asking, I figured it might be good to list a native option similar to PHP Data Objects (PDO).

You can review sample code for SqlDataReader below:
- https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader

Likewise, here are some PDO links for you (and others) to review for comparison:
- https://blog.pusher.com/php-laravel-migration/#:~:text=Database%20querying

- Go to the section titled "Database querying" for examples using pdo.

- https://www.php.net/manual/en/book.pdo.php

----
I'd recommend moving your querying logic to separate method(s) within your migration class and call those methods from your Up() or Down() methods respectively.

Hopefully, I did a better job with this response. 😉👌

Answer 2

Josh Greig I've got a response for you. But I might not be able to post until tomorrow. I just wanted to let you know in case no one else replies by then.

Answer 3

[Part 2 of 3]
------
Digging Deeper into Why EF Models aren't used in DB Migrations:
---
You might be wondering why using EF models with DB Migrations wasn't listed above as an option...

Let's begin by understanding the problem with your use case scenario and how that fits into the context of DB migrations.

I'm assuming you're experiencing runtime errors notifying you that the entity DbContext is out of sync with the database schema - or something to that effect.

Essentially, you are looking for a way to use custom EF models in the data migration process similar to how you use them in your application code. While this may seem like a reasonable expectation at first glance, it makes less sense logically when reviewing how things fit in the Code First development workflow.

All 3 development workflow steps below are required prior to runtime use of the EF models within the application:

- Step 1. Create / update the code for the Entity / Model classes.

- Step 2. Generate the data migration code and modify if needed.

- Step 3. Use the data migration code to sync the database schema and data to match the code.

Attempting to use EF models in Step 3 will result in an exception because the DbContext will still think the EF model code and database are still out of sync since the data migration is still in progress.

Hence... this is the reason using EF models during DB migrations isn't an option.

----
But then... you might be wondering what if we could get past this compatibility check... could we then use the EF models during data migration?

I'll cover that in my next response...

Answer 4

[Part 3 of 3]
For kicks and giggles, let's imagine you were able to bypass this restriction and managed to use your EF models to load and modify your data for a given data migration version.

Hooray! We feel triumphant in our mad hacking skills. 😉

Even though we've managed to suppress the compatibility check... that will only go so far if the database schema doesn't match the EF models. So, it would be critical for all database schema updates to be applied before attempting to use the EF models in the DB migration.

Let's assume we're disciplined enough to manually ensure the database matches the code before attempting to use EF models... WOOT! Now we can celebrate the successful use of our EF models during the data migration process.

That is... until hours, days, or months later when we end up changing something in the EF code and run into compilation errors from the previous data migration code. DOH... it appears the previous version of the EF code is tightly coupled to the previous data migration code. The only way to resolve this compilation error is to either avoid making code breaking changes in the EF models or remove the tight coupling introduced in the previous data migration to begin with. Derp... this brings us back to where we started... having to review the options in my earlier response. 😉

For the above listed reasons, it's essential that the migrations API is completely decoupled from the code used for the EF models.

The challenges presented here actually apply to any migration / scaffolding framework. While there might be ways to use ORM entities in other DB migrations, I'd be surprised if that practice wasn't considered to be a hack that should be avoided.

------
Anyway... apologies for the delayed response. I really seemed to struggle finding a way to organize my talking points and provide a rationale around the various issues.

Unfortunately, it all blew up resulting in this long winded, jumbled mess of a dissertation. 🤯🤣

Hopefully, this still satisfies your question.

Answer 5

Josh Greig I'm obviously missing some context that you may think should be clearer to me. 😉 It might be due to the context I'm reading from your references to Eloquent, which has similarities to EF Core.

For clarification, are you asking for how to query data during a migration without using the EF models?

If so, I completely misunderstood the ask.

The disconnect for me might come from the comments below which give me the impression you're using Eloquent ORM entities in your migrations:

"Looping like this is easy with Laravel's Eloquent ORM tool."

and

"Querying the database and getting a useful result is so easy in Laravel's Eloquent and other migration tools..."

To get me on the same page as you...

Are you using Eloquent ORM entities in your Laravel migrations or are you using the Query\Builder to query the data and do looping during your migrations?

This clarity will be helpful before attempting to post an answer that misses the mark as badly as you've indicated about my first attempt.

Answer 6

[Part 1 of 3]
My personal uses of Code First with EF(*) or any scaffolding frameworks have been limited to short-lived prototypes which don't typically involve complex migrations. Nonetheless, I'm quite familiar with DB Migrations across various platforms and frameworks and may still be able to provide insight.

TL;DR; - Code First DB migrations are simply very limited in nature. I wouldn't trust using them on anything critical like production databases and would typically use custom DB migrations involving a mix of custom built SQL scripts and custom C# code.

If trying to stay within the EF Core framework, then check out the options I've listed below based on EF Core Custom Migration Options presented in this link:

https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations

*NOTE: If it was me... I would probably go for Option 1b.

------
Option 1a: MigrationBuilder.Sql()
---
- This is a more tightly coupled approach to each DB expected to be used.
- However, you can create separate SQL variants for each known DB provider required to be supported.
- That said, most development shops will likely be committed to a single DB vendor anyway and not need to support multiple variants.

------
*Option 1b: MigrationBuilder.Sql(sp_Migration_vYYYYMMDD) with Stored Procedures
---
- Building on the above approach from the Microsoft link above, I would move the heavy lifting of data manipulation to stored procedures and write a version for each DB.

- This will require a new prerequisite step in your workflow to write and deploy those stored procs for each supported DB. But... it's worth it in the long run.

------
Option 2: MigrationOperation Classes
---
- This approach uses a layer of abstraction to decouple the migration code from the DB.
- I've not personally experimented with it and am not sure if it will cover your use case.

------
Option 3: LINQ-to-SQL
---
- LINQ-to-SQL may offer more flexibility than the MigrationOperation Classes while still using only C#.

Answer 7

To get more eyes on this, are there any others in the community who can assist with this question?

Answer 8

Thanks for trying to answer.

From everything you said, it still sounds like something like looping through the result of an SQL select on the associated database isn't possible with an ASP.net Core Entity Framework migration using code first approach.

If I'm wrong, can you share an example of a migration that performs an SQL select and prints the data to a log or to standard output? I want a clear example of a loop through result from a database query working instead of lots of alternatives that I'm not really asking for.

I knew several things that were mentioned like not to mix entity models with migrations and knew a stored procedure could tackle the hierarchy update problem. Querying the database and getting a useful result is so easy in Laravel's Eloquent and other migration tools that it feels pretty bad that ASP.net Core's Entity Framework makes it so hard or impossible to do in a migration.

Answer 9

Cool, remember to share tomorrow. I'm still eager for a solution and looked as much as I could without finding a solution.

Answer 10

David, do you still have a solution to share?

I wish you didn't comment like that because other people may feel discouraged from answering my question even though you didn't really answer anything.

Answer 11

Thanks for the effort.

This is the best part of the responses:
"First, let me start by clearly stating that there are no built-in mechanisms within the EF Core Migrations API that involve querying data during a migration using the EF Models or any other method provided by the Entity Framework that I'm aware of."

It sounds like you need to use another framework or tool because ef core migrations don't support that connection. It is just a painful limitation of ef core migrations.