> the idea that everyone is forced to upgrade every single library every 2 years is beyond unreasonable

That's overstating the case. A library that targets dotnet 6.0 will often continue to restore and run in a dotnet 8.0 application. The notable exceptions are framework-aligned packages like Microsoft.Extensions. Most of the time you just need to bump the target framework in the props file, check it builds, run unit tests, and upgrade a few library packages that might need it.