Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the solution to the poor query performance caused by multiple Include of EntityFramework Core 3?

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

Shulou(Shulou.com)06/01 Report--

This article will explain in detail what is the solution of low query performance caused by EntityFramework Core 3 Include for many times. The content of the article is of high quality, so the editor shares it for you to do a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

In this section, let's take a look at the performance problems caused by multiple Include in the title EF Core. Cut the crap and get to the point. First of all, we still give our sample class from the previous section:

Next, we make the following query on the console:

As shown in the figure above, there is nothing wrong with the generated SQL statement, right? next, let's query the navigation property Posts, as follows:

Hey, shouldn't it be INNER JOIN, but we can see that the resulting SQL statement is actually LEFT JOIN. The key is that we don't set the BlogId in the Post class to nullable, right? isn't that interesting? At the same time, the primary keys of both tables are sorted through ORDER BY. This is the starting point of the problem, so let's introduce two more classes:

We declared the categories and tags above, and we know that blogs have categories and tags, so there are navigation attributes for categories and tags in the blog class (here we don't care whether the relationship is one-to-one or one-to-many), and then modify the blog class, as follows:

Next, let's make the following query:

At this point, it has nothing to do with change tracking. Let's take a look at the resulting SQL statement and see if it is surprised. Assuming that there are multiple navigation attributes in a single class, the resulting SQL statement will continue to LEFT JOIN and ORDER BY, but imagine how poor its performance will be. So how should we solve such a problem? Since it has something to do with Include, each additional navigation attribute, that is, an additional Include will add a LEFT JOIN and ORDER BY, so why don't we query separately and get started.

At this time, it is obviously not advisable for us to do the above query, because we need to return IQueryable directly to the database for SQL query. At the same time, only one query can be returned according to the primary key query, so we transform it into the following query:

Since the navigation attributes need to be loaded from the context, we need to remove the AsNoTracking and specify the entity navigation attributes through the context loading, which can be loaded by the Load method, as follows:

Through the SQL statement generated above, we know that this is the result we want. The above code doesn't look so good, and there doesn't seem to be a more beautiful way to write it. Of course, I'm just demonstrating it in the console here. Changing the above to an asynchronous query is the best way to handle it. Performance is much better than generating a bunch of LEFT JOIN and ORDER BY.

Note: the above bloggers use stable version 3.0.1, while other versions have not been tested. In fact, for queries, it is recommended to use Dapper or the underlying connection to write native SQL is the best, for a single table, there is nothing wrong with using EF Core, for complex queries or not to use EF Core, the generated SQL is very uncontrollable, for convenience, the result will be CPU soaring up.

About EntityFramework Core 3 many times Include causes the query performance low solution is what is shared here, hoped that the above content can have certain help to everybody, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 269

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report