How to properly use nested queries in EntityFramework?

Question

I'm playing around EntityFrameworkCore with WebAPI while building voting app exercise.

I want to make the code in async way where is possible.

So should I have to use the nested query in async way somehow(// Problem 1, // Problem 2)?

/* The target of the question - the query*/
var pollResults =
                await _context.Polls
                        .Select(poll => new PollDto
                        {
                            Id = poll.Id,
                            Question = poll.Question,
                            CreatedAt = poll.CreatedAt,
                            Options = poll.Options
                            .Select(option => new OptionDto
                            {
                                Id = option.Id,
                                Value = option.Value,
                                VotesCount = option.Votes.Count() // Problem 1
                            })
                            .ToList(), // Problem 2
                            LastVotedAt = _context.PollVotes.Where(vote=>vote.PollId == poll.Id).Select(vote => vote.VoteDate).SingleOrDefault()
                        })
                        .ToListAsync(); 

/* Domain classes */

public class Poll
    {
        public int Id { get; set; }
        public ICollection<PollOption> Options { get; set; } = new List<PollOption>();
        public ICollection<PollVote> Votes { get; set; } = new List<PollVote>();
    }

public class PollOption
    {
        public int Id { get; set; }
        public string Value { get; set; }
        public int PollId { get; set; }
        public Poll Poll { get; set; }
        public ICollection<PollVote> Votes { get; set; } = new List<PollVote>();
    }

 public class PollVote
    {
        public int Id { get; set; }
        public int PollId { get; set; }
        public Poll Poll { get; set; }
        public int OptionId { get; set; }
        public PollOption Option { get; set; }
        public DateTime VoteDate { get; set; }
    }

/* Dto classes */

public class PollDto
    {
        public int Id { get; set; }
        public string Question { get; set; }
        public ICollection<OptionDto> Options { get; set; } = new List<OptionDto>();
        public DateTime LastVotedAt { get; set; }
    }

public class OptionDto
    {
        public int Id { get; set; }
        public string Value { get; set; }
        public int VotesCount { get; set; }
    }

So in not nested queries Count and SingleOrDefault would make request to the database and it should be executed in async way. But in my case the whole query is a single request.

Should I have to modify something to done the methods Count and SingleOrDefault in async way ? Or calling ToListAsync at end is enough?

I believe the answer is that 1 request to the database goes in 1 async call. But I didn't find any solution in the internet.

In a few hours of playing I realized that: 1. calling Count (from problem 1) telling to EF how to form query to the DB. Thus not fetching all votes but just add VotesCount which works over PollVotes table on DB level; 2. calling ToList (from problem 2) working on received yet data on my application level. So my 2 problems are different kind. Repair me if I wrong. — Alexander Kolarov
– Alexander Kolarov, Commented Feb 20, 2020 at 1:26

Steve Py · Accepted Answer · 2020-02-20 01:19:40Z

ToListAsync() at the end is enough. Expressions inside the query are used by EF to compose the query. They are not "executed" as SQL like they would have been as stand-alone statements against the DbSets.

For instance when I run something similar:

var parents = await context.Parents
    .Select(x => new
    {
        x.ParentId,
        x.Name,
        Children = x.Children.Select(c => new { c.ChildId, c.Name }).ToList(),
        ChildCount = x.Children.Count()
    }).ToListAsync();

in a test and set a breakpoint with a profiler running. The statement produces a single SQL statement:

SELECT 
    [Project2].[ParentId] AS [ParentId], 
    [Project2].[Name] AS [Name], 
    [Project2].[C2] AS [C1], 
    [Project2].[C1] AS [C2], 
    [Project2].[ChildId] AS [ChildId], 
    [Project2].[Name1] AS [Name1]
    FROM ( SELECT 
        [Project1].[ParentId] AS [ParentId], 
        [Project1].[Name] AS [Name], 
        [Extent3].[ChildId] AS [ChildId], 
        [Extent3].[Name] AS [Name1], 
        CASE WHEN ([Extent3].[ChildId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        [Project1].[C1] AS [C2]
        FROM   (SELECT 
            [Extent1].[ParentId] AS [ParentId], 
            [Extent1].[Name] AS [Name], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[Children] AS [Extent2]
                WHERE [Extent1].[ParentId] = [Extent2].[ParentId]) AS [C1]
            FROM [dbo].[Parents] AS [Extent1] ) AS [Project1]
        LEFT OUTER JOIN [dbo].[Children] AS [Extent3] ON [Project1].[ParentId] = [Extent3].[ParentId]
    )  AS [Project2]
    ORDER BY [Project2].[ParentId] ASC, [Project2].[C1] ASC
go

Not 3 queries that you might be concerned would block. This was when looking at the navigation properties for related records.

The bigger question I saw when looking at your example to double-check was this line:

LastVotedAt = _context.PollVotes.Where(vote=>vote.PollId == poll.Id).Select(vote => vote.VoteDate).SingleOrDefault()

As this would go back directly to the Context rather than access votes through a collection on the Poll. But I tried that as well and it too still resulted in a single query.

Children = x.Children.Select(c => new { c.ChildId, c.Name }).ToList(),
ChildCount = x.Children.Count(),
YoungestChild = context.Children.OrderBy(c=>c.BirthDate).Where(c=>c.ParentId == x.ParentId).FirstOrDefault()

In my test example I go back to the context to retrieve the Youngest child for the parent record rather than the Children navigation property. In this case it still executes as 1 query.

For questions like this I definitely recommend creating an EF experimentation sandbox project with a local database, then leverage and SQL profiler tool to watch the SQL statements being produced and when they are executed. Async is useful for queries that are expected to take a while to run, but should be used sparingly as they can lower the overall performance of the queries being run when used on every trivial query.

Thank you very much. Accepted. I just realized this in few hours and I just added commend. You told me I'm not wrong.

Collectives™ on Stack Overflow

How to properly use nested queries in EntityFramework?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related