Five methods of EntityFramework nested query: entityframework nested

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 cinner join Posts p on p.CategoryId=c.Idwhere 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]    WHEREEXISTS (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]    WHEREEXISTS (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.