EF7 and GroupBy () cannot be translated

Question

I have 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 very simple to me, and in SQL there is a GROUP BY group. Is there any way to make it work on the server?

Answer 1

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

Answer 2

You can use context.Locations.FromSql(sql).ToList() to ensure that your request is launched at your request on the server.

Answer 3

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

This is a little hack right now to use the view, but here is what I came up with if you are using scaffolding:

How to create a database view in a secure DbContext in EF7 / Entity Framework Core 1.0

In conclusion, I inherit from DbContext and manually create an entity class for presentation.