Related Topic: Entity framework performance tuning
Does Entity Framework Core always create query with best performance? Does any body have a Counterexample (EF query has less performance vs raw SQL query (EF generate a SQL query that we can write it in better way using raw SQL))?
Thanks
CodePudding user response：
There are many performance tips for Efficient Querying like Tracking, no-tracking and identity resolution, split queries , indexes ,
EF is ORM (object relational mapping) that finally translated to end Db Engine
To prevent the programmer from getting involved in the database language
has its own overhead cost
you can also use raw SQL queries with
FromSqlRaw
you can learn more
https://docs.microsoft.com/en-us/ef/core/performance/
CodePudding user response：
Short answer is No,
Explanation
EFCore (short for Entity Framework Core) does not aim to generate query with better performance, EFCore only aims to generate query based on the model you have provided. EFCore will create correct query based on the result you are trying to achieve.
Queries are only generated based on the navigation properties and foreign keys involved.
Databases mostly use index statistics to generate best query plans, that has nothing to do with if query is written using EFCore or plain SQL.
Irrespective of the way you write queries using EFCore, SQL server will choose most efficient plan always.
Split Queries
EFCore provides query splitting, which results in smaller datasets compared to many number of joins in single query generated by older Entity Framework.
You can read about it here: https://docs.microsoft.com/en-us/ef/core/querying/single-split-queries, it definitely is faster compared to earlier Entity Framework.
We have seen query times dropping to 20ms from 2 seconds, using split queries. There are very small inconsistencies as both occur in different time, and are actually executed as many separate queries. But for most part, it is sufficient and fast. And best part is you can use .AsSingleQuery() to use single query.