左外连接并存在于 Linq To SQL C# .NET 3.5

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

提问by stevenjmyu

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

我一直在翻译来自 LINQToSQL 的左外连接，该连接返回唯一的父行。

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

我有 2 个表（Project、Project_Notes，它是由 Project_ID 链接的一对多关系）。我正在对 2 表的多列进行关键字搜索，如果 Project_Notes 中的列包含关键字，我只想返回唯一的项目。我有这个 linqtoSQl 序列，但它似乎返回多个 Project 行。也许Exist在 LINQ 中做一个？或者也许是某种分组？

Here's the LINQToSQL:

这是 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

这是它从分析器生成的 SQL

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

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 日期时间,@p2 日期时间,@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 Existsin LINQ from here. http://www.linq-to-sql.com/linq-to-sql/t-sql-to-linq-upgrade/linq-exists/

此查询在结果中返回 1 对多关系的所有多组。我Exists从这里找到了如何在 LINQ 中执行操作。http://www.linq-to-sql.com/linq-to-sql/t-sql-to-linq-upgrade/linq-exists/

Here is the LINQToSQL using Exists:

这是 LINQToSQL 使用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:

生成的SQL语句：

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

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.

我databind()从使用Exists.

采纳答案by Amy B

it seems to be returning multiple Project rows

它似乎正在返回多个 Project 行

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

是的，这就是 join 的工作方式。如果一个项目有 5 个匹配的音符，它会出现 5 次。

---

---

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

回答by casperOne

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:

您将不得不使用 DefaultIfEmpty 扩展方法。已经有一些关于 SO 的问题展示了如何做到这一点。这是一个很好的例子：

How can I perform a nested Join, Add, and Group in LINQ?

如何在 LINQ 中执行嵌套的 Join、Add 和 Group？