我有两个表:
发票(InvoiceID,InvoiceNumber)
发票_产品(InvoiceID、ProductID、IsFinalized)
我显示了所有发票的列表,并且有一些按钮可以按"已完成"或"未完成"发票进行筛选。最终发票是指上面的每个产品都是IsFinalized==true
的发票。
目前,我有以下代码,执行速度相当慢:
IEnumerable<Invoice> invoices = db.Invoices;
if (isFinalized) // filter by finalized invoices
{
List<Invoice> unfinalizedInvoices = new List<Invoice>();
foreach (var invoice in invoices)
{
int invoicesProductsCountTotal = db.Invoices_Products.Where(l => l.InvoiceID == invoice.InvoiceID).Count();
int invoicesProductsCountFinalized = db.Invoices_Products.Where(l => l.InvoiceID == invoice.InvoiceID && l.IsFinalized == true).Count();
if (invoicesProductsCountTotal != invoicesProductsCountFinalized)
{
unfinalizedInvoices.Add(invoice);
}
}
invoices = invoices.Except(unfinalizedInvoices);
}
else
{
List<Invoice> finalizedInvoices = new List<Invoice>();
foreach (var invoice in invoices)
{
int invoicesProductsCountTotal = db.Invoices_Products.Where(l => l.InvoiceID == invoice.InvoiceID).Count();
int invoicesProductsCountFinalized = db.Invoices_Products.Where(l => l.InvoiceID == invoice.InvoiceID && l.IsFinalized == true).Count();
if (invoicesProductsCountTotal == invoicesProductsCountFinalized && invoicesProductsCountFinalized > 0)
{
finalizedInvoices.Add(invoice);
}
}
invoices = invoices.Except(finalizedInvoices);
}
我意识到这不是最优的,但我喜欢分散我的LINQ,这样我就可以阅读和理解它。
我的问题是:有没有什么方法可以使用.All
或.Any
或其他东西更快地进行查询,或者我需要重新思考我的数据库设计(可能会在发票表中添加一个额外的列)
编辑:第三个表是产品(ProductID,ProductNumber),但您知道
此时您正在加载所有发票,然后为每个发票加载产品。这肯定会很慢(当你开始添加大量发票时,速度会慢很多)。
您应该在EntityFramework中创建多对多关系。(参见示例)
你的课程看起来是这样的:
class Invoice
{
List<Product> Products {get; set;}
}
class Product
{
bool IsFinalized {get; set;}
}
现在,您可以使用LINQ来确保只执行SQL语句,该语句只获取您想要的数据:
var invoices = db.Invoices.Where(i => i.Products.All(p => p.IsFinalized == finalized));
在每个Invoice
上进行迭代,然后向数据库发出额外请求将非常缓慢。让您的查询一次获得所有信息,然后遍历结果。
var result = from invoice in db.Invoices
join invoicedProduct in db.Invoices_Products
on invoice.InvoiceId equals invoicedProduct.InvoiceId
select new
{
InvoiceId = invoice.InvoiceId,
ProductId = invoicedProduct.ProductId,
IsFinalized = invoicedProuct.IsFinalized
};
var grpResult = from record in result
group record by record.ProductId into productGrp
select productGrp;
foreach( var grp in grpResult )
{
Console.WriteLine( "ProductId: " + grp.Key.ToString( ) );
Console.WriteLine( "TotalCount: " + grp.Count( ).ToString( ) );
Console.WriteLine( "Finalized: " + grp.Where( item => item.IsFinalized ).Count( ).ToString( ) );
}
if (isFinalized)
{
invoices = invoices.Where(l => l.Invoices_Products.All(m => m.IsFinalized == true));
}
else
{
List<Invoice> finalizedInvoices = invoices.Where(l => l.Invoices_Products.All(m => m.IsFinalized == true)).ToList();
invoices = invoices.Except(finalizedInvoices);
}
^^这似乎极大地提高了性能。哦,谢谢你收听