Right now EF Core is probably the best ORM that has ever existed. What exactly is missing?
Although for performance you would probably reach for something like Dapper but that is not an ORM.
* Support for "FOR UPDATE" and "SKIP LOCKED"; it's easy enough to tag the queries and modify the SQL in an interceptor though.. Yuck.
* Transaction attributes ala Spring; Though I can make do without them it's nice being able to specify what sorta transaction party a method is interested in.
* Better support for "bulk updates". SQL Alchemy, as rough an onboarding experience as it is, has really good support for executing db-side update logic.
That said, I largely agree with you and nobody should overlook LinqPad. Anyone interested in babysitting the generated SQL should be using it; too bad it's not on Linux :| :( ;(
The optimizations they cover in the docs should all be done by default IMHO; optimizing models, polling db contexts, and etc. I also open and close a db connection at app start which further reduced the first request latency after putting a process in rotation.
Wish I could agree but they would have to fix the very slow time to first query when using big models (+500 tables in our case). Compiled models is not a solution for us since our model changes a lot and the compilation is just as slow. It's disappointing because it used to work fine under the ancient Linq2SQL library.
Also maybe you might find a benefit from splitting your context into multiples. I am considering this option for one of my code bases
I can't split the model without massive refactorings, and even then, some tables are common across all modules and would need to be duplicated. Your advice is unfortunately the standard answer in my case, I guess EF Core is just not for me, really disappointing but "c'est la vie".
edit: ha you probably mean commit it in source control so other devs can also use it? I guess it's a compromise, would still slow down our DbContext refresh command a lot.
Are you relying on model conventions or spelling out everything in modelBuilder calls?
We're not used to something like EF, perhaps it would work for us. But debugging generated queries due to performance issues is something we'd like to avoid. For now the decision was made to not use EF.
To be honest you should keep all ORM queries fairly simple if you can. Where clauses fine. Inserts, updates, deletes, ORMs save so much code, and so much pain when you add new properties/remove them.
But if a query is more than a few includes or joins you should be handcrafting it with FromSQL() or loading it piecemeal using Load().
And don't even think about using it to make complicated reports, that is not a good idea. Make a stored procedure or view.
And that's especially true if you are using anything other than SQL Server. I've seen abysmal performance myself on MySQL/Maria on moderately complex EF queries. I've not really looked since EF 6, but it used to love making nested selects instead of JOINs, which were fine in SQL Server but terrible performance-wise in MySQL. Postgre I've never used with EF in anger so can't comment.
You can use EF with Hot chocolate to make a GraphQL endpoint really easily, but I'd imagine that's an easy way to saddle yourself with serious performance problems unless you limit the levels it can go. I'd be interested to hear if anyone's using it and how they find it?
Also the lazy-loading and the in-memory provider for tests are both kind of misfeatures.
If you don't want to debug difficult queries, then extend it to use Dapper and use the best of both worlds.
There is one gigantic footgun in EF Core, that is the decision between single query and split query. If you choose single query in the wrong situation you can end up with truly pathological queries. I might blame EF Core here a bit for a dangerous default, but to be honest the other choice would be dangerous in a different way, so there is no obvious good default choice here. This is a part that you need to understand to use this ORM, and fortunately it generates warnings now and kind of forces you to choose the strategy.
The one other aspect that helps to generate good queries with EF Core is to use "Select()" for any case where you want to request fewer columns than available in your tables. I find it quite natural to write queries this way in any case.
The Interpolated family of methods take nice, clean string interpolation like $"Select * from Table where Id = {Id}" and make sure that is properly parametric SQL queries (ie, avoiding things like SQL injection attacks).
It's a killer feature and I have some idea why it lives in the EF side of the house rather than being generally applied across all of ADO.NET, but it should still probably be a more reusable library of its own beyond just EF.
I usually call .SaveChanges() when i % 20 == 0
Works faster than with .Tracking. The SQL script runs in your transaction, so rollback runs as usual.
The i%20==0 condition with .SaveChanges() is used with .Tracking(), yes.