c# EF Core Or修改为Union优化，速度大幅度提升！

最近在优化代码时发现linq 采用了||查询导致查询效率非常慢，查询语句出现扫表情况，

采用了Union大大提升了效率，解决了问题

优化c# 前的linq:

dateTime = dateTime.Date;
            DateTime BegTime = dateTime;
            DateTime EndTime = dateTime.AddDays(1);
            var v = from q in _MyDbContext.Document
                    where q.Customer.AgentId == agentId && (!q.UserRefundId.HasValue || q.UserRefund.RefundStatus != RefundStatusType.SUCCESS)
                    && ((q.OutputTime >= BegTime && q.OutputTime <= EndTime && q.DocumentStatus == DocumentStatus.OutputComplete)
                    || (q.Order.Status == OrderStatusType.SUCCESS && q.Order.SuccessTime >= BegTime && q.Order.SuccessTime <= EndTime))
                    select new PrinterStatisticsDto
                    {
                        PrinterId = q.PrinterId,
                        PrinterName = q.PrinterName,
                        CustomerId = q.CustomerId.Value,
                        CustomerName = q.Customer.Name,
                        JobType = q.JobType,
                        SimpleDuplexType = q.SimpleDuplexType == SimpleDuplexType.Simple ? SimpleDuplexType.Simple : SimpleDuplexType.DuplexLong,
                        PageSize = q.PageSize,
                        ColorType = q.ColorType,
                        PageCount = q.CalculationCount > 0 ? q.CalculationCount : q.PageCount,
                        Copies = q.Copies,
                        GivePageCount = q.GivePageCount,
                        TotalCost = (q.OrderId.HasValue && q.Order.Status == OrderStatusType.SUCCESS) ? q.TotalCost : 0
                    };
            var s = from item in v
                    group item by new
                    {
                        item.PrinterId,
                        item.PrinterName,
                        item.CustomerId,
                        item.CustomerName,
                        item.JobType,
                        item.PageSize,
                        item.ColorType,
                        item.SimpleDuplexType
                    } into gr
                    select new StatisticsDto
                    {
                        Balance = gr.Sum(a => a.TotalCost.Value),
                        ColorType = gr.Key.ColorType,
                        Count = gr.Sum(a => a.PageCount * a.Copies),
                        JobType = gr.Key.JobType,
                        SimpleDuplexType = gr.Key.SimpleDuplexType,
                        PageSize = gr.Key.PageSize,
                        PrinterName = gr.Key.PrinterName,
                        PrinterId = gr.Key.PrinterId,
                        CustomerId = gr.Key.CustomerId,
                        CustomerName = gr.Key.CustomerName,
                        GivePageCount = gr.Sum(a => a.GivePageCount),
                    };
            return s.ToList();

这样的代码生成的sql 语句如下：

SELECT SUM(CASE
    WHEN `d`.`OrderId` IS NOT NULL AND (`o`.`Status` = 1) THEN `d`.`TotalCost`
    ELSE 0
END) AS `Balance`, `d`.`ColorType`, SUM(CASE
    WHEN `d`.`CalculationCount` > 0 THEN `d`.`CalculationCount`
    ELSE `d`.`PageCount`
END * `d`.`Copies`) AS `Count`, `d`.`JobType`, CASE
    WHEN `d`.`SimpleDuplexType` = 1 THEN 1
    ELSE 2
END AS `SimpleDuplexType`, `d`.`PageSize`, `d`.`PrinterName`, `d`.`PrinterId`, `d`.`CustomerId`, COUNT(*) AS `OrderNumber`, SUM(`d`.`GivePageCount`) AS `GivePageCount`, timestamp('2022-08-04 00:00:00') AS `DateTime`
FROM `Document` AS `d`
LEFT JOIN `UserRefund` AS `u` ON `d`.`UserRefundId` = `u`.`Id`
LEFT JOIN `Order` AS `o` ON `d`.`OrderId` = `o`.`Id`
LEFT JOIN `Customer` AS `c` ON `d`.`CustomerId` = `c`.`Id`
WHERE ((`d`.`CustomerId` = 100076) AND (`d`.`UserRefundId` IS NULL OR ((`u`.`RefundStatus` <> 3) OR `u`.`RefundStatus` IS NULL))) AND ((((`d`.`OutputTime` >= timestamp('2022-08-04 00:00:00')) AND (`d`.`OutputTime` <= timestamp('2022-08-05 00:00:00'))) AND (`d`.`DocumentStatus` = 9)) OR (((`o`.`Status` = 1) AND (`o`.`CreateTime` >= timestamp('2022-08-04 00:00:00'))) AND (`o`.`CreateTime` <= timestamp('2022-08-05 00:00:00'))))
GROUP BY `d`.`PrinterId`, `d`.`PrinterName`, `d`.`CustomerId`, `c`.`Name`, `d`.`JobType`, `d`.`PageSize`, `d`.`ColorType`, CASE
    WHEN `d`.`SimpleDuplexType` = 1 THEN 1
    ELSE 2
