Optimising Linq to Entities

Question

I have a set of related entities. I'm using linq to group a collection of an entity type by a property on a related entity and then doing a sum calculation on a property of another related entity:

Vehicles.GroupBy(v => v.Mechanics.Engine.Size)
        .Select(g => g.Sum(s => s.Passengers.Count));

I'm trying to do as much as possible via linq to entities because there is a large number of records in the db. However, the generated sql includes 9 select statements and an outer apply which takes more than 5 times as long to execute as writing the simplified sql code to achieve the same in one select statement.

How do I improve the generated sql?

first of all I am impressed you profiled the sql generated so many don't — krystan honour, Oct 22 '14 at 22:34
Are you sure this is the only code that builds up a query? You should include the generated sql and simplified version. You also may have a [N + 1 problem](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue). It would be nice to see your models definitions. I've build a simple query very similar to your one, that resulted in 1 quite-simple sql — Ilya Ivanov, Oct 22 '14 at 22:37

score 0 · Answer 1 · answered Oct 22 '14 at 22:46

Perhaps try the query like this:

Vehicles
    .Select(x => new
    {
        EngineSize = x.Mechanics.Engine.Size,
        PassengersCount = xs.Passengers.Count,
    })
    .ToArray()
    .GroupBy(v => v.EngineSize)
    .Select(g => g.Sum(s => s.PassengersCount));

This will execute in a single query, but may pull back too much data to make it faster. It's worth timing and profiling to see which is better.

JWP · Answer 2 · 2014-10-22T23:05:04.853

You could also consider a hybrid approach whereby you bypass LINQ query generation yet use EF to project results into strong types like this:

public List<Vechicles> GetVehcileInformation(string VehicleType){
  var QueryString = Resources.Queries.AllVehicles;
  var parms = new List<SqlParameters>();
  parms.Add(new SqlParameter("VehicleType", VehicleType );
  try{

      using (var db = new MyEntities()){
      var stuff= db.SqlQuery<Vehicles>(QueryString, parms.ToArray());
      return stuff.ToList();
      }

  }catch(exception iox){Log.ErrorMessage(iox);}

 }

The idea is that the group by is done at DB layer which gives you more control than in LINQ. You get the speed of direct SQL Queries but get back strongly typed results! The query string itself is stored in a resources file as a string with Parameter place holders like this:

Select * from Table Where FieldName = @VehicleType...

score 0 · Accepted Answer · answered Oct 22 '14 at 23:00

You're in fact counting the number of passengers per engine size. So, the navigation properties permitting, you could also do:

Passengers.GroupBy(p => p.Vehicle.Mechanics.Engine.Size)
          .Select(g => g.Count())

This will probably generate more joins and less subqueries. And only one aggregating statement in stead of two in the original query, of which one (Count) is repeated for each size.

Optimising Linq to Entities

3 Answers3