EF7 and GroupBy() cannot be translated

Question

I have following code running on EF7 Beta 8:

var locationGrops = from l in db.Locations
                    group l by l.ServiceType into g
                    select g;

var list = locationGrops.ToList();

When I execute this code, EF displays a warning.

warning : [Microsoft.Data.Entity.Query.QueryCompilationContext] The LINQ express
ion 'GroupBy([l].ServiceType, [l])' could not be translated and will be evaluate
d locally.

The query seems quite basic to me and there is GROUP BY in SQL. Is there any way to make it run on a server?

Answer 1

At this time group by and and most subqueries are not supported by EF7.

Answer 2

You can use context.Locations.FromSql(sql).ToList() to ensure your query is run as you desire on the server.

Answer 3

One approach is to create a database View with the logic (which for complex groupings is probably better anyway).

Right now it's a bit of a hack to use the view, but here's what I came up with if you're using scaffolding :

How can I create database Views in a scaffolded DbContext in EF7 / Entity Framework Core 1.0

In summary I inherit from the DbContext, and manually create the entity class for the view.