Hi,

I normally use the PredicateBuilder class to filter my queries. In the following query, I would like to retrieve the customers with their "main" address (here MainThirdPartyAddressTown) when the town starts by a filter passed as paramter to the method. I can't figure how to do it using the PredicateBuilder class. Is there a way ?

        
private IList<ThirdPartyCustomerProjection> GetCustomers(System.Linq.Expressions.Expression<System.Func<ThirdPartyCustomerEntity, bool>> predicate, string townFilter)
        {
            LinqMetaData metaData = new LinqMetaData(Adapter);

            // Apply the filters created using PredicateBuilder
            var tempoQuery = metaData.ThirdPartyCustomer
                .Select(o => o);
            if (predicate != null)
            {
                tempoQuery = tempoQuery.Where(predicate);
            }

            // Create the query
            var query =
                from thirdPartyCustomer in tempoQuery
                join thirdPartyAddress in
                    (
                        from address in metaData.ThirdPartyAddress
                        where address.SortNumber == 1
                        select address
                    ) on thirdPartyCustomer.Id equals thirdPartyAddress.FkThirdPartyId into leftJoinedThirdPartyAddresses
                from leftJoinedThirdPartyAddress in leftJoinedThirdPartyAddresses.DefaultIfEmpty()
                where thirdPartyCustomer.FkThirdPartyTypeId == (long)ThirdPartyTypeEnumeration.Customer
                  && thirdPartyCustomer.FkThirdPartyOwnCompanyId == thirdPartyOwnCompanyId
                select new ThirdPartyCustomerProjection
                {
                    Id = thirdPartyCustomer.Id,
                    Name = thirdPartyCustomer.Name,
                    MainThirdPartyAddressTown = leftJoinedThirdPartyAddress.Town
                };

            // THE LINE I WOULD LIKE TO REPLACE USING PredicateBuilder 
            query = query.Where(thirdPartyCustomer => thirdPartyCustomer.MainThirdPartyAddressTown.Contains(townFilter));

            // Execute the query
            return query.ToList<ThirdPartyCustomerProjection>();
        }

sybjeb wrote:

// THE LINE I WOULD LIKE TO REPLACE USING PredicateBuilder 
query = query.Where(thirdPartyCustomer => thirdPartyCustomer.MainThirdPartyAddressTown.Contains(townFilter));

I don't see what is the problem. I mean, you can pass an additional predicateBuilder expression to your method so you can use it in this last Where. A Predicate builder expression can include a Contains(...) expression. So maybe I don't understand your question.

I will try to explain. I used to design my DAO like this :

• GetList() : returns the complete list of elements, so without any filter.
• GetList(string searchFilter, int pageIndex, int pageSize) : returns a paged list of elements, filtered by a criteria entered by user (done on a certain number of columns)
• GetListQuery(System.Linq.Expressions.Expression<System.Func<XXXXXEntity, bool>> predicate) : called by the 2 previous methods which create the predicate and fill it with their needs.

