I've used many migrations tools at this point (multiple Django libraries, EF OG, EF Modern, Flyway now) and that sort of degradation as migrations get into three and four digits is a common failure pathway. As with all code, you get bugs especially in the things that you don't actively test for. If you don't have a good reason to test runs of building databases through
all migrations then you will find lots of little things that make that tough. If developers
have to run migrations from start to finish to have a local testing database, they are more likely to make sure that bugs don't creep in. (They are also more likely to worry about the overall performance of it as they get to hundreds of migrations; tools like EF Migrations have ways to set new initial migration points.) If the only reason developers need to run migrations from start to finish is standing up a new environment once a decade they are going to miss issues.
A lot of the same goes for testing down migrations, but even more overlooked how disincentivized they sometimes get. If a developer never has a reason to use down migrations, they never get tested, and likely are full of bugs. I know too many developers that don't believe in down migrations for that very reason that they "never" see a use for them and so will never use them and will never bother testing them. I've also worked on applications where rollback plans were critical and rollback testing something of a norm even in developer local environments and down migrations, especially loss-less ones (though that is true hard mode for SQL), were a critical part of CI/CD and the frequent testing made for good down migrations. (If for some reason you need to test a new bug in an old version application as it was, and you can clone a current production database and trust that you can run down migrations to get it to the state that old version expects, there's some useful debugging things you can do, sometimes.) I sometimes don't think down migrations are worth the effort either even having seen them used at all, much less kind of well.
Direct advice that I can offer specific to EF Migrations as it sounds that you do still have those hundreds of migrations: if you haven't already, switch from scripts to EXEs. The "Idempotent" scripts especially are nice when a project is just starting out and you've got a few or a few dozen at most migrations, and they are "normal" T-SQL files so there's a lot of DBA comfort there, but it is in practice just about impossible to keep them working after more than a couple dozen migrations or so due to T-SQL parsing bugs and I've seen too many migrations break the script files entirely for silly parsing reasons. Today's EF Migrations offer a way to build a self-contained EXE for any platform .NET supports (so you can build a linux-x64 if you need it for your cheapest build agents in your CD pipeline) to run through migrations. The EXEs are faster and more reliable, use good transactions, only run the migrations that are needed, and can handle a lot of migrations including start-to-finish runs that you might currently see failing as scripts (because T-SQL parsing has weird quirks and they get weirder the larger the file is).
I was a fan of SSDT for a number of years, it's always been a good option. At this point SSDT aren't often my first option given the Visual Studio requirement and SQL Server specific nature of it. (EF Migrations can be used now for other databases, such as Postgres. Also, not that I don't like Visual Studio, but there are benefits to more cross-platform options like VS Code.) (For some of those years I wished I had DBAs I was working with that understood SSDT better because of silly reasons that SSMS worked just fine with dacpac/bacpac files as deployment artifacts but DBAs I encountered seemed to have a hard time learning those tools in SSMS and refused to try SSDT.) I know Azure Data Studio had been slowly increasing its support for SSDT DB projects and dacpac/bacpac when I last was doing SQL Server-only work, so maybe there's a future for SSDT's descendants outside of Visual Studio itself as that gets better support. (Though I still don't expect to see those teams at Microsoft support DB projects for Postgres and SQLite like EF Migrations can.)