I encounter some problem when using the Predicate Builder class when I also want to retrieve columns of another table (not the one I use to filter with Predicate Builder).

The only way I found is something like this.

1) Build the predicate 2) Apply this predicate on a first query (query1) 3) Create another query (query2) to make a join on the ThirdPartyProviderType table (table where some columns I need to retrieve are stored).

        public IQueryable<ThirdPartyProviderProjection> GetListQuery(long? thirdPartyProviderTypeId)
        {
            LinqMetaData metaData = new LinqMetaData(Adapter);
            var predicate = PredicateBuilder.Null<ThirdPartyProviderEntity>();
            if (thirdPartyProviderTypeId.HasValue)
            {
                predicate = predicate.And(thirdPartyProvider => thirdPartyProvider.FkThirdPartyProviderTypeId == thirdPartyProviderTypeId.Value);
            }

            var query1 = metaData.ThirdPartyProvider
                .Where(predicate)
                .Select(thirdPartyProvider => thirdPartyProvider);

            var query2 = from thirdPartyProvider in query1
                             join thirdPartyProviderType in metaData.ThirdPartyProviderType on thirdPartyProvider.FkThirdPartyProviderTypeId equals thirdPartyProviderType.Id
                             select new ThirdPartyProviderProjection
                             {
                                 Id = thirdPartyProvider.Id,
                                 Name = thirdPartyProvider.Name,
                                 FirstName = thirdPartyProvider.FirstName,
                                 Code = thirdPartyProvider.Code,
                                 IsPhysical = thirdPartyProvider.IsPhysical,
                                 IsActiveRecord = thirdPartyProvider.IsActiveRecord,
                                 ProviderTypeId = thirdPartyProviderType.Id,
                                 ProviderTypeText = thirdPartyProviderType.Text
                             };

            return query2;
        }

Is it possible to write the join clause directly on the query1, something like this :

        public IQueryable<ThirdPartyProviderProjection> GetListQuery(long? thirdPartyProviderTypeId)
        {
            LinqMetaData metaData = new LinqMetaData(Adapter);
            var predicate = PredicateBuilder.Null<ThirdPartyProviderEntity>();
            if (thirdPartyProviderTypeId.HasValue)
            {
                predicate = predicate.And(thirdPartyProvider => thirdPartyProvider.FkThirdPartyProviderTypeId == thirdPartyProviderTypeId.Value);
            }

            var query1 = from thirdPartyProvider in metaData.ThirdPartyProvider
join thirdPartyProviderType in metaData.ThirdPartyProviderType on thirdPartyProvider.FkThirdPartyProviderTypeId equals thirdPartyProviderType.Id
                .Where(predicate)
                .Select(thirdPartyProvider => new ThirdPartyProviderProjection
                             {
                                 Id = thirdPartyProvider.Id,
                                 Name = thirdPartyProvider.Name,
                                 FirstName = thirdPartyProvider.FirstName,
                                 Code = thirdPartyProvider.Code,
                                 IsPhysical = thirdPartyProvider.IsPhysical,
                                 IsActiveRecord = thirdPartyProvider.IsActiveRecord,
                                 ProviderTypeId = thirdPartyProviderType.Id,
                                 ProviderTypeText = thirdPartyProviderType.Text
                             });

            return query1;
        }

I haven't found any example on how to do it on the web.

Thanks in advance

The thing is: whenever you put .Where(fromPredicateBuilder) the current context must be of the type of the PredicateBuilder (in your caseThirdPartyProviderEntity). That's why it works when you write two queries. In the one-query version it won't work because the result of the query is an anonymous type. Also you can't use predicate builder in an in-line linq query, you should use it with the lambda version (.Where(...)).

OK I understand but is it possible to write the join clause using a lambda expression? This is what I'm looking for.

Shouldn't this work?

public IQueryable<ThirdPartyProviderProjection> GetListQuery(long? thirdPartyProviderTypeId)
{
    LinqMetaData metaData = new LinqMetaData(Adapter);
    var query1 = metaData.ThirdPartyProvider
    var predicate = PredicateBuilder.Null<ThirdPartyProviderEntity>();
    if (thirdPartyProviderTypeId.HasValue)
    {
        query1 = query1.Where(predicate.And(thirdPartyProvider => thirdPartyProvider.FkThirdPartyProviderTypeId == thirdPartyProviderTypeId.Value));
    }

    var query2 = from thirdPartyProvider in query1
                 join thirdPartyProviderType in metaData.ThirdPartyProviderType on thirdPartyProvider.FkThirdPartyProviderTypeId equals thirdPartyProviderType.Id
                 select new ThirdPartyProviderProjection
                 {
                     Id = thirdPartyProvider.Id,
                     Name = thirdPartyProvider.Name,
                     FirstName = thirdPartyProvider.FirstName,
                     Code = thirdPartyProvider.Code,
                     IsPhysical = thirdPartyProvider.IsPhysical,
                     IsActiveRecord = thirdPartyProvider.IsActiveRecord,
                     ProviderTypeId = thirdPartyProviderType.Id,
                     ProviderTypeText = thirdPartyProviderType.Text
                 };
    return query2;
}

Just append the .Where to one of the join sides in the join statement. It's been moved to the outer query by the provider for you.

Also, it's unclear what the problem is you're having: crash, compile error, bad SQL, no results etc...

In fact, I just wanted to know if it's possible to merge the query1 and query2 in one and only one query.

It's seems that it's not possible. So, I finally write it like : * build the predicate * create a temporary query only to take into account the predicate * create the final query using the temporary query and adding the join I need

=> So no matter, I finally write something like this :

            LinqMetaData metaData = new LinqMetaData(Adapter);
            var predicate = PredicateBuilder.Null<ThirdPartyProviderEntity>();
            predicate = predicate.And(thirdPartyProvider => thirdPartyProvider.FkThirdPartyTypeId == (long)ThirdPartyTypeEnumeration.Provider);
            predicate = predicate.And(thirdPartyProvider => thirdPartyProvider.FkThirdPartyOwnCompanyId == thirdPartyOwnCompanyId);
            predicate = FilterByRecordStatus(predicate, recordStatusFilter);
            if (thirdPartyProviderTypeId.HasValue)
            {
                predicate = predicate.And(thirdPartyProvider => thirdPartyProvider.FkThirdPartyProviderTypeId == thirdPartyProviderTypeId.Value);
            }

            var query1 = metaData.ThirdPartyProvider
                .Where(predicate)
                .Select(thirdPartyProvider => thirdPartyProvider);

            var query2 =
                from thirdPartyProvider in query1
                join thirdPartyProviderType in metaData.ThirdPartyProviderType on thirdPartyProvider.FkThirdPartyProviderTypeId equals thirdPartyProviderType.Id
                select new ThirdPartyProviderProjection
                {
                    Id = thirdPartyProvider.Id,
                    Name = thirdPartyProvider.Name,
                    FirstName = thirdPartyProvider.FirstName,
                    Code = thirdPartyProvider.Code,
                    IsPhysical = thirdPartyProvider.IsPhysical,
                    IsActiveRecord = thirdPartyProvider.IsActiveRecord,
                    ProviderTypeId = thirdPartyProviderType.Id,
                    ProviderTypeText = thirdPartyProviderType.Text
                };

            return query2;

Thanks for your answer