[Solved]-Advanced LINQ Grouping and Projection Query for Hierarchical Data (EF 4.0 + LINQ + ASP.NET MVC + HighCharts)-Highcharts

As I understand, you have millions of records in a database, and you need very good performance from your application, and you have very complex structure.

Stay away from EF and I mean it. You can use it for smaller datasets, but for anything that requires superb performance from a huge query, don't even go there.

My suggestion is to write the queries manually, because if you wanted to optimize as much as you could with EF, you would practically not be able to skip much code anyway and would basically be writing SQL in LINQ way (ie specifying all SELECT statements).

If you want a little bit more specific, use Dapper. It's the "ORM" that runs StackOverflow. There's comparisons on speed here: http://code.google.com/p/dapper-dot-net/

However, if you're dead set on using EF...

Don't try to write one query. It won't happen. Instead, split them. Take a different one for each group that you need to make. It's by far the easiest way out. I believe you should already have a LINQ query for getting just one group out. Now repeat that with every other group you need, especially if you have a ton of Wheres and OrderBy's in it.

Source: a guy that works with large dataset queries daily.