Five methods of EntityFramework nested query: entityframework nested

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.