c# - 在 LINQ 中将 OR 转换为并集

Question

LINQ 查询中的 OR 子句导致性能问题。如果我将其更改为 Union 性能会提高很多。这是当前查询。如何(includeBatch && i.BatchId != null || includeSingle && i.BatchId == null)将这部分查询转换为联合。

原始调用：

var inputs = _inputRepository.GetProcessorQueue(includeBatch, includeSingle, companyIds);

当前解决方案：进行两次调用并进行联合操作

if (includeSingle && includeBatch)
{
var inputsSingle = _inputRepository.GetProcessorQueue(false, includeSingle, companyIds);
var inputsBatch = _inputRepository.GetProcessorQueue(includeBatch, false, companyIds);  

return inputsSingle.Union(inputsBatch).ToList();
}
else
{
var inputs = _inputRepository.GetProcessorQueue(includeBatch, includeSingle, companyIds);
return inputs.Select(Input.ToQueueInputExpression(inputQueueMetaDataTypes)).ToList();
}

原始 LINQ：

public IQueryable<Input> GetProcessorQueue(bool includeBatch, bool includeSingle, int[] companyIds)
        {
            return FindAsQueryableReadOnly(i => !i.IsDeleted
                && i.InputTypeId == (int)InputType.Values.TRANSPORTATION
                && i.ExportedStatusId != (int)ExportedStatus.Values.EXPORTED
                && ImportStatus.DataEntryQueue.Contains(i.ImportStatusId) 
                && ((i.TransportationInput.TransportationInputTypeId == (int)TransportationInputType.Values.FACTOR
                        && (includeBatch && i.BatchId != null || includeSingle && i.BatchId == null)
                        && !i.DocumentOnlyStatus)
                    || (includeBatch && i.BatchId != null && i.DocumentOnlyStatus && i.InputDocumentStats.DocumentId != null)
                )
                && (companyIds == null || !companyIds.Any() || companyIds.Contains(i.CompanyId)),
                i => i.Batch,
                i => i.ImportStatus,
                i => i.InputCharges,
                i => i.InputDocumentStats,
                i => i.InputDocumentStats.DocumentOrigin,
                i => i.InputDocumentStats.Document.DocumentMetaData);
        }

原始 SQL：（为了可读性删除了 select 语句）

FROM [eroom].[Input] AS [i]
INNER JOIN [eroom].[ImportStatus] AS [i.ImportStatus] ON [i].[ImportStatusId] = [i.ImportStatus].[ImportStatusId]
LEFT JOIN [eroom].[Batch] AS [i.Batch] ON [i].[BatchId] = [i.Batch].[BatchId]
LEFT JOIN [eroom].[InputDocumentStats] AS [i.InputDocumentStats] ON [i].[InputId] = [i.InputDocumentStats].[InputId]
LEFT JOIN [eroom].[DocumentOrigin] AS [i.InputDocumentStats.DocumentOrigin] ON [i.InputDocumentStats].[DocumentOriginId] = [i.InputDocumentStats.DocumentOrigin].[DocumentOriginId]
LEFT JOIN [eroom].[Document] AS [i.InputDocumentStats.Document] ON [i.InputDocumentStats].[DocumentId] = [i.InputDocumentStats.Document].[DocumentId]
LEFT JOIN [eroom].[TransportationInput] AS [i.TransportationInput] ON [i].[InputId] = [i.TransportationInput].[InputId]
WHERE ((((([i].[HiddenStatus] = 0) AND ([i].[InputTypeId] = 1)) AND ([i].[ExportedStatusId] <> 3)) AND [i].[ImportStatusId] IN (1, 4, 5, 7)) AND (((([i.TransportationInput].[TransportationInputTypeId] = 1) AND ([i].[BatchId] IS NOT NULL OR [i].[BatchId] IS NULL)) AND ([i].[DocumentOnlyStatus] = 0)) OR (([i].[BatchId] IS NOT NULL AND ([i].[DocumentOnlyStatus] = 1)) AND [i.InputDocumentStats].[DocumentId] IS NOT NULL))) AND [i].[CompanyId] IN (1, 2, 3)

