Take() after GroupJoin() forces client-side evaluation

[sandersaares]

The below example code results in client-side evaluation of the "take" operation. It works if I move the "take" before the GroupJoin.

The LINQ expression 'Take(__p_0)' could not be translated and will be evaluated locally.'.

In my head the SQL query would be the same in either case (the joined rows should not affect the count/order of the parent rows, after all), so I would expect Take to work on both sides of the GroupJoin.

Steps to reproduce

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Debug;
using System;
using System.Linq;

namespace ConsoleApp14
{
    internal class Program
    {
        public static readonly Guid Parent1Id = new Guid("bcee42c7-ad08-4a43-b9ea-ecb62939666e");
        public static readonly Guid Parent2Id = new Guid("d65de666-7a3c-49bb-90e2-c780d10832aa");
        public static readonly Guid Child1Id = new Guid("5d846ff2-9db8-4a45-a939-3746df161eb4");
        public static readonly Guid Child2Id = new Guid("7dcdc71c-d4c9-4d79-a62a-10e9545f4e21");

        private static void Main(string[] args)
        {
            using (var dc = new DataContext())
            {
                var joined = dc.Parent
                    //.Take(1) // Here it is fine.
                    .GroupJoin(dc.Child, p => p.Id, c => c.ParentId, (parent, children) => new { parent, children })
                    .Take(1) // Here it forces client-side evaluation.
                    .ToArray();

                Console.WriteLine($"{joined.Length} rows.");
            }
        }
    }

    public class ParentRow
    {
        public Guid Id { get; set; }

        public DateTimeOffset Created { get; set; }
    }

    public class ChildRow
    {
        public Guid Id { get; set; }
        public Guid ParentId { get; set; }

        public DateTimeOffset Created { get; set; }
    }

    public class DataContext : DbContext
    {
        public DbSet<ParentRow> Parent { get; set; }
        public DbSet<ChildRow> Child { get; set; }

        public DataContext()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ConfigureWarnings(warnings =>
            {
                warnings.Throw(RelationalEventId.QueryClientEvaluationWarning);
            });

            optionsBuilder.EnableSensitiveDataLogging();
            optionsBuilder.UseLoggerFactory(MyLoggerFactory);

            if (!optionsBuilder.IsConfigured)
                optionsBuilder.UseSqlServer("Server=(local);Database=EFCore_Issue_Repro;Trusted_Connection=True;");
        }

        public static readonly LoggerFactory MyLoggerFactory = new LoggerFactory(new[]
        {
            new DebugLoggerProvider((category, level) => true)
        });

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ParentRow>()
                .HasData(
                    new ParentRow
                    {
                        Id = Program.Parent1Id,
                        Created = DateTimeOffset.UtcNow
                    },
                    new ParentRow
                    {
                        Id = Program.Parent2Id,
                        Created = DateTimeOffset.UtcNow
                    }
                    );

            modelBuilder.Entity<ChildRow>()
                .HasData(
                    new ChildRow
                    {
                        Id = Program.Child1Id,
                        ParentId = Program.Parent1Id,
                        Created = DateTimeOffset.UtcNow
                    },
                    new ChildRow
                    {
                        Id = Program.Child2Id,
                        ParentId = Program.Parent2Id,
                        Created = DateTimeOffset.UtcNow
                    }
                );
        }
    }
}

Further technical details

EF Core version: 2.2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 1809
IDE: Visual Studio 2017 15.9.5

[tuespetre]

The query compiler can translate group-join by using a left join with an ordering, reading in the results chunked by the parents' ids.

For the 'before' case, the query is selecting one parent and then group-joining its children. In other words, the parent row comes from a SELECT TOP(1) subquery, onto which the children are joined.

But when you have the Take after the group join, while the resulting data should be the same, the query compiler's options for translation are not the same. It can't add TOP (1) to the query that was built so far because there may be more than one row due to joining more than one child for the first parent (well, it could, but it would have to use FOR JSON or FOR XML or something to marshall the children out in a single row.)

[smitpatel]

GroupJoin which does not convert to LeftJoin is not allowed starting 3.x release. See #17068