title: Left Outer Join and Exists in Linq To SQL C# .NET 3.5
body: <p>I'm stuck on translating a left outer join from LINQToSQL that returns unique parent rows.</p>
  
  <p>I have 2 tables (Project, Project_Notes, and it's a 1-many relationship linked by Project_ID). I am doing a keyword search on multiple columns on the 2 table and I only want to return the unique projects if a column in Project_Notes contains a keyword. I have this linqtoSQl sequence going but it seems to be returning multiple Project rows. Maybe do an <code>Exist</code> somehow in LINQ? Or maybe a groupby of some sort?</p>
  
  <p>Here's the LINQToSQL: </p>
  
  <pre><code> query = from p in query
   join n in notes on p.PROJECT_ID equals n.PROJECT_ID into projectnotes
   from n in notes.DefaultIfEmpty()
   where n.NOTES.Contains(cwForm.search1Form)
   select p;
  </code></pre>
  
  <p>here's the SQL it produced from profiler </p>
  
  <blockquote>
    <p>exec sp_executesql N'SELECT [t2].[Title], [t2].[State], [t2].[PROJECT_ID],
    [t2].[PROVIDER_ID], [t2].[CATEGORY_ID], [t2].[City], [t2].[UploadedDate], 
    [t2].[SubmittedDate], [t2].[Project_Type]FROM ( SELECT ROW_NUMBER() OVER (ORDER BY 
    [t0].[UploadedDate]) AS [ROW_NUMBER], [t0].[Title], [t0].[State], [t0].[PROJECT_ID], 
    [t0].[PROVIDER_ID], [t0].[CATEGORY_ID], [t0].[City], [t0].[UploadedDate], 
    [t0].[SubmittedDate], [t0].[Project_Type] FROM [dbo].[PROJECTS] AS [t0] LEFT OUTER JOIN 
    [dbo].[PROJECT_NOTES] AS [t1] ON 1=1 WHERE ([t1].[NOTES] LIKE @p0) AND 
    ([t0].SubmittedDate] >= @p1) AND ([t0].[SubmittedDate] &lt; @p2) AND ([t0].[PROVIDER_ID] = 
    @p3) AND ([t0].[CATEGORY_ID] IS NULL)) AS [t2] WHERE [t2].[ROW_NUMBER] BETWEEN @p4 + 1 
    AND @p4 + @p5 ORDER BY [t2].[ROW_NUMBER]',N'@p0 varchar(9),@p1 datetime,@p2 datetime,@p3 
    int,@p4 int,@p5 int',@p0='%chicago%',@p1=''2000-09-02 00:00:00:000'',@p2=''2009-03-02 
    00:00:00:000'',@p3=1000,@p4=373620,@p5=20</p>
  </blockquote>
  
  <p>This query returns all mutiples of the 1-many relationship in the results. I found how to do an <code>Exists</code> in LINQ from here. <a href="http://www.linq-to-sql.com/linq-to-sql/t-sql-to-linq-upgrade/linq-exists/" rel="nofollow noreferrer">http://www.linq-to-sql.com/linq-to-sql/t-sql-to-linq-upgrade/linq-exists/</a></p>
  
  <p>Here is the LINQToSQL using <code>Exists</code>:</p>
  
  <pre><code>query = from p in query
  where (from n in notes
  where n.NOTES.Contains(cwForm.search1Form)
  select n.PROJECT_ID).Contains(p.PROJECT_ID)
  select p;
  </code></pre>
  
  <p>The generated SQL statement:</p>
  
  <blockquote>
    <p>exec sp_executesql N'SELECT COUNT(*) AS [value] FROM [dbo].[PROJECTS] AS [t0] WHERE 
    (EXISTS(SELECT NULL AS [EMPTY] FROM [dbo].[PROJECT_NOTES] AS [t1] WHERE 
    ([t1].PROJECT_ID] = ([t0].[PROJECT_ID])) AND ([t1].[NOTES] LIKE @p0))) AND 
    ([t0].[SubmittedDate] >= @p1) AND ([t0].[SubmittedDate] &lt; @p2) AND ([t0].[PROVIDER_ID] = 
    @p3) AND ([t0].[CATEGORY_ID] IS NULL)',N'@p0 varchar(9),@p1 datetime,@p2 datetime,@p3 
    int',@p0='%chicago%',@p1=''2000-09-02 00:00:00:000'',@p2=''2009-03-02 
    00:00:00:000'',@p3=1000</p>
  </blockquote>
  
  <p>I get a SQL timeout from the <code>databind()</code> from using <code>Exists</code>.</p>