这样的双where的语句应该怎么写呢：
var test=MyList.Where(a => a.Flows.Where(b => b.CurrentUser == “”)
下面我就说说这个问题，想想有几种方法。先来做一下准备工作，我们使用最简单的模型Category和Post
| 123456789101112131415161718192021 | 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来写的话很简单：
| 123 | select distinct(c.Id),c.Name from Categories cinner join Posts p on p.CategoryId=c.Idwhere p.Author=N'cj' |
下面依次来看EntityFramework的四种实现方法。
第一种，使用Any
| 1 | var list = ctx.Categories.Where(t => t.Posts.Any(s => s.Author == "cj")); |
生成的SQL语句如下：
| 123456789 | 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语句如下：
| 12345678910 | 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
| 12345 | 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语句如下：
| 12345678910 | 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语句如下：
| 12345678910 | 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。
补充（2014-5-11）
第五种方法，使用Contains
| 1 | var list = ctx.Categories.Where(t => t.Posts.Select(s => s.Author).Contains("cj")); |
生成的SQL语句如下：
| 123456789 | 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语句是一样一样的。
