In one of the previous posts, we have seen the an approach which can be used to troubleshoot performance issues. In this article, let’s try to understand how a query is evaluated by EF core. We will also see how a badly written query can affect performance.

Client vs Server

The terms – client and server – generally used in various context and the exact meaning of the term depends on the context being discussed. For example, in case of web applications, these two terms mean a caller (or web browser) and the server which serves the HTTP requests.

But for the sake of this article, client means the C# code written in data access layer of some application. And server means the database server with which EF core interacts.

How query is evaluated ?

EF Core tries execute most of the parts of query on the server side. Before handing over the query to server, EF Core checks if there are any parameters which can be executed on client side.

Let’s have a look at lifecycle of a query. Query goes through 3 steps in its lifecycle.

Conversion to database provider language

LINQ query is first translated to an intermediate form which can be understood by the database provider being used. The result of this conversion is cached so this translation logic can be skipped and cached translation can be directly handed over to the database provider.

Database Provider fetches data from database

Database provider receives the translated query. Then it performs below steps:

• Database provider then identifies the parts which can be evaluated by the underlying database.
• The identified parts are then converted to database specific language (e.g. SQL for relational databases).
• The query which is now in database specific language – is sent to the database.
• The database then returns the resultset. This result set is just collection of values from database, it is not yet converted to EF core entities.

Entity Instances are created and optionally tracked

EF core receives the result set. Next steps depends upon the query behavior.

If the query behavior was set to No Tracking, then EF core creates instances of entities from result set and returns them.

If the query behavior was set to Tracking, then EF core checks if the data in resultset is being represented by an existing instance of the entity in change tracker in the context. If it is found, then EF core returns existing entity instances are returned. If such entity is not found in the change tracker, then EF core creates new instances of entities and adds them to change tracker. These new instances are then returned by EF core.

When Query is Evaluated ?

The LINQ query is not handed over to database provider immediately. When we add new LINQ operators to the query, it just builds an in-memory representation of the query. The query is evaluated only when the result is required for next steps / operators.

For example, in the query given below, ToListAsync is the call which triggers evaluation of query.

Similarly, other methods like FirstAsync, FirstOrDefaultAsync, SingleOrDefaultAsync, Count are some other methods which trigger the evaluation of query.

Client Side Evaluation and Performance

Why the information given above is important ? Because if this part is neglected, the queries may cause poor application performance. Let’s take few examples.

Sometimes a top level projection may contain a logic to process the data coming from database. For example, let’s say a custom formatting is required to be applied to a specific column, like appending something to column value. Let’s say this logic is written in a method and if this method is called from projection. As SQL does not know about this custom logic, this query would bring all data to EF core and then the translation would happen.

Sometimes the client side execution may be beneficial as it allows to apply a custom logic. Hence, sometimes, you may want to explicitly convey EF core to execute all operators on client side. It can be easily done by triggering the query execution by calling ToList, Single, FirstOrDefault methods before the LINQ operators.

Note, that it may pull a lot of data in memory. The delays due to serialization and deserialization of huge data might introduce delays in query processing.

Hence explicit client side evaluation should be used only if we know that the result set returned by query is sufficiently small. In fact explicit client side evaluation is recommended only if there is NO corresponding server side translation.

Wrapping Up

So, while working on EF core, it is absolutely necessary that we keep eye on the way queries are being written. Application should always be monitored for ensuring that queries sent to database are optimized so that application can perform efficiently.

I hope you find this information useful. Let me know your thoughts.