EF Core Linq多条件查询失效：非空参数未参与And逻辑

需求与问题现状 #

需求是实现Linq Where条件中「值非空则执行And操作，否则跳过」的逻辑，基于以下5个字段查询Employee Details：

• Employee Code（非空则直接返回结果）
• Employee Name
• SelectedRankId
• SelectedDistrictLocationId
• SelectedOfficeLocationId

当前问题：传入所有非空参数时，生成的SQL仅包含Employee Name的过滤条件，返回结果不符合预期（应仅返回第一条数据，实际返回多条）。

问题代码片段 #

// Model Validation -> Checking at least one value is passed, if Yes then,

IQueryable<EmployeeDetail> searchedData;
if (searchVM.EmpCode != null)
{
    searchedData = _db.EmployeeDetails.Where(x => x.EmpCode == searchVM.EmpCode);
}
else
{
    var normalizedName = searchVM.Name?.ToLower();

    searchedData = _db.EmployeeDetails
        .Where(x =>
        !string.IsNullOrWhiteSpace(normalizedName) ? x.EmpName.ToLower().Contains(normalizedName) : true
        && searchVM.SelectedRank != null ? x.CurrentRankId == searchVM.SelectedRank : true
        && searchVM.SelectedDistrictLocationId != null ? x.CurrentPlaceofPostingId == searchVM.SelectedDistrictLocationId : true
        && searchVM.SelectedOfficeLocationId != null ? x.CurrentlyPostedAtId == searchVM.SelectedOfficeLocationId : true);
}

EF Core生成的SQL #

exec sp_executesql N'SELECT [e].[EmpCode], [e].[Address], [e].[CreatedBy], [e].[CreatedOn], [e].[CurrentPlaceofPostingID], [e].[CurrentRankID], [e].[CurrentlyPostedAtID], [e].[DateOfBirth], [e].[EmpName], [e].[Gender], [e].[IsDeleted], [e].[JoiningDate], [e].[RetirementDate], [e].[Status], [e].[Telephone], [e].[UpdatedBy], [e].[UpdatedOn]
FROM [Employee_Details] AS [e]
WHERE (@__normalizedName_0 LIKE N'''') OR (CHARINDEX(@__normalizedName_0, LOWER([e].[EmpName])) > 0)',N'@__normalizedName_0 nvarchar(50)',@__normalizedName_0=N'test'

HTTP请求数据 #

{
  "Name": "test",
  "EmpCode": null,
  "SelectedRank": 1,
  "SelectedDistrictLocationId": 4,
  "SelectedOfficeLocationId": 1,
  "Ranks": null,
  "DistrictLocations": null,
  "OfficeLocations": null
}

错误返回结果 #

[
  {"EmpCode":23,...},...
]

问题原因 #

这是运算符优先级导致的逻辑错误：&& 的优先级高于 ?:，所以整个Where条件会被错误解析为嵌套结构，只有第一个三元表达式生效，后续条件被合并进第一个三元的分支中，最终EF Core仅生成Employee Name的过滤SQL。

修复方案 #

方案1：添加括号明确逻辑分组 #

给每个三元表达式加上括号，确保每个非空判断和对应过滤条件作为整体参与&&运算：

IQueryable<EmployeeDetail> searchedData;
if (searchVM.EmpCode != null)
{
    searchedData = _db.EmployeeDetails.Where(x => x.EmpCode == searchVM.EmpCode);
}
else
{
    var normalizedName = searchVM.Name?.ToLower();

    searchedData = _db.EmployeeDetails
        .Where(x =>
        (!string.IsNullOrWhiteSpace(normalizedName) ? x.EmpName.ToLower().Contains(normalizedName) : true)
        && (searchVM.SelectedRank != null ? x.CurrentRankId == searchVM.SelectedRank : true)
        && (searchVM.SelectedDistrictLocationId != null ? x.CurrentPlaceofPostingId == searchVM.SelectedDistrictLocationId : true)
        && (searchVM.SelectedOfficeLocationId != null ? x.CurrentlyPostedAtId == searchVM.SelectedOfficeLocationId : true));
}

方案2：拆分Where条件（更易读） #

逐步追加过滤条件，无需担心运算符优先级问题，EF Core会自动合并为And逻辑的SQL：

IQueryable<EmployeeDetail> searchedData = _db.EmployeeDetails;
if (searchVM.EmpCode != null)
{
    searchedData = searchedData.Where(x => x.EmpCode == searchVM.EmpCode);
}
else
{
    var normalizedName = searchVM.Name?.ToLower();
    if (!string.IsNullOrWhiteSpace(normalizedName))
    {
        searchedData = searchedData.Where(x => x.EmpName.ToLower().Contains(normalizedName));
    }
    if (searchVM.SelectedRank != null)
    {
        searchedData = searchedData.Where(x => x.CurrentRankId == searchVM.SelectedRank);
    }
    if (searchVM.SelectedDistrictLocationId != null)
    {
        searchedData = searchedData.Where(x => x.CurrentPlaceofPostingId == searchVM.SelectedDistrictLocationId);
    }
    if (searchVM.SelectedOfficeLocationId != null)
    {
        searchedData = searchedData.Where(x => x.CurrentlyPostedAtId == searchVM.SelectedOfficeLocationId);
    }
}

内容的提问来源于stack exchange，提问作者AbhiAbzs