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.

Check [this my answer](https://stackoverflow.com/questions/73471187/how-to-select-many-from-string-in-efcore/73473316#73473316). It is what you expect? — Svyatoslav Danyliv, Feb 17 '23 at 10:22

score 0 · Answer 1 · answered Feb 16 '23 at 20:43

0

You need an if statement for the case when both inputs are non-empty, and then move the conjunction into the any, so that both conditions are for the same person:

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

answered Feb 16 '23 at 20:43

Jonas Høgh

10,358
1
26
46

That was one possibility but I was just wondering if there's a better way to do it. Especially, if there are many properties other than just First and Last Name. – Andy Feb 16 '23 at 20:48
Are all those properties searched properties of the Owners, or are some properties of the account itself? – Jonas Høgh Feb 16 '23 at 20:52
The search properties could be any of the 2 classes (consumer, consumeraccount). – Andy Feb 16 '23 at 20:55
That makes sense. See new answer. Cannot provide a full code example from my phone right now, but I hope it makes sense anyway – Jonas Høgh Feb 16 '23 at 21:22
Thanks. I'll play around with that idea. – Andy Feb 16 '23 at 21:23
Using a separate predicate for consumer and the predicate looks like "`p => p.ConsumerAccountOwners.Any(cao => cao.Consumer.FirstName.Equals("FName"))) AndAlso p.ConsumerAccountOwners.Any(cao => cao.Consumer.LastName.Equals("LName"))))". Is there a way to modify so that it'll look like "p => p.ConsumerAccountOwners.Any(cao => cao.Consumer.FirstName == ("FName"))) AndAlso cao => cao.Consumer.LastName == ("LName")))`". I'm assuming it can be done with ExpressionVisitor but I'm fairly new to Expression. Any help would be really appreciated. – Andy Feb 21 '23 at 19:31
@Andy I updated the other answer with a code example. Hope it helps – Jonas Høgh Feb 22 '23 at 13:55

Jonas Høgh · Accepted Answer · 2023-02-22T13:54:59.857

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; }
    }
}

PredicateBuilder with navigation properties and multiple conditions

2 Answers2