LINQ stands for Language Integrated Queries, which helps developers write expressions in C# that are translated into equivalent queries for execution.
This is core to ORMs such as EF Core which relies on LINQ-to-SQL to help developers write SQL interactions on database without having to bother about database language (SQL).
In this article, let’s look at the two main concepts in LINQ execution patterns that help in writing efficient and memory conscious expressions.
When we write LINQ expressions without awareness of these concepts, we end up writing expressions which may hurt the application performance.
LINQ Deferred Execution vs Immediate Execution
Deferred Execution is when the evaluation of an expression is delayed until the result is actually required. it improves performance by avoiding unnecessary execution. applicable on any in-memory collection as well as remote LINQ providers like linq-to-sql.
For example, in the below code:
var students = new List<Student>();
var filtered = from r in students where r.Rank < 10 select r;
foreach (var student in filtered)
{
Console.WriteLine("Student: {student.Name}, Rank: {student.Rank}");
}the execution for the result “filtered” is not invoked until the point the foreach is called and executed. this improves performance by not allocating memory to the result of filtered, until the point its actually required.
var students = new List<Student>();
students.Add(new Student { });
students.Add(new Student { });
students.Add(new Student { });
students.Add(new Student { });
var filtered = from r in students where r.Rank < 10 select r;
// evaluation takes place, result is printed
foreach (var student in filtered)
{
Console.WriteLine("Student: {student.Name}, Rank: {student.Rank}");
}
students.Add(new Student { });
students.Add(new Student { });
// evaluation takes place again, result is printed
foreach (var student in filtered)
{
Console.WriteLine("Student: {student.Name}, Rank: {student.Rank}");
}Deferred Execution re-evaluates the expression on each call, this results in obtaining latest dataset on every call. Hence this is also called as Lazy Evaluation.
To implement deferred execution on any method that returns an IEnumerable, you need to use yield keyword.
Immediate Execution is the opposite to deferred execution, where the expression is executed immediately instead of waiting for its actual usage. To convert a deferred expression to immediate execution, we call the To…() method.
var students = new List<Student>();
students.Add(new Student { });
students.Add(new Student { });
students.Add(new Student { });
students.Add(new Student { });
var filtered = (from r in students where r.Rank < 10 select r).ToList();
// evaluation takes place
foreach (var student in filtered)
{
Console.WriteLine("Student: {student.Name}, Rank: {student.Rank}");
}
// result is printedClient Evaluation vs Server Evaluation
EF Core tries to convert and evaluate the input expression on the database server as much as possible. It converts parts of the query into parameters which is evaluated on the client side, the remaining is then carried over to the database server for the execution. This results in lesser stress on the client and a better performance in cases when the query runs on a large dataset.
var rankers = _context.Students.Where(x => x.Rank <= 10);This expression is completely convertible into an equivalent query and so this evaluation takes place in the database instead of the client.
If an expression is not able to be translated onto the equivalent query, EF Core tries to execute parts on the database which can be converted and then applies the unconverted parts on the client side.
_context.Students.Where(x => x.Rank <= 10)
.Select(x => new Ranker
{
Id = x.Id,
Name = x.Name,
Grade = ConvertScoreToGrade(x.Score),
Rank = x.Rank
});In this case, the expression can only be partly converted into an equivalent query; because the method ConvertScoreToGrade() is unknown to the database.
In this case, expression till the Where() clause, happens in the database in its query form and the result of this dataset is brought to the client and the Select() is applied. Sometimes this might cause larger issues on the client, when the dataset is very large, hence EF Core throws runtime errors in such cases.
dbug: 12-03-2021 12:06:20.734 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query)
Compiling query expression:
'DbSet<Student>()
.Where(x => x.Rank <= 10)
.Select(x => new Ranker{
Id = x.Id,
Name = x.Name,
Grade = StudentsController.ConvertScoreToGrade(x.Score),
Rank = x.Rank
}
)'
fail: Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HM7544FLHELT", Request id "0HM7544FLHELT:00000001": An unhandled exception was thrown by the application.
System.InvalidOperationException: The client projection contains a reference to a constant expression of 'ODataCore3.API.Controllers.StudentsController' through the instance method 'ConvertScoreToGrade'. This could potentially cause a memory leak; consider making the method static so that it does not capture constant in the instance. See https://go.microsoft.com/fwlink/?linkid=2103067 for more information.
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.ConstantVerifyingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
For example, the below query throws an error.
var rankers = _context.Students.Where(x =>
x.Name.Contains("Jr", StringComparison.OrdinalIgnoreCase));This is because, the Contains() overload with a StringComparison doesn’t have an equivalent query translation and so EF throws an exception for translation, whereas the below expression would work fine without an issue –
var rankers = _context.Students.Where(x => x.Name.Contains("Jr"));which gets translated as:
info: 12-03-2021 12:10:42.624 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (159ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [s].[Id], [s].[Class], [s].[DateOfBirth], [s].[Name], [s].[Rank], [s].[Score]
FROM [Students] AS [s]
WHERE [s].[Name] LIKE N'%Jr%'If we look at the error that occurs for the case above:
dbug: 12-03-2021 12:07:02.846 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query)
Compiling query expression:
'DbSet<Student>()
.Where(x => x.Name.Contains(
value: "Jr",
comparisonType: OrdinalIgnoreCase))'
fail: Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HM7544FLHELU", Request id "0HM7544FLHELU:00000001": An unhandled exception was thrown by the application.
System.InvalidOperationException: The LINQ expression 'DbSet<Student>()
.Where(s => s.Name.Contains(
value: "Jr",
comparisonType: OrdinalIgnoreCase))' could not be translated.The next part of this exception is what makes this case interesting –
Additional information: Translation of method 'string.Contains' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.The exception suggests the expression either to be refactored, or be called with a ToList() method so as to make the expression be executed “locally”. When we affix the expression with a ToList(), the call now throws no error:
_context.Students.ToList()
.Where(x => x.Name.Contains("Jr", StringComparison.OrdinalIgnoreCase));which translates to the below query –
Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [s].[Id], [s].[Class], [s].[DateOfBirth], [s].[Name], [s].[Rank], [s].[Score]
FROM [Students] AS [s]You can see that the WHERE clause is now missing, because we’ve now affixed the ToList() method, which switches to a ‘client evaluation’, In this case, the expression brings all the results of the Student table to the client memory and runs the WHERE condition on the client.
Similarly, in the case of Select() method where we’re calling ConvertScoreToGrade() method call, we can fix the error too by switching to client evaluation.
_context.Students.Where(x => x.Rank <= 10)
.ToList().Select(x => new Ranker
{
Id = x.Id,
Name = x.Name,
Grade = ConvertScoreToGrade(x.Score),
Rank = x.Rank
});This expression now runs without any issues and the converted query looks like below:
Executed DbCommand (161ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [s].[Id], [s].[Class], [s].[DateOfBirth], [s].[Name], [s].[Rank], [s].[Score]
FROM [Students] AS [s]
WHERE [s].[Rank] <= 10This gets the job done without any issues, but creates a serious performance problem, when the Student table is very large.
Conclusion – Some Observations
Although Client evaluation and Server evaluation are concepts of Entity Framework Core, they follow the concepts of Immediate execution and Deferred execution internally.
This is because by definition, deferred and immediate execution apply to evaluation of operations on all collections and LINQ-to-SQL operations – the latter is what used by EF Core.
By default, EF Core expressions follow deferred evaluation – particularly the WHERE clauses where IQueryable is returned. For example, the below code is not executed untill the control moves out of the Action method.
[HttpGet, Route("GetToppers")]
public IEnumerable<Student> GetToppers()
{
var results = _context.Students.Where(x => x.Rank <= 10);
// no execution here
results = results.Where(x => x.Id % 5 == 0);
// no execution here
return results;
// no execution here
}
// out of method control, into response
// executed hereClient evaluation implies immediate execution, this is because the client requires the data to be available right during its execution. This is the reason in both the cases, we affix a To…() method to the expression.
Found this article helpful? Please consider supporting!
