EF6. We have a request:
var changedDocs1 = (from d in nsql.Doc //join c in nsql.Cnt on d.Receive equals c.NOwner join ad in nsql.AlterationDoc.Where(x => x.AlterTime > localVersionDate) on d.NDoc equals ad.NDoc join cep in nsql.ChangeEndPeriod.Where(x => x.prev_Employee == eqEmployee && x.DateModific > localVersionDate) on new { d.NDoc, d.Employee } equals new { cep.NDoc, Employee = cep.curr_Employee ?? -1 } into CEP from cep in CEP.DefaultIfEmpty(null) let deleteFlag = (!d.IsShipped || (cep != null && cep.curr_Employee != cep.prev_Employee)) ? true : false where d.AccountDate == Date && (d.Employee == eqEmployee || cep != null) select new { d.NDoc, Doc = d.Doc1 ?? -1, NOwner = d.Receive, d.Terms, deleteFlag, changeDate = ad.AlterTime >= (cep == null ? ad.AlterTime : cep.DateModific) ? ad.AlterTime : (cep.DateModific ?? ad.AlterTime), //c.Owner, //c.DocAddress Owner = "", DocAddress = "" }).ToList();
We look at the generated query by logs:
SELECT [Extent1].[NDoc] AS [NDoc], CASE WHEN ([Extent1].[Doc] IS NULL) THEN -1 ELSE [Extent1].[Doc] END AS [C1], [Extent1].[Receive] AS [Receive], [Extent1].[Terms] AS [Terms], CASE WHEN (([Extent1].[IsShipped] <> 1) OR (([Extent3].[Id] IS NOT NULL) AND ( NOT (([Extent3].[curr_Employee] = [Extent3].[prev_Employee]) AND ((CASE WHEN ([Extent3].[curr_Employee] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent3].[prev_Employee] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))))) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C2], CASE WHEN ([Extent2].[AlterTime] >= (CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST( [Extent2].[AlterTime] AS datetime2) ELSE [Extent3].[DateModific] END)) THEN [Extent2].[AlterTime] WHEN ([Extent3].[DateModific] IS NULL) THEN [Extent2].[AlterTime] ELSE [Extent3].[DateModific] END AS [C3], N'' AS [C4], N'' AS [C5] FROM [dbo].[Doc] AS [Extent1] WITH (NOLOCK) INNER JOIN [dbo].[AlterationDoc] AS [Extent2] WITH (NOLOCK) ON [Extent1].[NDoc] = [Extent2].[NDoc] LEFT OUTER JOIN [dbo].[ChangeEndPeriod] AS [Extent3] WITH (NOLOCK) ON ([Extent3].[prev_Employee] = @p__linq__1) AND ([Extent3].[DateModific] > @p__linq__2) AND ([Extent1].[NDoc] = [Extent3].[NDoc]) AND ([Extent1].[Employee] = (CASE WHEN ([Extent3].[curr_Employee] IS NULL) THEN -1 ELSE [Extent3].[curr_Employee] END)) WHERE ([Extent2].[AlterTime] > @p__linq__0) AND ([Extent1].[AccountDate] = @p__linq__3) AND (([Extent1].[Employee] = @p__linq__4) OR ([Extent3].[Id] IS NOT NULL))
The execution time of this query without any changes via the SqlAdapter is 0.2 seconds.
Execution time via EntityFramework - 120 seconds.
The execution results are the same.
What can cause such a difference?
UPD. The problem is localized.
At some point, SQL Server cached an extremely stupid query plan. The query through EF used it, the query through ADO - no (due to the casting of parameter types). Adding OPTION (RECOMPILE) solved the problem.