Left Outer Join and Exists in Linq To SQL C# .NET 3.5

Question

I'm stuck on translating a left outer join from LINQToSQL that returns unique parent rows.

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 Exist somehow in LINQ? Or maybe a groupby of some sort?

Here's the LINQToSQL:

 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;

here's the SQL it produced from profiler

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] < @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

This query returns all mutiples of the 1-many relationship in the results. I found how to do an Exists in LINQ from here. http://www.linq-to-sql.com/linq-to-sql/t-sql-to-linq-upgrade/linq-exists/

Here is the LINQToSQL using Exists:

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;

The generated SQL statement:

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] < @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

I get a SQL timeout from the databind() from using Exists.

Answer 1

You are going to have to use the DefaultIfEmpty extension method. There are a few questions on SO already that show how to do this. Here is a good example:

http://stackoverflow.com/questions/254784/how-can-i-perform-a-nested-join-add-and-group-in-linq

Answer 2

it seems to be returning multiple Project rows

Yes, that's how join works. If a project has 5 matching notes, it show up 5 times.

---

What if the problem is - "Join" is the wrong idiom!

You want to filter the projects to those whose notes contain certain text:

var query = db.Project
  .Where(p => p.Notes.Any(n => n.NoteField.Contains(searchString)));