GROUP BY translation - invalid SQL generated - 'MAX' is not a recognized built-in function name

[tomasherceg]

I have tried the Group By translation feature in EF Core 2.1 Preview 2.
The following query generated SELECT N+1, which is expected according to issue #10472.

My original query was:

var report = context.Posts
    .GroupBy(p => p.Blog)
    .Select(p => new ReportItem()
    {
        BlogName = p.Key.Name,
        PostCount = p.Count(),
        LatestPostDate = p.Max(i => i.PublishedDate),
        MaxComments = p.Max(i => i.Comments.Count())
    })
    .ToList();

I tried to rewrite the query so the post.Comments.Count() was hit before the actual grouping:

var report = context.Posts
    .Select(p => new
    {
        BlogName = p.Blog.Name,
        PublishedDate = p.PublishedDate,
        CommentsCount = p.Comments.Count()
    })
    .GroupBy(p => p.BlogName)
    .Select(p => new ReportItem()
    {
        BlogName = p.Key,
        PostCount = p.Count(),
        LatestPostDate = p.Max(i => i.PublishedDate),
        MaxComments = p.Max(i => i.CommentsCount)
    })
    .ToList();

But the SQL generated is invalid - the syntax near the second MAX call is not valid (I am using SQL Server 13.0).

SELECT [p.Blog].[Name] AS [BlogName], COUNT(*) AS [PostCount], MAX([p].[PublishedDate]) AS [LatestPostDate], MAX((
    SELECT COUNT(*)
    FROM [Comments] AS [c1]
    WHERE [p].[Id] = [c1].[PostId]
) AS [CommentsCount0]) AS [MaxComments]
FROM [Posts] AS [p]
INNER JOIN [Blogs] AS [p.Blog] ON [p].[BlogId] = [p.Blog].[Id]
GROUP BY [p.Blog].[Name]

I am getting the following error:

Exception message: 'MAX' is not a recognized built-in function name.
Stack trace: not important, it's a classic System.Data.SqlClient.SqlException

Steps to reproduce

Here is the model.

    public partial class Blogs
    {
        public Blogs()
        {
            Posts = new HashSet<Posts>();
        }

        public int Id { get; set; }
        public string Name { get; set; }

        public ICollection<Posts> Posts { get; set; }
    }

    public partial class Posts
    {
        public Posts()
        {
            Comments = new HashSet<Comments>();
        }

        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime? PublishedDate { get; set; }
        public int BlogId { get; set; }

        public Blogs Blog { get; set; }
        public ICollection<Comments> Comments { get; set; }
    }

    public partial class Comments
    {
        public int Id { get; set; }
        public string Text { get; set; }
        public string AuthorName { get; set; }
        public string IpAddress { get; set; }
        public DateTime CreatedDate { get; set; }
        public string Type { get; set; }
        public int PostId { get; set; }

        public Posts Post { get; set; }
    }

Further technical details

EF Core version: 2.1.0-preview2-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.6

[ajcvickers]

Duplicate of #11636