locked
IEnumerable - How many times a query will execute really? RRS feed

  • Question

  •  Hello all,           

    I read that when referencing an IEnumerable, the query will be re-executed every time the list is referenced.

    If I have 4 rows (objects) in the table (entity) destination:

    Q1:

    how many actual database invokations will take place in the sample code below? (i.e how many times SQL will be generated and sent to the database)? Is it 5 times?

    IEnumerable<String> IEnumResult = (from s in _cntxt.Destinations select s.Name);
    Console.WriteLine(IEnumResult.Count());
    
    foreach (var item in IEnumResult)
    {
     Console.WriteLine(item.ToString());
    }

    Q2:

    If I use var instead of IEnumerable, do I get the database called 1 time only?

    Thanks.


    Wednesday, May 9, 2012 10:03 AM

Answers

  • Hi ForEverLearning,

    If you're enumerating a query of a million rows then each object will be instantiated as it is retrieved, EF doesn't instantiate them all the moment you hit the foreach (...). If you want the Garbage Collector to be able to collect the previously retrieved entities before you've finished enumerating the query then you'd need to set the ObjectSet to not track the retrieved entities: _cntxt.Destinations.MergeOption = MergeOption.NoTracking;

    There's no "one size fits all" when it comes to performance. Your best bet is to test and see what works best for you in your situation. You can check out this link for things to take into account when dealing with EF performance: http://msdn.microsoft.com/en-us/library/cc853327(v=vs.100).aspx

    Regards,

    Tyler

    Wednesday, May 9, 2012 11:26 AM

All replies

  • Hi ForEverLearning,

    A query is sent to the database every time you enumerate the IEnumerable<string>. The code you posted will result in two database calls, once at .Count() and once at the foreach (...). Changing to var will change the type of IEnumResult to IQueryable<string> and will still result in two calls to the database but the first will be sent as something like "SELECT Count(*) FROM Destinations" since the call to .Count() will be converted to T-SQL. If you know you'll need to enumerate the results more than once then you'll want to call .ToList() or .ToArray() to populate an in memory collection and then enumerate that.

    Regards,

    Tyler

    Wednesday, May 9, 2012 10:22 AM
  • Hi Tyler,

    Thanks for your reply. If the foreach will execute the query once only, then the objects will have to be stored somewhere internally. My worry is about two things here if the objects are stored implicitly on the client:

    1. Performance

    2. Memory

    for example, if the table has millions of rows, how do you think the best approach would be?

    Wednesday, May 9, 2012 11:13 AM
  • Hi ForEverLearning,

    If you're enumerating a query of a million rows then each object will be instantiated as it is retrieved, EF doesn't instantiate them all the moment you hit the foreach (...). If you want the Garbage Collector to be able to collect the previously retrieved entities before you've finished enumerating the query then you'd need to set the ObjectSet to not track the retrieved entities: _cntxt.Destinations.MergeOption = MergeOption.NoTracking;

    There's no "one size fits all" when it comes to performance. Your best bet is to test and see what works best for you in your situation. You can check out this link for things to take into account when dealing with EF performance: http://msdn.microsoft.com/en-us/library/cc853327(v=vs.100).aspx

    Regards,

    Tyler

    Wednesday, May 9, 2012 11:26 AM
  • Thank you very much.
    Wednesday, May 9, 2012 5:22 PM