Linq To Sql Left outer join - filtering null results

Posted by Harry on Stack Overflow See other posts from Stack Overflow or by Harry
Published on 2010-03-28T05:28:52Z Indexed on 2010/03/28 5:33 UTC
Read the original article Hit count: 233

Filed under:
|
|

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!

© Stack Overflow or respective owner

Related posts about linq-to-sql

Related posts about inner-join