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:
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.
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:
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:
_dbContext.CarrierResponses.Where(x => x.TenantId == tenantId).Where(FilterCarrierResponsesBy(modifiedLoads));
It generates SQL statements like:
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):
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:
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:
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:
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.
