title: EF Creating business objects in Linq or in foreach
body: <p>I am measuring differences in query execution and stumbled upon a case I have no explanation for. The query should retrieve 10000 customers with their main address (a customer can have many addresses). We used 2 different methods with Navigation Properties which differ greatly in execution time.</p>
  
  <p>The first method retrieves the customers the way I usually write Linq queries: write the results directly to a business object and calling ToList(). This method takes 25 seconds to execute.</p>
  
  <p>The second method retrieves the customers as a list of EF Entities first. The EF Entities are converted to business objects in a foreach loop. This method takes 2 seconds to execute.</p>
  
  <p>Can someone explain the difference? And is it possible to modify the first method so the execution time is similar to the second?</p>
  
  <pre><code>private List&lt;ICustomer&gt; NavigationProperties_SO(int method)
  {
     using (Entities context = new Entities())
     {
        context.Database.Log = s =&gt; System.Diagnostics.Debug.WriteLine(s);
  
        context.Configuration.ProxyCreationEnabled = false;
        context.Configuration.AutoDetectChangesEnabled = false;
  
        List&lt;ICustomer&gt; customerList = new List&lt;ICustomer&gt;();
        if (method == 1)
        {
           // Execution time: 25 seconds
           customerList = (from c in context.cust
                                             .Include(o =&gt; o.AddressList)
                                             .Include(o =&gt; o.AddressList.Select(p =&gt; p.ADDR))
                               let mainAddress = c.AddressList.Where(o =&gt; o.Main_addr == "1").FirstOrDefault()
                               select new Customer
                               {
                                  cust = c,
                                  mainAddress = mainAddress,
                                  addr = mainAddress == null ? null : mainAddress.ADDR
                               }).AsNoTracking().ToList&lt;ICustomer&gt;();
        }
        else if (method == 2)
        {
           // Execution time: 2 seconds
           var tempList = (from c in context.cust
                                             .Include(o =&gt; o.AddressList)
                                             .Include(o =&gt; o.AddressList.Select(p =&gt; p.ADDR))
                           select c).AsNoTracking().ToList();
           foreach (var c in tempList)
           {
              ICustomer customer = new Customer();
              var mainaddress = c.AddressList.Where(o =&gt; o.Main_addr == "1").FirstOrDefault();
              customer.cust = c;
              customer.mainAddress = mainaddress;
              customer.addr = mainaddress == null ? null : mainaddress.ADDR;
              customerList.Add(customer);
           }
        }
        return customerList;
     }
  }
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>Here are the (simplified) queries generated by Entity Framework:</p>
  
  <p>Method 1</p>
  
  <pre><code>SELECT 
  *
  FROM   [DBA].[CUST] AS [Extent1]
  OUTER APPLY  (SELECT TOP ( 1 ) 
      *
      FROM [DBA].[CUST_ADDR] AS [Extent2]
      WHERE (([Extent1].[Id] = [Extent2].[Id]) AND (N'1' = [Extent2].[Main_addr])
      ORDER BY 'a' ) AS [Limit1]
  LEFT OUTER JOIN [DBA].[ADDR] AS [Extent3] ON [Limit1].[Id] = [Extent3].[Id]
  </code></pre>
  
  <p>Method 2</p>
  
  <pre><code>SELECT 
  *
  FROM ( SELECT 
      *
      FROM  [DBA].[CUST] AS [Extent1]
      LEFT OUTER JOIN  (SELECT *
          FROM  [DBA].[CUST_ADDR] AS [Extent2]
          LEFT OUTER JOIN [DBA].[ADDR] AS [Extent3] ON [Extent2].[Id] = [Extent3].[Id] ) AS [Join1] ON ([Extent1].[Id] = [Join1].[Id])
  )  AS [Project1]
  </code></pre>
  
  <p>The difference is that the first method does the filtering in the query (´let´) while the second method retrieves all records and filters in the loop.</p>