How to Optimally Combine LINQ-to-SQL Queries
I solved my problem, but because I tried and could not solve it before and it took me a lot of effort this time, I wanted to post this question and if anyone has no better answer, so I don't forget like this to do in the future, and help someone else face a similar problem. My task is as follows:
I have a function used to filter a parameter list to only return those that are traced in a traceback, and when applicable, also only show items that match the specified pattern:
Private Shared Function FilterResultsLot(ByVal source As IQueryable(Of Item), _
ByVal itemCode As String) As IQueryable(Of Item)
If HasFilter(itemCode, True) Then
Return From row In source Where row.ItemDetail.IsLotTraced AndAlso _
row.ItemCode.ToLower() Like itemCode.ToLower()
Else
Return From row In source Where row.ItemDetail.IsLotTraced
End If
End Function
There was another requirement and I would like to generalize the tracing criteria to a generic filter, so I created this function:
Private Shared Function FilterResults(source As IQueryable(Of Item), _
itemCode As String, filter As Func(Of Item, Boolean)) As IQueryable(Of Item)
Dim predicate As Func(Of Item, Boolean)
If HasFilter(itemCode, True) Then
predicate = Function(row) row.ItemCode.ToLower() Like itemCode.ToLower() AndAlso filter.Invoke(row)
Else
predicate = filter
End If
Return source.Where(predicate).AsQueryable()
End Function
And this feature works through LINQ-to-SQL, but loses significant optimization. Whereas the first function will execute an SQL query ending with:
FROM [dbo].[OITM] AS [t0]
LEFT OUTER JOIN [dbo].[FSE_ItemDetail] AS [t1] ON [t1].[ItemCode] = [t0].[ItemCode]
WHERE ([t1].[IsLotTraced] = 1) AND (LOWER([t0].[ItemCode]) LIKE @p0 ESCAPE ''~'')
The second will execute several SQL queries with these endings (and maybe many more):
FROM [dbo].[OITM] AS [t0]
FROM [dbo].[FSE_ItemDetail] AS [t0]
WHERE [t0].[ItemCode] = @p0',N'@p0 nvarchar(4000)',@p0=N'BF-BIKE'
FROM [dbo].[FSE_ItemDetail] AS [t0]
WHERE [t0].[ItemCode] = @p0',N'@p0 nvarchar(4000)',@p0=N'BF-BIKE-ITEM'
So, the question is how to combine LINQ-to-SQL delegate expressions into one without calling Invoke or AsQueryable or losing optimal execution.
source to share
Here is my current solution. Please comment or provide better solutions if you have:
Private Shared Function FilterResults(source As IQueryable(Of Item), itemCode As String, filter As Expressions.Expression(Of Func(Of Item, Boolean))) As IQueryable(Of Item)
If HasFilter(itemCode, True) Then
Return Queryable.Where(Queryable.Where(source, Function(row) row.ItemCode.ToLower() Like itemCode.ToLower()), filter)
Else
Return Queryable.Where(source, filter)
End If
End Function
Edit:
After working with it a little more, I prefer a simplification / alternative to this syntax:
Return Queryable.Where(Queryable.Where(source, _
Function(row) row.ItemCode.ToLower() Like itemCode.ToLower()), filter)
My preference:
Return Queryable.Where(From row in source _
Where row.ItemCode.ToUpper() Like itemCode.ToUpper(), filter)
(I also suggest ToUpper instead of ToLower forcing case-insensitive on a case-sensitive server because I heard that UPPER is better optimized for this.)
source to share