更好的 SQL 性能（删除了 select 语句以提高可读性）

FROM [eroom].[Input] AS [i]
INNER JOIN [eroom].[ImportStatus] AS [i.ImportStatus] ON [i].[ImportStatusId] = [i.ImportStatus].[ImportStatusId]
LEFT JOIN [eroom].[Batch] AS [i.Batch] ON [i].[BatchId] = [i.Batch].[BatchId]
LEFT JOIN [eroom].[InputDocumentStats] AS [i.InputDocumentStats] ON [i].[InputId] = [i.InputDocumentStats].[InputId]
LEFT JOIN [eroom].[DocumentOrigin] AS [i.InputDocumentStats.DocumentOrigin] ON [i.InputDocumentStats].[DocumentOriginId] = [i.InputDocumentStats.DocumentOrigin].[DocumentOriginId]
LEFT JOIN [eroom].[Document] AS [i.InputDocumentStats.Document] ON [i.InputDocumentStats].[DocumentId] = [i.InputDocumentStats.Document].[DocumentId]
LEFT JOIN [eroom].[TransportationInput] AS [i.TransportationInput] ON [i].[InputId] = [i.TransportationInput].[InputId]
WHERE ((((([i].[HiddenStatus] = 0) AND ([i].[InputTypeId] = 1)) AND ([i].[ExportedStatusId] <> 3)) AND [i].[ImportStatusId] IN (1, 4, 5, 7)) AND (((([i.TransportationInput].[TransportationInputTypeId] = 1) AND [i].[BatchId] IS NOT NULL) AND ([i].[DocumentOnlyStatus] = 0)) OR (([i].[BatchId] IS NOT NULL AND ([i].[DocumentOnlyStatus] = 1)) AND [i.InputDocumentStats].[DocumentId] IS NOT NULL))) AND [i].[CompanyId] IN (1, 2, 3)


UNION

FROM [eroom].[Input] AS [i]
INNER JOIN [eroom].[ImportStatus] AS [i.ImportStatus] ON [i].[ImportStatusId] = [i.ImportStatus].[ImportStatusId]
LEFT JOIN [eroom].[Batch] AS [i.Batch] ON [i].[BatchId] = [i.Batch].[BatchId]
LEFT JOIN [eroom].[InputDocumentStats] AS [i.InputDocumentStats] ON [i].[InputId] = [i.InputDocumentStats].[InputId]
LEFT JOIN [eroom].[DocumentOrigin] AS [i.InputDocumentStats.DocumentOrigin] ON [i.InputDocumentStats].[DocumentOriginId] = [i.InputDocumentStats.DocumentOrigin].[DocumentOriginId]
LEFT JOIN [eroom].[Document] AS [i.InputDocumentStats.Document] ON [i.InputDocumentStats].[DocumentId] = [i.InputDocumentStats.Document].[DocumentId]
LEFT JOIN [eroom].[TransportationInput] AS [i.TransportationInput] ON [i].[InputId] = [i.TransportationInput].[InputId]
WHERE ((((([i].[HiddenStatus] = 0) AND ([i].[InputTypeId] = 1)) AND ([i].[ExportedStatusId] <> 3)) AND [i].[ImportStatusId] IN (1, 4, 5, 7)) AND ((([i.TransportationInput].[TransportationInputTypeId] = 1) AND [i].[BatchId] IS NULL) AND ([i].[DocumentOnlyStatus] = 0))) AND [i].[CompanyId] IN (1, 2, 3)

 public IQueryable<T> FindAsQueryableReadOnly(Expression<Func<T, bool>> where, params Expression<Func<T, object>>[] includeProperties)
        {
            IQueryable<T> query = _dbSet.AsQueryable();
            query = IncludeProperties(includeProperties, query);
            return query.Where(where).AsNoTracking();
        }

标签： c#sqlsql-serverlinqlambda