LINQ support for Group By

[ealsur]

Capturing requirement for LINQ GroupBy support to track future progress.

[markjbrown]

Stack question on this, https://stackoverflow.com/questions/60356762/efcore-3-1-cosmosdb-query-could-not-be-translated

Thanks.

[pnda489]

Is there any ETA for this?

[valerionatangelo]

We really need an ETA on this feature.

[dan-matthews]

/bump... I've been waiting for this feature a loooong time and it seems SO close :)

[AshishMoradiya]

Waiting for group by support

[petro2050]

any update?

[joergeugster]

still waiting....?

[ealsur]

@khdang Do you have any updates on this request?

[onionhammer]

This would be tremendous! Are the supported operations documented somewhere or do we just figure it out by trial and error at runtime?

[fvalentiner]

It would be amazing! It's basically the only thing holding us from migrating to CosmosDB

[alexeymarkov]

Any news there? Requesting all entities to the client and doing GROUP BY and COUNT operations at the client results in a significant higher RUs...

[adstep]

Love to see this feature supported.

[adamreed90]

Please! <3

[vengi83644]

Must needed !!!

[j82w]

@timsander1 any update on this?

[timsander1]

This is on our long-term roadmap but unplanned in the current 6 month semester. For now, we recommend using SQL queries instead of LINQ if you need to use GROUP BY.

[dan-matthews-takeda]

This is on our long-term roadmap but unplanned in the current 6 month semester. For now, we recommend using SQL queries instead of LINQ if you need to use GROUP BY.

Unfortunately, using SQL queries just isn't possible for us - and I suspect most people using LINQ queries. We build up a query using LINQ expressions, often using generics through a repository pattern or similar. To construct SQL queries would require an entire and extremely complex re-architecture of our entire solution. Not having GroupBy LINQ support is actually killing our Cosmos DB RUs - we're having to double-hit the DB with large result sets.

I understand that everything has a priority so, given that this ticket has been open over a year already, is the delay due to low priority or complexity of implementation?

[vengi83644]

This is on our long-term roadmap but unplanned in the current 6 month semester. For now, we recommend using SQL queries instead of LINQ if you need to use GROUP BY.

Unfortunately, using SQL queries just isn't possible for us - and I suspect most people using LINQ queries. We build up a query using LINQ expressions, often using generics through a repository pattern or similar. To construct SQL queries would require an entire and extremely complex re-architecture of our entire solution. Not having GroupBy LINQ support is actually killing our Cosmos DB RUs - we're having to double-hit the DB with large result sets.

I understand that everything has a priority so, given that this ticket has been open over a year already, is the delay due to low priority or complexity of implementation?

+100000

[jstensved]

This is on our long-term roadmap but unplanned in the current 6 month semester. For now, we recommend using SQL queries instead of LINQ if you need to use GROUP BY.

Unfortunately, using SQL queries just isn't possible for us - and I suspect most people using LINQ queries. We build up a query using LINQ expressions, often using generics through a repository pattern or similar. To construct SQL queries would require an entire and extremely complex re-architecture of our entire solution. Not having GroupBy LINQ support is actually killing our Cosmos DB RUs - we're having to double-hit the DB with large result sets.

I understand that everything has a priority so, given that this ticket has been open over a year already, is the delay due to low priority or complexity of implementation?

I can't agree more. Our database is too big to even be able to fetch everything into memory and count. It must be done server-side.

[kipusoep]

Looking for this 'feature' as we speak, too bad it's not implemented yet.

[leonids2005]

this is a big problem for us as well!!! Really need this feature!!!

[randolphpark]

I am expericing same issue. Please do cosnider to prioritise this feature. Much appriciated.

[onionhammer]

GroupBy is kind of an antipattern until cosmos can somehow utilize the index under on the server; currently it has to read through every single document to execute a group by

[leonids2005]

why it is an anti-pattern I do not understand? If the engine cannot efficiently execute the operation it does not mean this is an anti-pattern :)

Group by is supported by SDK so it should be supported in Linq... this is a standard functionality imo.

[onionhammer]

If the engine cannot efficiently execute the operation it does not mean this is an anti-pattern :)

