Query(LINQ) 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,
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.
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.
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:
- Best Practices of Configuring DBContext in the EF Core
- Entity Framework Repository Implementation in ASP.NET Core
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.