Query(LINQ) Evaluation in Entity Framework – Guidelines

QueryLINQ Evaluation in Entity Framework Guidelines

Today in this article we shall understand Query(LINQ) Evaluation in Entity Framework along with best practices around its usage in .NET or .NET Core used with C# language.

We shall cover below types of query evaluation supported in EFCore,

  • Client-Side Query evaluation
  • Server-Side Query evaluation

As we know Entity Framework Core uses queries to query data from the database supporting SQL, MySQL, and other RDBMS.

This query is in the form LINQ(Language Integrated Query ) which helps to represent complex database queries in the form of the object (strongly typed). These queries can be created within the given DBContext and Entity models (created out of the box using EFCore scaffolding commands).

Most of the time as a developer we use these queries without knowing its execution patterns.

Let’s get started

Let’s take a simple example below. I had an issue with the below query in .NET Core 3.1.

As I understood .NET Core 3.0 onwards Entity Framework Core recommends query execution on the server only. .NET core 2.1 and lower version based Entity Framework Core supported client evaluation anywhere in the query.

Client-Side Query evaluation

.NET Core 3.0 onwards Entity Framework Core has blocked client-side query evaluation and recommends query execution on the server-side only.

Lets use below simple example,

QueryLINQ Evaluation in Entity Framework Guidelines

Above evaluation produces below error in ASP.NET Core 3.1,

The LINQ expression ‘DbSet
.Where(e => e.EmployeeId
.GetEmployeeName().Contains(“007”))’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(),

Above same expression works fine in any lower version of .NET Core

Above use of expression in the ‘where’ clause causes exception

As seen above ASP.NET Core 3.0 onwards Entity Framework Core evaluates a LINQ query on the server as much as possible and Entity Framework Core blocks any client evaluation.

Such an issue can be resolved by either rewriting the query or as stated above using AsEnumrable() to force client-side evaluation.

Server-Side Query evaluation

As seen above ASP.NET Core 3.0 onwards Entity Framework Core evaluates a LINQ query on the server as much as possible and Entity Framework Core blocks any client evaluation.

After refactoring the above query as below resolve the issue as required by the framework.

QueryLINQ Evaluation in Entity Framework Guidelines

The above use of expression outside of the query instructs the compiler to execute the whole query on the server-side.

Explicit client evaluation and issues related to its usage

You might get scenarios when you might want to force client evaluation vs server evaluation.

Calling query Enumerable() or using generics ToList(), ToArray() or ToListAsync() make the code execution immediately on the client side.

QueryLINQ Evaluation in Entity Framework Guidelines

The above use of AsEnumerable() instructs the compiler to execute the whole query on the client-side.

Explicit client evaluation, however, comes with performance implications and could cause memory leaks issue if you are dealing with large data. You can use this option if dealing with small data. This approach if not used properly may cause an issue with compiled query plan and caching.

Best practices and guidelines

  • Opt in for server side evaluation:

.NET Core 3.0 onwards Entity Framework Core recommends query execution on the server. Please try opting for the same unless it is absolutely required in certain cases.

  • Opt in for Client evaluation only if necessary :

Opt-in for client-side evaluation if the query you are dealing with has a small amount of data or query has no server-side translation.

  • Always validate user input: 

Please validate the user inputs before assigning it assigned to entity properties in LINQ queries, or to other EF core APIs.(values ​​from unrecognized sources could cause security issue)

  • Always validate Expression: 

Even when using LINQ, if you are accepting user input to build expressions, please validate the input required for the intended expressions.

  • If using < EFCore 3.0 version

One can configure warning for < .NET Core 3.0 version code using techniques discussed in the Best Practices of Configuring DBContext in the EF Core.

References:

That’s All, Enjoy Coding!

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Summary

Today we looked at understanding the query evaluation in Entity Framework along with best practices around its usage. We looked at a few examples of using client-side query evaluation and server-side query evaluation in .NET or .NET Core.



Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.



Leave a Reply

Your email address will not be published. Required fields are marked *