Complex ASP.NET project: ORM choice?

Question

ASP.NET

The question has been raised more than once, but still there is no comprehensive answer anywhere, so please excuse me.
So - WHICH ORM to choose for a complex multi-level project on ASP.NET MVC, in which there are many types of requests - and complex selections with a bunch of joins, complex queries to update data, insert-delete thousands of records, etc.
The fact is that the project is migrating from php to asp.net and, oddly enough, in PHP + MySQL for more than ten years, there were enough bare queries to the database plus a small wrapper for mapping selects, despite the complexity of queries, mainly due to the existence "ON DUPLICATE KEY UPDATE" constructs.
There is no such thing in T_SQL. In my previous work with Windows applications, I used the classic Dataset and that was enough. Now you need to choose some kind of ORM that supports data caching (like Local in EF), fast mapping on bare SQL (like Dapper), bulk upsert (EF.Utilities / SQLBulkCopy).
I have not found any product that combines full functionality for all tasks.
EF is universal, but loses a lot in performance in simple selects where fast mapping is needed, in upsert (which is stupidly absent, there is only a separate insert / update or AddOrUpdate that generates TWO queries for each Entity), in the complexity of building linq for complex large queries ( for comparison, the formation of raw-sql for a bunch of conditions took about 70 lines, taking into account the formatting, and the query converted to Linqer took a hundred lines and this is without half the conditions).
Dapper allows you to easily select data using the most complex raw queries, but does not know how to work with upsert.
EF.Utilities/SqlBulkCopy are not built into EF organically, that is, to use them, you need to write code separately, moving away from the standard EF functionality.
What ORMs exist to solve all of the above taskscomplex ? Maybe there are some hybrid bundles (like EF + Dapper, which is often mentioned), custom solutions in NuGet?
I would like to see a DbContext that implements all the functionality of EF plus Dapper is also implemented there to work with raw queries with the ability to embed the received entities into DbContext DbSets automatically, taking into account tracking, plus the bulk upsert functionality is implemented WITHOUT additional dances with a tambourine (call SaveChanges and then the ORM itself decides which data to insert based on the specified keys, which to update, whether it is necessary to return identifiers for tracking records, etc.).
Maybe it's worth getting away from MSSQL and working in conjunction with MySQL, given the presence of the "ON DUPLICATE KEY UPDATE" construct?

Answer 1

I advise you to think not only about the ORM, but also about the architecture of your application.
ORM allows you to quickly implement the layer responsible for transforming database models into business model objects. In fact, it implements a bunch of classic patterns like Data Mapper, Identity Map, Lazy Load, Repository, Unit of Work, and a bunch more. All this you get out of the box + such nice things for database version control as CodeFirst + migrations. Yes, in some cases you pay the price of read / write speed.
This is where the correct architecture of your application should help you. You can design according to Domain-Driven-Design principles. In this case (as opposed to a monolithic solution), the application is divided into parts. Each part can have its own infrastructure that can write / read data from the database (repository pattern) and use some kind of separate framework. Parts that don't need fast speed can easily use the handy EF. If in some part you see problems with performance (bottleneck), you can change the repository - throw EF out of it and use Dapper. With the right architecture, such a replacement will not affect other parts of the system and your refactoring will be painless.
In addition, there are extensions for EF that implement Bulk functions:
https://github.com/loresoft/EntityFramework.Extended
entityframework-extensions.net
https://efbulkinsert.codeplex.com/

Answer 2

I would first make a migration as such, and then I would select ORMs for those other needs. The only thing is that it is desirable to architecturally remove work with data into a certain data access layer (so that later, having covered it with tests, you can edit only it without touching the code much higher levels). In this case, it is better to do migration on bare ADO.NET, since it allows you to do everything that you need with all the listed methods with the database.
When you move from PHP, you can already work on ORMs. For CRUD operations, EF is fine. For bulk updates/inserts, it's probably easier to leave ADO.NET (I would still make such operations asynchronous for processing in the background with subsequent notification of the end - via a poll or via push notifications - say WebRTC). For selections with complex queries - remove these complex queries into stored procedures and pull them either through EF or through ADO.NET. For data mapping to objects used above the data access level, use AutoMapper . With it, you can map anything to anything, the main thing is not to be lazy and figure out the possibilities of configuring it.
There simply cannot be a single ORM that will well cover all the listed types of work with data and at the same time be optimal for everyone. All ORMs are based on one or another concept - how to work with data. Universal systems are when all parts work in the same way (and usually equally badly :)).

Answer 3

I can suggest taking a look at Flexberry ORM . It was designed just for the development of complex systems with a long period of maintenance and improvements. For a complete creation cycle, there is a Framework for ASP.NET (but, unfortunately, only WebForms) or a variant of the OData + EmberJS bundle .