Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / C#
Tip/Trick

Fixes to LINQ to SQL Explicit Client Evaluation which Causes Poor Performance

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
25 Dec 2021CPOL2 min read 3.9K   3  
Entity Framework Core client evaluation was disabled by default since version 3.0 because of performance.

Introduction

The web app is based on EF3.1. A SQL profiling against the production database shows that almost 100,000 table rows were retrieved.

Background

The LINQ queries that cause so many table rows returned looks like these:

LINQ
var responses = (from cr in _dbContext.CarrierResponses where cr.TenantId == tenantId select cr).ToList()
var modified = responses.Where(cr => modifiedLoads.Any(m => cr.LoadId == m.LoadId && cr.DateModified < m.ModifiedDateTimeUtc));

where modifiedLoads is a list of modified load passed from app user request. The first statement returns a overwhelming records if the tenant is very active.

I was wondering why the original code author wrote this query to return so many table records. From the source code commits history, I found the codes were written some time ago as below.

LINQ
var responses = from cr in _dbContext.CarrierResponses
                where cr.TenantId == tenantId && modifiedLoads.Any(m => cr.LoadId == m.LoadId && cr.DateModified < m.ModifiedDateTimeUtc)
                select cr;

It should have been working prior to EF Core 3.0, when client evaluation was allowed by default. By the way, even if it worked, according to the doc under Breaking Changes in EF Core 3.x, "This behavior can result in unexpected and potentially damaging behavior that may only become evident in production....can cause all rows from the table to be transferred from the database server, and the filter to be applied on the client.", it is not a good idea to use client evaluation in this case.

The app should have been upgraded with new version of EF core some time, so that the original LINQ did not work any longer, and a bug fix was created like that shown above. It just made it work, the performance issue which may only become evident in production was not fixed.

For more information about explicit client evaluation by a linq operation, please refer to Client vs. Server Evaluation.

Fixes to the Problem

1. Fix by dynamic Lambda Expression

Create a dynamic Expression function:

C#
private Expression<Func<CarrierResponse, bool>> FilterCarrierResponsesBy(IEnumerable<OpenLoadModified> modifiedLoads)
{
    var carrierResponseFilterExpression = Expression.Parameter(typeof(CarrierResponse));
    CarrierResponse cr;
    var expressionList = new List<Expression>();
    modifiedLoads.ToList().ForEach(x =>
    {
         var exprLoadIdEqual = Expression.Equal(Expression.Property(carrierResponseFilterExpression, nameof(cr.LoadId)), Expression.Constant(x.LoadId));
         var exprDateTimeLessThan = Expression.LessThan(Expression.Property(carrierResponseFilterExpression, nameof(cr.DateCreated)), Expression.Constant(x.ModifiedDateTimeUtc));
         var andExp = Expression.And(exprLoadIdEqual, exprDateTimeLessThan);
         expressionList.Add(andExp);
    });

    var exprModifiedLoadOr = null as Expression;
    foreach (var expr in expressionList)
    {
        if (exprModifiedLoadOr == null)
        {
            exprModifiedLoadOr = expr;
            continue;
        }
        exprModifiedLoadOr = Expression.Or(exprModifiedLoadOr, expr);
    }
    return Expression.Lambda<Func<CarrierResponse, bool>>(exprModifiedLoadOr, carrierResponseFilterExpression);
}

Use this dynamic Expression in this way:

LINQ
_dbContext.CarrierResponses.Where(x => x.TenantId == tenantId).Where(FilterCarrierResponsesBy(modifiedLoads));

It generates SQL statements like:

SQL
exec sp_executesql N'SELECT [c].[Id], [c].[CarrierId], [c].[DateCreated], [c].[DateModified], [c].[IsLoadModified], [c].[LoadId], [c].[PrevResponseCode], [c].[PrevUserName], [c].[ResponseCode], [c].[TenantId], [c].[UserName]
FROM [CarrierResponses] AS [c]
WHERE ([c].[TenantId] = @__tenantId_0) AND (((CASE
    WHEN [c].[LoadId] = N''999'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [c].[DateCreated] < ''2021-12-20T19:13:19.6866667'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) | (CASE
    WHEN [c].[LoadId] = N''1000'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [c].[DateCreated] < ''2021-12-20T19:13:19.6866667'' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END)) = CAST(1 AS bit))',N'@__tenantId_0 bigint',@__tenantId_0=30

which runs so fast as "Reads: 57, Duration: 3662" from the Profiler, compared with "Reads: 43, Duration: 35186" for the exsiting codes which uses client evaluation.

2. Fix by Stored Procedure

Firstly, create a user-defined data type (supported since SQL Server 2012):

SQL
CREATE TYPE [dbo].[OpenLoadModified] AS TABLE(
    [LspId] [nvarchar](10) NOT NULL,
    [LoadId] [nvarchar](30) NOT NULL,
    [ModifiedDateTimeUtc] [datetime2](7) NULL,
    PRIMARY KEY CLUSTERED 
  (
    [LspId] ASC,
    [LoadId] ASC
  ) WITH (IGNORE_DUP_KEY = OFF)
)

Then create the Stored Procedure:

SQL
CREATE PROC [dbo].[GetCarrierResponses]
(
    @TenantId BIGINT,
    @LoadsModified OpenLoadModified READONLY
)
AS
BEGIN
    SELECT r.*
    FROM CarrierResponses AS r WITH(NOLOCK)
    JOIN @LoadsModified AS m ON r.[LoadId] = m.[LoadId]
    WHERE r.TenantId = @TenantId AND r.DateModified < m.ModifiedDateTimeUtc
END

Lastly, in the business part of the app, use these codes:

C#
var dtModifiedLoads = new DataTable();
dtModifiedLoads.Columns.Add("LspId", typeof(string));
dtModifiedLoads.Columns.Add("LoadId", typeof(string));
dtModifiedLoads.Columns.Add("ModifiedDateTimeUtc", typeof(DateTime));

foreach (var load in modifiedLoads)
{
    var row = dtModifiedLoads.NewRow();
    row["LspId"] = load.LspId;
    row["LoadId"] = load.LoadId;
    row["ModifiedDateTimeUtc"] = load.ModifiedDateTimeUtc;
    dtModifiedLoads.Rows.Add(row);
}

_DAL.GetDataSetBySqlParameter("[dbo].[GetCarrierResponses]", new SqlParameter[] {
    new SqlParameter("@TenantId", tenantId),
    new SqlParameter("@LoadsModified", SqlDbType.Structured)
    {
        TypeName = "dbo.OpenLoadModified",
        Value = dtModifiedLoads
    }
});

It generates these SQL statements:

SQL
declare @p2 dbo.OpenLoadModified
insert into @p2 values(N'7929497',N'999','2021-12-20 19:13:19.6866667')
insert into @p2 values(N'7929497',N'1000','2021-12-20 19:13:19.6866667')

exec [dbo].[GetCarrierResponses] @TenantId=30,@LoadsModified=@p2

For which the Profiler reports "Reads: 50, Duration: 4300".

Points of Interest

In the early days of Entity Framework, I might have written some client evaluation LINQ codes. Hopefully it did not cause too big performance issue, and the fixes listed above could be of any help to the bug fixers.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --