How does one build 'where' clause dynamically when projecting to Dto?

Question

When projecting results of the joins onto DTO using EF, how does one dynamically build a "where" clause? In NHibernate it was really easy to do this, but I cannot figure out how to do this with Entity Framework. Just to clarify, I don't want to filter objects, I want sql to be built.

This is what I am trying to do.

private async Task<List<InvoiceInfo>> GetDataWithManualProjection()
{
    var query = (from i in db.Invoices
                 join cj in db.CustomerJobs on i.CustomerJobID equals cj.ID
                 join c in db.Customers on cj.CustomerId equals c.ID
                 let invoice = i
                 let customer = c
                 let customerJob = cj
                 select new InvoiceInfo()
                {
                    Number = invoice.InvoiceNumber,
                    Balance = invoice.InvoiceBalance,
                    PrePay = customer.PrepaymentBalance
                })
                .AsQueryable();

                BuildWhereClause(filter, query);

               return await q.ToListAsync();
}



private void BuildWhereClause(InvoicesFilter filter, ref IQueryable<Invoice> q)
{


    if (string.IsNullOrWhiteSpace(filter.ClientName) == false)
    {
        switch (filter.ClientNameFilterOptions)
        {
            case ClientNameFilterOptions.Contains:
                q = q.Where(x => x.ClientName.Contains(filter.ClientName));
                break;
            case ClientNameFilterOptions.EqualTo:
                q = q.Where(x => x.ClientName == filter.ClientName);
                break;
            case ClientNameFilterOptions.StartsWith:
                q = q.Where(x => x.ClientName.StartsWith(filter.ClientName));
                break;
        }
    }

    if (filter.InvoiceBalance.HasValue)
    {
        var invoiceBalance = filter.InvoiceBalance.Value;
        switch (filter.InvoiceBalanceComparisonOperator)
        {
            case ComparisonOperator.EqualTo:
                q = q.Where(x => x.InvoiceBalance == invoiceBalance);
                break;
            case ComparisonOperator.GreaterThan:
                q = q.Where(x => x.InvoiceBalance > invoiceBalance);
                break;
            case ComparisonOperator.GreatherThanOrEqualTo:
                q = q.Where(x => x.InvoiceBalance >= invoiceBalance);
                break;
            case ComparisonOperator.LessThan:
                q = q.Where(x => x.InvoiceBalance < invoiceBalance);
                break;
            case ComparisonOperator.LessThanOrEqualTo:
                q = q.Where(x => x.InvoiceBalance <= invoiceBalance);
                break;
        }
    }

    if (string.IsNullOrWhiteSpace(filter.Address) == false)
    {
        q = q.Where(x => x.CustomerJob.BillingAddress1 == filter.Address);
    }

    if (string.IsNullOrWhiteSpace(filter.City) == false)
    {
        q = q.Where(x => x.CustomerJob.BillingCity == filter.City);
    }

    if (string.IsNullOrWhiteSpace(filter.ZipCode) == false)
    {
        q = q.Where(x => x.CustomerJob.BillingZip == filter.ZipCode);
    }

    if (filter.InvoiceFrequency != InvoiceFrequency.NotSet)
    {
        q = q.Where(x => x.InvoiceFrequency == filter.InvoiceFrequency);
    }

    if (filter.InvoiceStatus != InvoiceStatus.NotSet)
    {
        q = q.Where(x => x.Status == filter.InvoiceStatus);
    }

    if (filter.StartDate.HasValue)
    {
        q = q.Where(x => x.InvoiceStartDate >= filter.StartDate.Value);
    }

    if (filter.EndDate.HasValue)
    {
        q = q.Where(x => x.InvoiceEndDate <= filter.EndDate.Value);
    }

    if (filter.PaymentMethod != MethodOfPayment.NotSet)
    {
        q = q.Where(x => x.MethodOfPayment == filter.PaymentMethod);
    }

    if (filter.InvoiceNumbers.Any())
    {
        q = q.Where(x => filter.InvoiceNumbers.Contains(x.InvoiceNumber));
    }

    if (string.IsNullOrWhiteSpace(filter.LineItemDescription) == false)
    {
        q = q.Where(x => x.LineItems.Any(l => l.InvoiceDescription.Contains(filter.LineItemDescription)));
    }

    if (filter.HasCCOnFile)
    {
        throw new NotImplementedException();
    }
}

Answer 1

Your private method needs to have the following syntax:

 private  IQueryable<InvoiceInfo> BuildWhereClause(Expression<Func<InvoiceInfo,bool>> filter,
                               IQueryable<InvoiceInfo> q)
 {
    return q.Where(filter);
 }

And you don't need to call AsQueryable method at the end of your query

Update

If you want to apply the filter to your entity and not to the projection, then you need to compose your query in several steps, eg:

IQueryable<Invoice> query= db.Invoices;
BuildWhereClause(filter,ref query);
var result= (from i in query
             join cj in db.CustomerJobs on i.CustomerJobID equals cj.ID
             join c in db.Customers on cj.CustomerId equals c.ID
             select new InvoiceInfo()
            {
                Number = i.InvoiceNumber,
                Balance = i.InvoiceBalance,
                PrePay = c.PrepaymentBalance
            });

Also, there is not need to use let clause in your query, every time you are using it, an extra projection will be applied.