c# - 转换到 Dto 时如何动态构建 'where' 子句？

Question

使用 EF 将联接结果投影到 DTO 时，如何动态构建“where”子句？在 NHibernate 中，做到这一点真的很容易，但我无法弄清楚如何使用 Entity Framework 做到这一点。澄清一下，我不想过滤对象，我想要构建 sql。

这就是我想要做的。

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

您可以提前应用 Invoice 过滤器并在 LINQ 查询中使用生成的 IQueryable:

var invoices = db.Invoices.AsQueryable();
BuildWhereClause(filter, ref invoices);

var query = (from i in invoices
             join cj in db.CustomerJobs on i.CustomerJobID equals cj.ID
             ...