Does converting from DbSet to IEnumerable cause the query to execute?

David Kethel picture David Kethel · Aug 14, 2012 · Viewed 9k times · Source

I have the following 2 methods in my Logs repository.

public IEnumerable<Log> GetAll()
{
   var db = new CasLogEntities();
   return db.Logs;
}           


public DbSet<Log> GetAllSet()
{
   var db = new CasLogEntities();
   return db.Logs;
}           

The only difference is that one returns an IEnumerable of Log and the other a DbSet of Log.

In My Asset Controller I have the following code

var allLogs = _logRepo.GetAllSet();
var Logs = (from log in allLogs
              group log by log.DeviceId
              into l
              select new {DeviceId = l.Key, TimeStamp = l.Max(s => s.TimeStamp)}).ToList();

Now the issues is that I am getting massive performance difference in the group by statement depending on which one of the repo methods I call.

  • getAllSet which returns the DbSet is lightning fast,
  • GetAll returns IEnumerable is reallllyyyy slow.

Can anybody explain this. I was thinking that the conversion of the DbSet to the IEnumerable in the GetAll was causing the Query to execute and hence I was doing the group by on a massive in memory set. While as the GetAllSet was deferring the query execution until the "ToList()" and hence was doing the group by work on the server.

Is this correct? Is there another way to explain this?

I would prefer for the GetAll to return the IEnumerable as I am more familiar with it and its a bit easier for testing.

Answer

Craig Stuntz picture Craig Stuntz · Aug 14, 2012

No, converting to IEnumerable<T> does not cause it to execute.

It does, on the other hand, take the query into object space, so the SQL generated will be different when you project onto an anonymous type. Watch SQL Server Profiler to see the difference.

This would explain the performance difference.

If you return IQueryable<T> instead of IEnumerable<T>, the SQL/performance should be identical.