none
Query Performance RRS feed

  • Question

  • Hi, i recently read the following statement:

    "Entity Framework will query the database every time you trigger an iteration over the contents of a DbSet. This has performance implications if you are continually querying the database for the same data. To avoid this,
    you can use a LINQ operator such as ToList to copy the results into a list. You can then iterate over the contents of this list multiple times without causing multiple trips to the database."

    Am i correct that the following code results in multiple trips to the database? If so how can i verify this. When i try using the Sql Profiler (which, by the way, i am new to) i seem to get the same number of calls as in the "amended version" of the code below.

    foreach (var destination in context.Destinations)
    {
      Console.WriteLine(destination.Name);
    }

    Amended version:

    var query = context.Destinations.ToList();
    
    foreach (var destination in query)
    {
      Console.WriteLine(destination.Name);
    }

    "Trace properties" in Sql Profiler:

    Results:

    Tuesday, October 29, 2013 7:23 PM

Answers

  • You are confusing queries with collections. A query (IQueryable) is an expression, and every time you use foreach, or ToList() or AsEnumerable() the query is run.

    Once you run the query you can copy the results into a collection and iterate them without hitting the database. EG:

    foreach (var destination in context.Destinations)   //runs query
    {
      Console.WriteLine(destination.Name);
    }
    
    foreach (var destination in context.Destinations)   //runs query
    {
      Console.WriteLine(destination.Name);
    }
    
    var list = context.Destinations.ToList(); //runs query
    
    foreach (var destination in list)  //iterates results in memory
    {
      Console.WriteLine(destination.Name);
    }
    
    foreach (var destination in list)  //iterates results in memory again
    
    {
      Console.WriteLine(destination.Name);
    }

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, October 30, 2013 5:21 PM

All replies

  • <copied>

    var query = context.Destinations.ToList();

    foreach (var destination in query)
    {
     
    Console.WriteLine(destination.Name);
    }

    <end>

    If you Dispose() on "context" closing the connection to the database before you go into the loop, it can't go back to the database because the connection is closed, and you are only working with objects in the 'query' object in memory a collection of objects in memory.

    You should close the connection when it is no longer needed no matter what you are doing with CRUD (create, read, update and delete) operations with the database, and it should be done as soon as possible.

    You don't need the ToList() and do the query without the ToList(). You should just do the query and close the connection before iterating over the  objects in the collection -> disconnected objects.

    ttt

    Wednesday, October 30, 2013 2:38 AM
  • You are confusing queries with collections. A query (IQueryable) is an expression, and every time you use foreach, or ToList() or AsEnumerable() the query is run.

    Once you run the query you can copy the results into a collection and iterate them without hitting the database. EG:

    foreach (var destination in context.Destinations)   //runs query
    {
      Console.WriteLine(destination.Name);
    }
    
    foreach (var destination in context.Destinations)   //runs query
    {
      Console.WriteLine(destination.Name);
    }
    
    var list = context.Destinations.ToList(); //runs query
    
    foreach (var destination in list)  //iterates results in memory
    {
      Console.WriteLine(destination.Name);
    }
    
    foreach (var destination in list)  //iterates results in memory again
    
    {
      Console.WriteLine(destination.Name);
    }

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, October 30, 2013 5:21 PM
  • Thanks David. Well explained. -:)
    Thursday, October 31, 2013 5:21 PM