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

asp.netcleft-joinlinq-to-sqlsql

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.

Best Answer

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)));

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Related Topic