title: Entity Framework Performance - Each relational property call slow
body: <p>I have a large database that has one primary table with a few hundred thousand records, and then 30-40 different relational tables that join from it for additional data.</p>
  
  <p>I created an EF connection to this database, and I was able to pull the data without an issue.</p>
  
  <p>The goal for our project is to keep the 200K objects, in memory, in our own custom POCO classes that do not match the database structure we have. The idea here is that with the 200K items cached in memory, when someone goes to fetch 8000 of them, it should return extremely fast.</p>
  
  <p>I was also able to achieve this using MemoryCache.</p>
  
  <p>The problem is the original loading of the POCO clas objects. As I iterate through my List&lt;> of records and populate my POCO objects, it seems to take more time than I had hoped. I believe that EF is making SQL calls for each record to get the relational data. So when i say:</p>
  
  <pre><code>foreach(var o in MyEntities.Stuff)
  {
  var x = new MyCustomClass();
  x.Property1 = o.RelationalTableA.PropertyX;
  x.Property2 = o.RelationalTableB.PropertyY;
  x.Property3 = o.RelationalTableC.PropertyZ;
  MyPocoList.Add(o);
  }
  </code></pre>
  
  <p>This seems to take a few hundred milliseconds.</p>
  
  <p>Well, for 100K items, that might be 300ms * 100,000 = 8 hours ... </p>
  
  <p>Just setting the poco objects to hard coded values, for 100K items, takes a couple of minutes, which is a reasonable amount of time to load up a huge amount of records in memory.</p>
  
  <p>Are there optimization approaches I can use with EF so that it isn't running join queries for each property I am setting?</p>
  
  <p>Edited to add:</p>
  
  <p>In my code, I am also creating different types of classes depending on value(s) in the returned record. For example:</p>
  
  <pre><code>IInterface x;
  if(o.PropertyA == "A")
  {
    x = new MyCustomClass1();
  }
  else if(o.PropertyA == "B")
  {
    x = new MyCustomClass2();
  }
  </code></pre>
  
  <p>.</p>