END
ORDER BY `d`.`PrinterId` DESC, `d`.`JobType`
LIMIT 100 OFFSET 0

通过优化linq后代码如下,这样效率大大提升，没有出现扫表情况：

dateTime = dateTime.Date;
            DateTime BegTime = dateTime;
            DateTime EndTime = dateTime.AddDays(1);
            var v = from q in _MyDbContext.Document
                    where q.CustomerId == customerId
                    && (!q.UserRefundId.HasValue || q.UserRefund.RefundStatus != RefundStatusType.SUCCESS)
                    && (q.OutputTime >= BegTime && q.OutputTime <= EndTime && q.DocumentStatus == DocumentStatus.OutputComplete)
                    select new PrinterStatisticsDto
                    {
                        PrinterId = q.PrinterId,
                        PrinterName = q.PrinterName,
                        CustomerId = q.CustomerId.Value,
                        CustomerName = q.Customer.Name,
                        JobType = q.JobType,
                        SimpleDuplexType = q.SimpleDuplexType == SimpleDuplexType.Simple ? SimpleDuplexType.Simple : SimpleDuplexType.DuplexLong,
                        PageSize = q.PageSize,
                        ColorType = q.ColorType,
                        PageCount = q.CalculationCount > 0 ? q.CalculationCount : q.PageCount,
                        Copies = q.Copies,
                        GivePageCount = q.GivePageCount,
                        TotalCost = (q.OrderId.HasValue && q.Order.Status == OrderStatusType.SUCCESS) ? q.TotalCost : 0
                    };
            var v1 = from q in _MyDbContext.Document
                     where q.CustomerId == customerId
                     && (!q.UserRefundId.HasValue || q.UserRefund.RefundStatus != RefundStatusType.SUCCESS)
                     && q.Order.Status == OrderStatusType.SUCCESS && q.Order.SuccessTime >= BegTime && q.Order.SuccessTime <= EndTime
                     select new PrinterStatisticsDto
                     {
                         PrinterId = q.PrinterId,
                         PrinterName = q.PrinterName,
                         CustomerId = q.CustomerId.Value,
                         CustomerName = q.Customer.Name,
                         JobType = q.JobType,
                         SimpleDuplexType = q.SimpleDuplexType == SimpleDuplexType.Simple ? SimpleDuplexType.Simple : SimpleDuplexType.DuplexLong,
                         PageSize = q.PageSize,
                         ColorType = q.ColorType,
                         PageCount = q.CalculationCount > 0 ? q.CalculationCount : q.PageCount,
                         Copies = q.Copies,
                         GivePageCount = q.GivePageCount,
                         TotalCost = (q.OrderId.HasValue && q.Order.Status == OrderStatusType.SUCCESS) ? q.TotalCost : 0
                     };
            v = v.Union(v1);
            var s = from item in v
                    group item by new
                    {
                        item.PrinterId,
                        item.PrinterName,
                        item.CustomerId,
                        item.CustomerName,
                        item.JobType,
                        item.PageSize,
                        item.ColorType,
                        item.SimpleDuplexType
                    } into gr
                    select new PrinterStatistics
                    {
                        Balance = gr.Sum(a => a.TotalCost.Value),
                        ColorType = gr.Key.ColorType,
                        Count = gr.Sum(a => a.PageCount * a.Copies),
                        JobType = gr.Key.JobType,
                        SimpleDuplexType = gr.Key.SimpleDuplexType,
                        PageSize = gr.Key.PageSize,
                        PrinterName = gr.Key.PrinterName,
                        PrinterId = gr.Key.PrinterId,
                        CustomerId = gr.Key.CustomerId,
                        GivePageCount = gr.Sum(a => a.GivePageCount),
                        OrderNumber = gr.Count(),
                        DateTime = dateTime,
                    };

            return s.ToList();