Linq To Sql Left outer join - filtering null results
- by Harry
I'd like to reproduce the following SQL into C# LinqToSql
SELECT TOP(10) Keywords.*
FROM Keywords LEFT OUTER JOIN
IgnoreWords ON Keywords.WordID = IgnoreWords.ID
WHERE (DomainID = 16673) AND (IgnoreWords.Name IS NULL)
ORDER BY [Score] DESC
The following C# Linq gives the right answer.
But I can't help think I'm missing something (a better way of doing it?)
var query = (from keyword in context.Keywords
join ignore in context.IgnoreWords
on keyword.WordID equals ignore.ID into ignored
from i in ignored.DefaultIfEmpty()
where i == null
where keyword.DomainID == ID
orderby keyword.Score descending
select keyword).Take(10);
the SQL produced looks something like this:
SELECT TOP (10) [t0].[DomainID], [t0].[WordID], [t0].[Score], [t0].[Count]
FROM [dbo].[Keywords] AS [t0]
LEFT OUTER JOIN
(SELECT 1 AS [test], [t1].[ID] FROM [dbo].[IgnoreWords] AS [t1]) AS [t2] ON [t0].[WordID] = [t2].[ID]
WHERE ([t0].[DomainID] = 16673) AND ([t2].[test] IS NULL)
ORDER BY [t0].[Score] DESC
How can I get rid of this redundant inner selection?
It's only slightly more expensive but every bit helps!
