EntityFramework嵌套查询的五种方法

这样的双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]