Five methods of EntityFramework nested query: entityframework nested
How should I write such a double where statement:
Var test = MyList. Where (a => a. Flows. Where (B => B. CurrentUser = "")
Next I will talk about this problem. There are several ways to think about it. Let's take a look at the preparations. We use the simplest models: Category and 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 ; } } |
The above problem is converted into this model to explain: query the Category of a Post containing an Author.
If SQL is directly used for writing this problem, it is very simple:
123 |
select distinct (c.Id),c. Name from Categories c inner join Posts p on p.CategoryId=c.Id where p.Author=N 'cj' |
The following describes the four implementation methods of EntityFramework in sequence.
First, use Any
1 |
var list = ctx.Categories.Where(t => t.Posts.Any(s => s.Author == "cj" )); |
The generated SQL statement is as follows:
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]) ) |
Second, Select
1 |
var list = ctx.Posts.Where(t => t.Author == "cj" ).Select(t => t.Category).Distinct(); |
The generated SQL statement is as follows:
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] |
Third, use selectworkflow
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(); |
The generated SQL statement is as follows:
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] |
Fourth, use selectworkflow
1 |
var list = ctx.Categories.SelectMany(t => t.Posts).Where(t => t.Author == "cj" ).Select(t => t.Category).Distinct(); |
The generated SQL statement is as follows:
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] |
These four methods are described as follows:
The first method, Any, is more in line with our query habits, that is, the query style of the question mentioned at the beginning of the article, except that the Where should be replaced with Any
The second method is Select. The generated SQL statement is the same as the SQL statement we write. This method uses Post as the query subject. The benefits of this method are the knowledge of SQL statement optimization.
Both the third and fourth types are SelectMany. Although EF statements are written differently, the generated SQL statements are identical. Of course, selectory is the query subject of Category. For more information about SelectMany usage, see MSDN.
Demo
Supplement () The fifth method, using Contains
1 |
var list = ctx.Categories.Where(t => t.Posts.Select(s => s.Author).Contains( "cj" )); |
The generated SQL statement is as follows:
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]) ) |
This method is the same as the SQL statement generated by the first method Any.