Efcore: Query: Incorrect syntax near the keyword 'AS` for group by aggregate

[smitpatel]

Actual Title Query: Define a transport mechanism for binding Key/ElementSelector for GroupBy Aggregate

We currently use MemberInfoProjectionMapping, which was designed to be used for things which are added to projection. And whenever it is referenced it would come from inner subquery.

We started reusing it for GroupBy Aggregate case since we treat element selector same as select clause. Which causes a lot of incorrect SQL.

Filing a general issue and closing all issues failing with this.

[smitpatel]

Dependent on #12089

[sam-wheat]

Perhaps covered by one of the other issues but I will add this for reference:

var xresult = db.Comments.GroupBy(x => x.ID)
            .Select(x => new { k = x.Key.ToString(), v = x.Key.ToString() }).ToList();

Copied from profiler:

SELECT CONVERT(VARCHAR(20), [x].[ID] AS [Key]) AS [v]
FROM [Comments] AS [x]
GROUP BY [x].[ID]

Exception: Incorrect syntax near the keyword 'AS'.

EF Core 2.1.1

[ajcvickers]

Discuss in triage 🇦🇺

[elAndyG]

I receive the same Sql structure as @sam-wheat, but when using x.Key.PropertyName.ToUpper() and x.Key.PropertyName.ToUpper() ?? "Unknown Property"

[jag43]

Is there an ETA for this?

[smitpatel]

Re-opening since this needs more tests to be added.

[smitpatel]

For test look all the closed referenced issue to cover all the scenarios.

[smitpatel]

@maumar - What is pending on this issue?

[maumar]

haven't added any tests yet, but we can/should do this in 5.0