it would be great if it were supported, but it would be even better if the server supported it better so groupby's cost wasnt directly proportional to how much data is in your container.

When I query 'select count(c) from c' and get back 127000, it costs 11 RUs and .6ms

When I query 'SELECT count(c), c.Stage FROM c GROUP BY c.Stage`, I get back 8 records and it costs 4574 RUs and 127ms

[fvalentiner]

@orionhammer

We use the Synapse Link feature to do that. So far it has worked great. A bit slow to get the SQL Queries out of Synapse but overall works ok

[onionhammer]

@fvalentiner we use that too for our reporting in PowerBI, but not our frontend application servers - too slow, as you said.

[leonids2005]

Curiosity, does the CosmosDB team read this, and how often? on the 23rd of April, they said it is not on 6 months roadmap.
I think we deserve some updates after 7 months.
We have raised this issue in our FastTrack project discussion but also have not gotten any response. :( ... disappointing.

Feel free to vote for this feature (it was actually not there :( ) - hopefully, it may increase the priority.

https://feedback.azure.com/d365community/idea/fb4fb5ad-e14e-ec11-a81a-6045bd78b970

[PKPublicCode]

Let me congratulate this feature request on 2-nd anniversary :) Any updates on this feature?

[timsander1]

Hi all, quick update that LINQ support for GROUP BY is planned for the first half of this calendar year. We're also investing in some improvements in GROUP BY performance that will be released around the same time.

[onionhammer]

Hi all, quick update that LINQ support for GROUP BY is planned for the first half of this calendar year. We're also investing in some improvements in GROUP BY performance that will be released around the same time.

Meaning investing in improving group by perf on the cosmos server side?

[dan-matthews-takeda]

Here's hoping that it will include the ability to sort by more than 1 column without an index ;)

[timsander1]

Hi all, quick update that LINQ support for GROUP BY is planned for the first half of this calendar year. We're also investing in some improvements in GROUP BY performance that will be released around the same time.

Meaning investing in improving group by perf on the cosmos server side?

That's correct. Improvements within the query engine.

[timsander1]

For additional feedback, could you please share ideas here: https://feedback.azure.com/d365community/forum/3002b3be-0d25-ec11-b6e6-000d3a4f0858

While we review Github issues, they don't get nearly the same attention as the above site. We also post updates on the above site at a regular 6 month cadence, when we plan our roadmap.

[ivpadim]

Cant wait for this feature to ship!

[shekarc]

Do you know of the anticipated release date for this feature?

[rmajid2]

Any updates on Group By Improvements (Linq support, engine optimization , index, RU Cost, Order By ...) ?

[dan-matthews]

Any news on this, Cosmos team? Been waiting for this feature a looooong time ;)

[markjbrown]

Adding @jcocchi

[dave101ua]

Hello ! Any plans regarding requested feature, please ?

[rmajid2]

FYI https://devblogs.microsoft.com/cosmosdb/introducing-a-new-system-function-and-optimized-query-operators/#group-by-and-distinct-optimizations

[adamreed90]

FYI https://devblogs.microsoft.com/cosmosdb/introducing-a-new-system-function-and-optimized-query-operators/#group-by-and-distinct-optimizations

Indeed it is supported in the query language but not supported with LINQ, the purpose of this raised issue.

[dan-matthews]

o/~ All I want for Christmas... is support for LINQ GroupBy... o/~

[machielvisser]

And we reached Q2 2023, even though @timsander1 indicated it was planned for H1 2022. What happened since then?

[markjbrown]

Adding @jcodella

[jmolla31]

Hi do we have a roadmap for this?

[dan-matthews]

June 2023, and still all quiet on this most vital and wanted aspect of Cosmos DB! Given that this is such a hotly awaiting feature by so many people, but it is not ever being implemented - does this suggest the state of the overall Cosmos DB service? curious

[jacobmohl]

+1 on this...

[jcocchi]

Hi everyone, thanks for your patience as we work on this highly anticipated feature- we appreciate your feedback! Group By support in LINQ is planned for Fall 2023.

[onionhammer]

Will add #3935 after group by support is added