PredicateBuilder with navigation properties and multiple conditions

Question

I'm using the PredicateBuilder in the LinqKit to dynamically form the Where expression. When either LastName or FirstName from the SearchCriteria is populated then the SQL statement works correctly. However when both FirstName and LastName are used to search for ConsumerAccount then the generated SQL (as shown below) will not bring back the correct number of rows.

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 EXISTS (
    SELECT 1
    FROM [ConsumerAccountOwner] AS [c2]
    INNER JOIN [Consumer] AS [c3] ON [c2].[ConsumerId] = [c3].[ConsumerId]
    WHERE [c].[ConsumerAccountId] = [c2].[ConsumerAccountId] AND [c3].[LastName]) = 'lName')

Is there a way to get it to generate the WHERE clause with both conditions for LastName and FirstName as follows?

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')

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

    var predicate = PredicateBuilder.New<ConsumerAccount>();

    if (!string.IsNullOrEmpty(searchCriteria.SearchLastName))
    {
        predicate = predicate.And(ca => ca.Owners.Any(o => o.Consumer.LastName.Equals(searchCriteria.SearchLastName)));
    }

    if (!string.IsNullOrEmpty(searchCriteria.SearchFirstName))
    {
        predicate = predicate.And(ca => ca.Owners.Any(o => o.Consumer.FirstName.Equals(searchCriteria.SearchFirstName)));
    }

    var query = (from ca in _dbContext.ConsumerAccounts.AsExpendable().Where(predicate)
                     join ao in _dbContext.AccountOwners on ca.Id equals ao.Id
                     join c in _dbContext.Consumers on ao.ConsumerId equals c.ConsumerId
                     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; }
}

I have tried to join the tables in different ways but could not to get it to work for all scenarios.

Answer 1

If you need a large amount of conditions filtering on different parts of the entity graph, you probably want to build multiple dynamic predicates for each subgraph filter. E.g the owner filters should go in an owner specific predicate, so that they all apply to one owner, and any filters on the account should be added to a separate predicate.

Here is an example running two predicates at the account and owner levels against an in-memory collection. I'm sure you can integrate it with your EF setup quite easily:

using System;
using System.Collections.Generic;
using System.Linq;
using LinqKit;

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

        var accountPredicate = PredicateBuilder.New<ConsumerAccount>();

        var ownerPredicate = PredicateBuilder.New<AccountOwner>();

        if (!string.IsNullOrEmpty(searchCriteria.SearchLastName))
        {
            ownerPredicate = ownerPredicate.And(o => o.Consumer.FirstName.Equals(searchCriteria.SearchLastName));
        }

        if (!string.IsNullOrEmpty(searchCriteria.SearchFirstName))
        {
            ownerPredicate = ownerPredicate.And(o => o.Consumer.FirstName.Equals(searchCriteria.SearchFirstName));
        }

        if (!string.IsNullOrEmpty(searchCriteria.SearchSomeAccountProperty)) {
            accountPredicate = accountPredicate.And(a => a.SomeAccountProperty.Equals(searchCriteria.SearchSomeAccountProperty));
        }

        var fakeData = new List<ConsumerAccount>();

        var results = fakeData.Where(accountPredicate).Where(a => a.Owners.Any(ownerPredicate)).ToList();
    }

    public class SearchCriteria
    {
        public string SearchLastName { get; set; }
        public string SearchFirstName { get; set; }
        public string SearchSomeAccountProperty { 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 string SomeAccountProperty { 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; }
    }
}