Query with multiple level subqery takes tremendous about of time to execute

[dvdobrovolskiy]

Query is like that

_context.Jobs.Select(s => new Job
            {
                ClosedDate = s.ClosedDate,
                JobId = s.JobId,
                User = s.User,
                UserId = s.UserId,
                Zayavka = _context.Zayavkas.Select(s1=> new Zayavka
                {
                    ZayavkaId = s1.ZayavkaId,
                    ZayavkaStatus = s1.ZayavkaStatus,
                    Block = _context.Blocks.SingleOrDefault(d1=> d1.BlockId == s1.BlockId),
                    Apartment = _context.Apartments.SingleOrDefault(d1 => d1.ApartmentId == s1.ApartmentId),
                    Description = s1.Description,
                    Creator = _context.Users.SingleOrDefault(d1 => d1.Id == s1.CreatorId),
                    Modifier = _context.Users.SingleOrDefault(d1 => d1.Id == s1.ModifierId),
                    CreatedDate = s1.CreatedDate,
                    EditedDate = s1.EditedDate,
                }).SingleOrDefault(d => d.ZayavkaId == s.ZayavkaId),
                ZayavkaId = s.ZayavkaId,
                CreatedDate = s.CreatedDate,
                Description = s.Description
            })
            .Where(w=>!w.ClosedDate.HasValue)
            .Where(w=>w.CreatedDate <= DateTime.Now)
            .OrderBy(o => o.ZayavkaId)
            .Skip(offset)
            .Take(DefaultPageSize)
            .ToList()

In SQL profiler I see multiple separate queries (open per first level query row then open per second query row)

like

SELECT` [d11].[Id], [d11].[AccessFailedCount], [d11].[ConcurrencyStamp], [d11].[Email], [d11].[EmailConfirmed], [d11].[FIO], [d11].[LockoutEnabled], [d11].[LockoutEnd], [d11].[MainPhone], [d11].[NormalizedEmail], [d11].[NormalizedUserName], [d11].[OtherPhone], [d11].[PasswordHash], [d11].[PhoneNumber], [d11].[PhoneNumberConfirmed], [d11].[SecondaryPhone], [d11].[SecurityStamp], [d11].[TwoFactorEnabled], [d11].[UserName], [d11].[UserStatus], [d11].[UserType]
FROM [Users] AS [d11]

SELECT [d11].[Id], [d11].[AccessFailedCount], [d11].[ConcurrencyStamp], [d11].[Email], [d11].[EmailConfirmed], [d11].[FIO], [d11].[LockoutEnabled], [d11].[LockoutEnd], [d11].[MainPhone], [d11].[NormalizedEmail], [d11].[NormalizedUserName], [d11].[OtherPhone], [d11].[PasswordHash], [d11].[PhoneNumber], [d11].[PhoneNumberConfirmed], [d11].[SecondaryPhone], [d11].[SecurityStamp], [d11].[TwoFactorEnabled], [d11].[UserName], [d11].[UserStatus], [d11].[UserType]
FROM [Users] AS [d11]

SELECT [s1].[ZayavkaId], [s1].[ZayavkaStatus], [s1].[BlockId], [s1].[ApartmentId], [s1].[Description], [s1].[CreatorId], [s1].[ModifierId], [s1].[CreatedDate], [s1].[EditedDate]
FROM [Zayavkas] AS [s1]

SELECT [d12].[Id], [d12].[AccessFailedCount], [d12].[ConcurrencyStamp], [d12].[Email], [d12].[EmailConfirmed], [d12].[FIO], [d12].[LockoutEnabled], [d12].[LockoutEnd], [d12].[MainPhone], [d12].[NormalizedEmail], [d12].[NormalizedUserName], [d12].[OtherPhone], [d12].[PasswordHash], [d12].[PhoneNumber], [d12].[PhoneNumberConfirmed], [d12].[SecondaryPhone], [d12].[SecurityStamp], [d12].[TwoFactorEnabled], [d12].[UserName], [d12].[UserStatus], [d12].[UserType]
FROM [Users] AS [d12]

SELECT [d12].[Id], [d12].[AccessFailedCount], [d12].[ConcurrencyStamp], [d12].[Email], [d12].[EmailConfirmed], [d12].[FIO], [d12].[LockoutEnabled], [d12].[LockoutEnd], [d12].[MainPhone], [d12].[NormalizedEmail], [d12].[NormalizedUserName], [d12].[OtherPhone], [d12].[PasswordHash], [d12].[PhoneNumber], [d12].[PhoneNumberConfirmed], [d12].[SecondaryPhone], [d12].[SecurityStamp], [d12].[TwoFactorEnabled], [d12].[UserName], [d12].[UserStatus], [d12].[UserType]
FROM [Users] AS [d12]

SELECT [s1].[ZayavkaId], [s1].[ZayavkaStatus], [s1].[BlockId], [s1].[ApartmentId], [s1].[Description], [s1].[CreatorId], [s1].[ModifierId], [s1].[CreatedDate], [s1].[EditedDate]
FROM [Zayavkas] AS [s1]

and so on. lots of separate queries. And it takes tremendous amount of time (like minute on where main table is 90 rows long).

Is any way EntityFramework to translate in one sql query?

[anpete]

Closing as this should be much better now (@dvdobrovolskiy, it would be great if you could confirm against a recent nightly) There will still be some client eval. for the sub-queries in projections, but we are tracking that as part of #4007