这样的双where的语句应该怎么写呢:
var test=MyList.Where(a => a.Flows.Where(b => b.CurrentUser == “”)
下面我就说说这个问题,想想有几种方法。先来做一下准备工作,我们使用最简单的模型Category和Post
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| public class Category
{
public int Id { get ; set ; }
public string Name { get ; set ; }
public virtual ICollection Posts { get ; set ; }
}
public class Post
{
public int Id { get ; set ; }
public string Author { get ; set ; }
public string Title { get ; set ; }
public int CategoryId { get ; set ; }
public Category Category { get ; set ; }
}
|
把上面的问题转换成这个模型解释就是:查询含有某个Author写的Post的Category。
这个问题如果直接使用SQL来写的话很简单:
1 2 3
| select distinct (c.Id),c. Name from Categories c
inner join Posts p on p.CategoryId=c.Id
where p.Author=N 'cj'
|
下面依次来看EntityFramework的四种实现方法。
第一种,使用Any
1
| var list = ctx.Categories.Where(t => t.Posts.Any(s => s.Author == "cj" ));
|
生成的SQL语句如下:
1 2 3 4 5 6 7 8 9
| SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ Name ] AS [ Name ]
FROM [dbo].[Categories] AS [Extent1]
WHERE EXISTS ( SELECT
1 AS [C1]
FROM [dbo].[Posts] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[CategoryId]) AND (N 'cj' = [Extent2].[Author])
)
|
第二种,使用Select
1
| var list = ctx.Posts.Where(t => t.Author == "cj" ).Select(t => t.Category).Distinct();
|
生成的SQL语句如下:
1 2 3 4 5 6 7 8 9 10
| SELECT
[Distinct1].[Id] AS [Id],
[Distinct1].[ Name ] AS [ Name ]
FROM ( SELECT DISTINCT
[Extent2].[Id] AS [Id],
[Extent2].[ Name ] AS [ Name ]
FROM [dbo].[Posts] AS [Extent1]
INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
WHERE N 'cj' = [Extent1].[Author]
) AS [Distinct1]
|
第三种,使用SelectMany
1 2 3 4 5
| var list = ctx.Categories.SelectMany(t => t.Posts, (category, post) => new
{
category,
post
}).Where(t => t.post.Author == "cj" ).Select(t => t.category).Distinct();
|
生成的SQL语句如下:
1 2 3 4 5 6 7 8 9 10
| SELECT
[Distinct1].[Id] AS [Id],
[Distinct1].[ Name ] AS [ Name ]
FROM ( SELECT DISTINCT
[Extent1].[Id] AS [Id],
[Extent1].[ Name ] AS [ Name ]
FROM [dbo].[Categories] AS [Extent1]
INNER JOIN [dbo].[Posts] AS [Extent2] ON [Extent1].[Id] = [Extent2].[CategoryId]
WHERE N 'cj' = [Extent2].[Author]
) AS [Distinct1]
|
第四种,还是使用SelectMany
1
| var list = ctx.Categories.SelectMany(t => t.Posts).Where(t => t.Author == "cj" ).Select(t => t.Category).Distinct();
|
生成的SQL语句如下:
1 2 3 4 5 6 7 8 9 10
| SELECT
[Distinct1].[Id] AS [Id],
[Distinct1].[ Name ] AS [ Name ]
FROM ( SELECT DISTINCT
[Extent1].[Id] AS [Id],
[Extent1].[ Name ] AS [ Name ]
FROM [dbo].[Categories] AS [Extent1]
INNER JOIN [dbo].[Posts] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[CategoryId]) AND ([Extent2].[CategoryId] = [Extent1].[Id])
WHERE N 'cj' = [Extent2].[Author]
) AS [Distinct1]
|
下面分别来说说这四种方法:
第一种方法Any,更符合我们的查询习惯,也就是文章开始提到的问题的查询风格,只不过里面的Where应该换成Any
第二种方法Select,生成的SQL语句,跟我们自己写的SQL语句是一样的,这种方法以Post为查询主体,好处可以看看SQL语句优化方面的知识。
第三种和第四种都是SelectMany,虽然EF的查询写法不同,但生成的SQL语句完全相同, 当然SelectMany是以Category为查询主体。关于SelectMany的用法请参考MSDN。
Demo下载地址
补充(2014-5-11)
第五种方法,使用Contains
1
| var list = ctx.Categories.Where(t => t.Posts.Select(s => s.Author).Contains( "cj" ));
|
生成的SQL语句如下:
1 2 3 4 5 6 7 8 9
| SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ Name ] AS [ Name ]
FROM [dbo].[Categories] AS [Extent1]
WHERE EXISTS ( SELECT
1 AS [C1]
FROM [dbo].[Posts] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[CategoryId]) AND (N 'cj' = [Extent2].[Author])
)
|
这种方法和第一种方法Any生成的SQL语句是一样一样的。
原标题:EntityFramework嵌套查询的五种方法
关键词: