优化LINQ，而不是创建新的集合/循环

本文关键字：集合 循环 创建 LINQ 优化 | 更新日期: 2023-09-27 18:24:01

我有两个表：

发票（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），但您知道

优化LINQ，而不是创建新的集合/循环

此时您正在加载所有发票，然后为每个发票加载产品。这肯定会很慢（当你开始添加大量发票时，速度会慢很多）。

您应该在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);
}

^^这似乎极大地提高了性能。哦，谢谢你收听

相关文章：