在Linq To SQL C#.NET 3.5中保留左外连接和存在

我坚持从LINQToSQL转换左外连接,返回唯一的父行.

我有2个表(Project,Project_Notes,它是由Project_ID链接的1-many关系).我在2表上的多个列上进行关键字搜索,如果Project_Notes中的列包含关键字,我只想返回唯一项目.我有这个linqtoSQl序列,但它似乎返回多个Project行.也许Exist在LINQ 做某种事？或者也许是某种类型的群体？

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

这是它从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].[标题],[t0].[状态],[t0].[PROJECT_ID],[t0].[PROVIDER_ID],[t0].[CATEGORY_ID],[t0].[城市],[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]为空))AS [t2] WHERE [t2].[ROW_NUMBER]在@ p4 + 1和@ 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

此查询返回结果中1-many关系的所有多个.我Exists从这里找到了如何在LINQ中做一个.http://www.linq-to-sql.com/linq-to-sql/t-sql-to-linq-upgrade/linq-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;

生成的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

我databind()从使用中获得了SQL超时Exists.

它似乎返回多个Project行

是的,这就是加入的方式.如果一个项目有5个匹配的笔记,它会显示5次.

如果问题是 - "加入"是错误的习惯用法!

您希望将项目过滤到其注释包含特定文本的项目:

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