dotnet/efcore

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

smitpatel opened this issue · 9 comments

smitpatel commented

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 commented

Dependent on #12089

sam-wheat commented

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

  • 👍1
ajcvickers commented

Discuss in triage 🇦🇺

elAndyG commented

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

jag43 commented

Is there an ETA for this?

smitpatel commented

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

smitpatel commented

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

smitpatel commented

@maumar - What is pending on this issue?

maumar commented

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

  • 👍1