Example :

  public IList<ThirdPartyCustomerProjection> GetList()
        {
            // Define the criterias
            var predicate = PredicateBuilder.Null<ThirdPartyCustomerEntity>();

            // Create the query
            var query = GetListQuery(predicate);

            // Retrieve the records
            query = query
                .OrderBy(thirdPartyCustomer => thirdPartyCustomer.Name);
            return query.ToList<ThirdPartyCustomerProjection>();
        }

  public PagedList<ThirdPartyCustomerProjection> GetList(string searchFilter, int pageIndex, int pageSize)
        {
            // Define the criteria
            var predicate = PredicateBuilder.Null<ThirdPartyCustomerEntity>();

            // Create the query
            var query = GetListQuery(predicate);

            if (!string.IsNullOrEmpty(searchFilter)) // I would like to do this using PredicateBuilder class, so move it before the call to GetListQuery
            {
                query = query.Where(thirdPartyCustomer =>
                    string.Concat
                    (
                        thirdPartyCustomer.Name,
                        string.Concat
                        (
                            thirdPartyCustomer.FirstName == null ? "" : thirdPartyCustomer.FirstName,
                            string.Concat
                            (
                                thirdPartyCustomer.Code == null ? "" : thirdPartyCustomer.Code,
                                thirdPartyCustomer.MainThirdPartyAddressTown == null ? string.Empty : thirdPartyCustomer.MainThirdPartyAddressTown
                            )
                        )
                    )
                    .Contains(GetFullTextSearchFilter(searchFilter)));
            }

            // Retrieve the records
            int totalRecords = query.Count();

            query = query
                .OrderBy(thirdPartyCustomer => thirdPartyCustomer.Name)
                .TakePage(pageIndex, pageSize);
            IList<ThirdPartyCustomerProjection> thirdPartyCustomers = query.ToList<ThirdPartyCustomerProjection>();

            return new PagedList<ThirdPartyCustomerProjection>(pageIndex, pageSize, totalRecords, thirdPartyCustomers);

 private IQueryable<ThirdPartyCustomerProjection> GetListQuery(         System.Linq.Expressions.Expression<System.Func<ThirdPartyCustomerEntity, bool>> predicate)
        {
            LinqMetaData metaData = new LinqMetaData(Adapter);

            var tempoQuery = metaData.ThirdPartyCustomer
                            .Select(o => o);
            if (predicate != null)
            {
                tempoQuery = tempoQuery.Where(predicate);
            }

            var query =
                from thirdPartyCustomer in tempoQuery
                join thirdPartyAddress in 
                (
                    from address in metaData.ThirdPartyAddress
                    where address.SortNumber == 1
                    select address
                ) on thirdPartyCustomer.Id equals thirdPartyAddress.FkThirdPartyId into leftJoinedThirdPartyAddresses
                from leftJoinedThirdPartyAddress in leftJoinedThirdPartyAddresses.DefaultIfEmpty()
                where thirdPartyCustomer.FkThirdPartyTypeId == (long)ThirdPartyTypeEnumeration.Customer
                  && thirdPartyCustomer.FkThirdPartyOwnCompanyId == thirdPartyOwnCompanyId
                select new ThirdPartyCustomerProjection
                {
                    Id = thirdPartyCustomer.Id,
                    Name = thirdPartyCustomer.Name,
                    FirstName = thirdPartyCustomer.FirstName,
                    Code = thirdPartyCustomer.Code,
                    IsPhysical = thirdPartyCustomer.IsPhysical,
                    IsActiveRecord = thirdPartyCustomer.IsActiveRecord,
                    MainThirdPartyAddressId = leftJoinedThirdPartyAddress.Id,
                    MainThirdPartyAddressTown = leftJoinedThirdPartyAddress.Town
                };

            return query;
        }

=> What I would like is to replace the query.Where (which you will find in the GetList method) by a predicate.And call (that would need to be moved before the GetListQuery called).

Note : If I refactor the GetListQuery to apply the predicate after the query creation and not before (as now), the columns I use in the predicate would have to be present in the DTO that the query returns and I don't want it.

What I would like is to replace the query.Where (which you will find in the GetList method) by a predicate.And

just use: predicate = predicate.And(......);

Then pass predicate to the GetListQueryMethod()

I don't think you understand the situation. I already pass the predicate variable to the GetListQuery() method but if I try to add the filter on the searchFilter variable, it will not run because predicate is applied to the tempoQuery variable and not to the query variable.

So the solution would be to apply it to the query variable but in a general manner, I don't want to apply it to the query variable because it would force me to add columns used by the filter to the DTO (here ThirdPartyCustomerProjection).

sybjeb wrote:

I don't think you understand the situation. I already pass the predicate variable to the GetListQuery() method but if I try to add the filter on the searchFilter variable, it will not run because predicate is applied to the tempoQuery variable and not to the query variable.

I think I understand. So you are using a filter for the collection itself, then another filter after the projection.

sybjeb wrote:

So the solution would be to apply it to the query variable but in a general manner, I don't want to apply it to the query variable because it would force me to add columns used by the filter to the DTO (here ThirdPartyCustomerProjection).

Yes, the only way is to use two predicates, one before the projection and another after the projection. I don't know whether the question is if you can use other classes other than entities in the PredicateBuilder, but you can:

// setup the first predicate
var predicateBeforeProjection = PredicateBuilder.Null<CustomerEntity>();
predicateBeforeProjection = predicateBeforeProjection.And<CustomerEntity>(c => c.Country == "USA");

// build the query
var adapter = new DataAccessAdapter();          
var metaData = new LinqMetaData(adapter);
var q1 = metaData.Customer.Where(predicateBeforeProjection);
var q2 = (from c in q1
            select new CustomerProjection
            {
                CustomerID = c.CustomerId,
                Name = c.CompanyName,
                City = c.City
            });

// setup the second filter
var predicateAfterProjection = PredicateBuilder.Null<CustomerProjection>();
predicateAfterProjection = predicateAfterProjection.And(c => c.Name.StartsWith("G"));

// build the final query
var q3 = q2.Where(predicateAfterProjection);

// fetch and tests
var results = q3.ToList();
Assert.AreEqual(1, results.Count);

So maybe you can pass two filters to your GetListQuery:

private IQueryable<ThirdPartyCustomerProjection> GetListQuery(
     System.Linq.Expressions.Expression<System.Func<ThirdPartyCustomerEntity, bool>> predicateBeforeProjection, 
System.Linq.Expressions.Expression<System.Func<ThirdPartyCustomerProjection, bool>> predicateAfterProjection)
        {
            LinqMetaData metaData = new LinqMetaData(Adapter);

            var tempoQuery = metaData.ThirdPartyCustomer
                            .Select(o => o);
            if (predicateBeforeProjection != null)
            {
                tempoQuery = tempoQuery.Where(predicateBeforeProjection);
            }

            var query =
                from thirdPartyCustomer in tempoQuery
                join thirdPartyAddress in 
                (
                    from address in metaData.ThirdPartyAddress
                    where address.SortNumber == 1
                    select address
                ) on thirdPartyCustomer.Id equals thirdPartyAddress.FkThirdPartyId into leftJoinedThirdPartyAddresses
                from leftJoinedThirdPartyAddress in leftJoinedThirdPartyAddresses.DefaultIfEmpty()
                where thirdPartyCustomer.FkThirdPartyTypeId == (long)ThirdPartyTypeEnumeration.Customer
                 && thirdPartyCustomer.FkThirdPartyOwnCompanyId == thirdPartyOwnCompanyId
                select new ThirdPartyCustomerProjection
                {
                    Id = thirdPartyCustomer.Id,
                    Name = thirdPartyCustomer.Name,
                    FirstName = thirdPartyCustomer.FirstName,
                    Code = thirdPartyCustomer.Code,
                    IsPhysical = thirdPartyCustomer.IsPhysical,
                    IsActiveRecord = thirdPartyCustomer.IsActiveRecord,
                    MainThirdPartyAddressId = leftJoinedThirdPartyAddress.Id,
                    MainThirdPartyAddressTown = leftJoinedThirdPartyAddress.Town
                };

            if (predicateAfterProjection != null)
            {
                query = query.Where(predicateAfterProjection );
            }

            return query;
        }

If you want to pass just one predicateBuilder instance then you should reformulate your query logic. I don't know if this actually answer your question, I hope so.

The solution you give me is the one I actually use, not using a predicate but adding a Where clause to the query (see GetList(string searchFilter, int pageIndex, int pageSize) method).

If I had to write a SQL query like this using string concatenation (and not ORM), I would be able to add the predicate directly on the query variable like this :

private string GetListQuery(string predicate)
        {
            var query = "SELECT 
                   thirdPartyCustomer.Id,
                   thirdPartyCustomer.Name,
                   thirdPartyCustomer.FirstName,
                   thirdPartyCustomer.Code,
                   thirdPartyCustomer.IsPhysical,
                   thirdPartyCustomer.IsActiveRecord,
                   leftJoinedThirdPartyAddress.Id,
                   leftJoinedThirdPartyAddress.Town
                   FROM third_party_customer
                   LEFT JOIN third_party_address ON (third_party_address.SortNumber = 1 AND  third_party_customer.Id = third_party_address.FK_third_party_customer_Id)";

            if (!string.IsNullOrEmpy(predicate))
            {
                query += "WHERE " + predicate;
            }
}

Because Linq not seems to support predicate directly, I have to add it using a lambda expression so create 2 queries : * _**tempoQuery **_just to apply the predicate * query which is the real query

var tempoQuery = metaData.ThirdPartyCustomer
                            .Select(o => o);
            if (predicate != null)
            {
                tempoQuery = tempoQuery.Where(predicate);
            }

            var query =
                from thirdPartyCustomer in tempoQuery
                ...

As I wrote it before, if I apply the predicate on the query variable (and not the tempoQuery), the DTO (here ThirdPartyCustomerProjection) would need to contain all the columns used by the predicate and not only the one I want to return which is not acceptable in my case.

So what I ask you is : Is there a way to apply the predicate on the final query without having to add all columns used by the predicate in the DTO (output) of the query as I would do it in using SQL built with string concatenation ?

Thanks in advance

This line:

query = query.Where(thirdPartyCustomer => thirdPartyCustomer.MainThirdPartyAddressTown.Contains(townFilter));

will be adding a Where call to the query. As the Where call has no projection, the already present projection will be used and the where call will be merged into the main query.

Problem is: you filter on something in the projection, so the filter has to be applied AFTER the projection. So what you want is actually that the query engine looks what the source is of thirdPartyCustomer.MainThirdPartyAddressTown in the projection and apply the filter onto that field (which is in this case: leftJoinedThirdPartyAddress.Town).

That's not possible: the engine isn't analyzing what the predicate does and what it affects and perhaps it should be rewritten: you specified this, and it's doing that: the projection might be used for a join later, which means the query isn't executed as a projection but as a subquery. The expression tree will then not be flattened but the where will be moved outside the join clause. Because your query is flattened to be able to produce a result (where is not a projection so it's merged with the select), you'll get this result.

David had the proper answer: you want to execute one filter before and one after the projection.

So might you do instead:

private IList<ThirdPartyCustomerProjection> GetCustomers(System.Linq.Expressions.Expression<System.Func<ThirdPartyCustomerEntity, bool>> predicate, string townFilter)
{
    LinqMetaData metaData = new LinqMetaData(Adapter);

    // Apply the filters created using PredicateBuilder
    var tempoQuery = metaData.ThirdPartyCustomer
        .Select(o => o);
    if (predicate != null)
    {
        tempoQuery = tempoQuery.Where(predicate);
    }

    // Create the query
    var query =
        from thirdPartyCustomer in tempoQuery
        join thirdPartyAddress in
            (
                from address in metaData.ThirdPartyAddress
                where address.SortNumber == 1
                select address
            ) on thirdPartyCustomer.Id equals thirdPartyAddress.FkThirdPartyId into leftJoinedThirdPartyAddresses
        from leftJoinedThirdPartyAddress in leftJoinedThirdPartyAddresses.DefaultIfEmpty()
        where thirdPartyCustomer.FkThirdPartyTypeId == (long)ThirdPartyTypeEnumeration.Customer
         && thirdPartyCustomer.FkThirdPartyOwnCompanyId == thirdPartyOwnCompanyId
        select new { Id = thirdPartyCustomer.Id, Name = thirdPartyCustomer.Name, Town = leftJoinedThirdPartyAddress.Town };
        
    if(!string.IsNullOrEmpty(townFilter))
    {
        query = query.Where(v=>v.Town.Contains(townFilter));
    }
    query = query.Select(v=>new ThirdPartyCustomerProjection
        {
            Id = v.Id,
            Name = v.Name,
            MainThirdPartyAddressTown = v.Town
        };

    // Execute the query
    return query.ToList<ThirdPartyCustomerProjection>();
}

But I haven't tested it in real life, so it might crash and burn somewhere.

OK so what I understand is that, due to the strongly typed language, it's not possible to manage all the filters with one unique predicate as I would do it in SQL string concatenation.

Sometimes, strongly typed language must have some inconvenients.

=> I close the ticket

Thanks for your answer