在投影到 Dto 时，如何动态构建'where'子句？

Question

使用 EF 将连接的结果投影到 DTO 上时，如何动态构建"where"子句？在NHibernate中，做到这一点真的很容易，但是我不知道如何使用实体框架做到这一点。 澄清一下，我不想过滤对象，我希望构建 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
...

Answer 2

私有方法需要具有以下语法：

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

而且您无需在查询结束时调用AsQueryable方法

更新

如果要将筛选器应用于实体而不是投影，则需要通过几个步骤编写查询，例如：

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

此外，无需在查询中使用let子句，每次使用它时，都会应用额外的投影。