I have realized that SQL Server is unable efficiently process some basic SQL queries, such as:
SELECT TOP (1) [t0].[Id], [t0].[L1], [t0].[L2], [t0].[Value]
FROM [Foos] AS [t0]
INNER JOIN [Lookup1] AS [t1] ON [t1].[Id] = [t0].[L2]
INNER JOIN [Lookup2] AS [t2] ON [t2].[Id] = [t0].[L1]
WHERE ([t1].[Name] = 'a') AND ([t2].[Name] = 'b')
ORDER BY [t0].[Value]
which is generated from LINQ expression:
// query 1
Foos
.Where(f => f.Lookup1.Name == "a" && f.Lookup2.Name == "b")
.OrderBy(f => f.Value)
.Take(1)
The schema definition is in question 'Index over multiple lookup tables in SQL Server'. @Hoots in the answer shows that the SQL query must look like:
SELECT TOP (1) [t0].[Id], [t0].[L1], [t0].[L2], [t0].[Value]
FROM [Foos] AS [t0]
CROSS JOIN (
SELECT TOP (1) [t1].[Id], [t2].[Id] AS [Id2]
FROM [Lookup1] AS [t1], [Lookup2] AS [t2]
WHERE ([t1].[Name] = 'a') AND ([t2].[Name] = 'b')
) AS [t3]
WHERE ([t0].[L1] = [t3].[Id]) AND ([t0].[L2] = [t3].[Id2])
ORDER BY [t0].[Value] DESC
which could be generated from the following LINQ expression:
// query 2
(from f in Foos
from l in (
from l1 in Lookup1s
from l2 in Lookup2s
where l1.Name == "a"
&& l2.Name == "b"
select new { L1 = l1.Id, L2 = l2.Id }).Take(1)
where f.L1 == l.L1 && f.L2 == l.L2
orderby f.Value descending
select f).Take(1)
My question is how to automatically rewrite the query 1 into query 2? So I could compose queries in multiple steps:
void Do()
{
var x = ListFoos("a", "b").OrderBy(f => f.Value).Take(2);
// ...
}
IQueryable<Foos> ListFoos(string l1, string l2)
{
var foos = Foos.AsQueryable();
if (l1 != null)
foos = foos.Where(f => f.Lookup1.Name == l1);
if (l2 != null)
foos = foos.Where(f => f.Lookup2.Name == l2);
return foos;
}
Has someone done that already? Is there a library simplifying the task?
Clarification:
The resulting expression of IQueryable<> is translated into SQL statement which SQL Server is unable efficiently evaluate. So I need to transform the expression into an expression which is translated into better SQL statement for SQL Server.
I think that I am not the first who has encountered this issue. LINQ is a longer time here and the SQL statements are pretty basic, so other developers might already have been solving this problem with SQL Server.
