LinqKit PredicateBuilder多条件关联查询SQL生成不符预期求助

问题根源 #

当前代码中，分别对FirstName和LastName添加独立的Any条件，EF会生成两个独立的EXISTS子查询，这会匹配存在至少一个所有者符合FirstName，且存在至少一个所有者符合LastName的账户（两个条件可对应不同所有者），而非你需要的存在同一个所有者同时符合FirstName和LastName的账户。

修正方案 #

需要把针对Consumer的多个条件合并到同一个Owners.Any()的判断逻辑中，动态构建Consumer的过滤条件，而非拆分多个Any。

修正后的代码 #

public static void Main()
{
    var searchCriteria = new SearchCriteria();
    searchCriteria.SearchLastName = "LastName1";
    searchCriteria.SearchFirstName = "FirstName1";

    // 先构建针对Consumer的过滤条件
    var consumerPredicate = PredicateBuilder.New<Consumer>();
    if (!string.IsNullOrEmpty(searchCriteria.SearchLastName))
    {
        consumerPredicate = consumerPredicate.And(c => c.LastName.Equals(searchCriteria.SearchLastName));
    }
    if (!string.IsNullOrEmpty(searchCriteria.SearchFirstName))
    {
        consumerPredicate = consumerPredicate.And(c => c.FirstName.Equals(searchCriteria.SearchFirstName));
    }

    // 再将Consumer的条件合并到ConsumerAccount的查询中
    var accountPredicate = PredicateBuilder.New<ConsumerAccount>();
    if (consumerPredicate.Body.NodeType != ExpressionType.Constant || !(bool)((ConstantExpression)consumerPredicate.Body).Value)
    {
        // 仅当有实际过滤条件时添加
        accountPredicate = accountPredicate.And(ca => ca.Owners.Any(o => consumerPredicate.Invoke(o.Consumer)));
    }

    var query = (from ca in _dbContext.ConsumerAccounts.AsExpandable().Where(accountPredicate)
                 join ao in _dbContext.AccountOwners on ca.Id equals ao.Id
                 join c in _dbContext.Consumers on ao.ConsumerId equals c.Id
                 select ca.Id).Distinct();

    var accountCount = query.Count();
}

// 实体类保持不变
public class SearchCriteria
{
    public string SearchLastName { get; set; }
    public string SearchFirstName { get; set; }
}

public class Consumer
{
    public int Id { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public List<ConsumerAccount> Accounts { get; set; }
}

public class ConsumerAccount
{
    public int Id { get; set; }
    public int ConsumerId { get; set; }
    public List<AccountOwner> Owners { get; set; }
}

public class AccountOwner
{
    public int Id { get; set; } 
    public int ConsumerId { get; set; }
    public int ConsumerAccountId { get; set; }
    public Consumer Consumer { get; set; }
}

代码解释 #

1. 构建Consumer过滤条件：用PredicateBuilder动态拼接FirstName和LastName的判断逻辑，确保两个条件针对同一个Consumer实体。
2. 合并到Account查询：通过Owners.Any(o => consumerPredicate.Invoke(o.Consumer))，让EF生成单个EXISTS子查询，同时包含两个条件，保证是同一个所有者满足双条件。
3. 空条件判断：添加对consumerPredicate的校验，避免无搜索条件时生成无效查询逻辑。

生成的SQL效果 #

修正后生成的SQL与你期望的一致：

SELECT distinct [c].[consumerAccountId]
FROM [dbo].[ConsumerAccount]  AS [c]
INNER JOIN [ConsumerAccountOwner] AS [c4] on [c].[ConsumerAccountId] = [c4].[ConsumerAccountId]
INNER JOIN [Consumer] AS [c5] ON [c4].[ConsumerId] = [c5].[ConsumerId]
WHERE EXISTS (
    SELECT 1
    FROM [consumeraccountowner] AS [c0]
    INNER JOIN [consumer] AS [c1] ON [c0].[consumerId] = [c1].[consumerId]
    WHERE [c].[consumerAccountId] = [c0].[consumerAccountId] 
      AND [c1].[FirstName] = 'fName' 
      AND [c1].[LastName] = 'lName'
)

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