8

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.

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
David Kethel
  • 2,440
  • 9
  • 29
  • 49

1 Answers1

11

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.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • Thanks for that, Worked Perfectly. I'm curious, can you explain a bit more about what you mean by "object space". Or point me to any good resources about them. Thanks – David Kethel Aug 14 '12 at 03:00
  • 4
    Stuff you do in `IQueryable` with a LINQ to Entities provider is converted to SQL. Stuff you do with `IEnumerable` isn't, because there is no provider to do the conversion. So instead the whole entity must be materialized so that the object can be converted to the new type with .NET code. – Craig Stuntz Aug 15 '12 at 02:47