c# - SQL "IN"CLAUSE 性能问题

Question

我有一个简单的 SQL 语句:

select * from Employee 
where LEFT(REPLACE(EmployeeName,'.',''),4) IN ('ABCE', 'BCDS', 'EDSC', 'XDSD', 'EFSE')

我正尝试在 Linq 中这样做:

Employees.Where(x => new[] { "ABCE", "BCDS", "EDSC", "XDSD", "EFSE" }.Contains((x.EmployeeName.Replace(".", "").Substring(0, 4))));

但是它生成的SQL语句效率不高。 EmployeeName 在与字符串比较之前每次都会更新，而不是只更新一次:

SELECT 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[EmployeeName] AS [EmployeeName], 
[Extent1].[EmployeeTypeID] AS [EmployeeTypeID], 
[Extent1].[Active] AS [Active]
FROM [dbo].[Employee] AS [Extent1]
WHERE (N'ABCE' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'BCDS' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'EDsC' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'XDSs' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'EFSE' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4)))

如何让生成的SQL看起来更像原来的sql语句？谢谢

Answer 1

我只使用 linqpad 对其进行了测试，但我认为这会生成更高效的查询。

var result = from record in Employee
             let name = record.EmployeeName.Replace( ".", "" ).Substring( 0, 4 )
             where new[] { "ABCE", "BCDS", [...] }.Contains( name )
             select record;