title: Using LINQ .Select() to cast into new type is TOO slow?
body: <p>Current project, broke head over this problem:</p>
  
  <p>Client Repository:</p>
  
  <pre><code>public class ClientRepository
  {
      // Members
      private masterDataContext _db;
  
      // Constructor
      public ClientRepository()
      {
          _db = new masterDataContext();
      }
  
      public IEnumerable&lt;ClientName&gt; GetCorporateClientNames()
      {
          return _db.corporate_client_tbs.Select(o =&gt; new ClientName { id = o.id, name = o.company_name }).AsEnumerable();
      }
  
      public IEnumerable&lt;ClientName&gt; GetRetailClientNames()
      {
          return _db.retail_client_tbs.Select(o =&gt; new ClientName { id = o.id, name = o.name }).AsEnumerable();
      }
  
      // Define return type
      public class ClientName
      {
          public int id { get; set; }
          public string name { get; set; }
      }
  }
  </code></pre>
  
  <p>Now in the Controller I have the following:</p>
  
  <pre><code>public ActionResult Index()
  {
      var _visits = _db.GetAllServiceVisits();
      return View(_visits);
  }
  </code></pre>
  
  <p>Which takes approximately 4 seconds to load the view with the 200 odd rows currently present.</p>
  
  <p>I want to add a property "client" to the visit model which contains the name of the client.
  The name of the client will come from one of two different tables which is fetched from one of two arrays of type "ClientName".</p>
  
  <p>This is the first approach, which used LINQ :</p>
  
  <pre><code>public ActionResult Index()
  {
      private ClientRepository _cr = new ClientRepository();
      var _retailclients = _cr.GetRetailClientNames().ToArray();
      var _corporateclients = _cr.GetCorporateClientNames().ToArray();
      var _visits = _db.GetAllServiceVisits();
  
      var _temp = _visits.Select(o =&gt; new ServiceVisitViewModel
          {
              service_visit = o,
              client = (o.client_type ? _corporateclients.Where(p =&gt; p.id == o.client_id).First().name : _retailclients.Where(p =&gt; p.id == o.client_id).First().name)
          }).ToArray();
  
      return View(_temp);
  }
  </code></pre>
  
  <p>This is the second approach, using plain 'ol C# :</p>
  
  <pre><code>public ActionResult Index()
  {
      private ClientRepository _cr = new ClientRepository();
      var _retailclients = _cr.GetRetailClientNames().ToArray();
      var _corporateclients = _cr.GetCorporateClientNames().ToArray();
      var _visits = _db.GetAllServiceVisits();
  
      List&lt;ServiceVisitViewModel&gt; _temp = new List&lt;ServiceVisitViewModel&gt;();
      foreach (service_visit_tb v in _visits)
      {
          _temp.Add(new ServiceVisitViewModel { service_visit = v, client = (v.client_type ? _corporateclients.Where(p =&gt; p.id == v.client_id).First().name : _retailclients.Where(p =&gt; p.id == v.client_id).First().name) });
      }
  
      return View(_temp);
  }
  </code></pre>
  
  <p>The second approach is about 8 - 10 times faster based on my tests.</p>
  
  <p>The only difference I can see is the .Select statement.</p>
  
  <p>Can someone please tell me if I have done something wrong in the first approach or in the alternative, why the first approach is so !@#$ing slow?!</p>
  
  <p><b>Edit:</b>
  The _db.GetAllServiceVisits() definition is as follows:</p>
  
  <pre><code>public IEnumerable&lt;service_visit_tb&gt; GetAllServiceVisits()
  {
      var _visits = _db.service_visit_tbs;
      return _visits.AsEnumerable();
  }
  </code></pre>
  
  <p><b>End Edit</b></p>
  
  <p><b>Second Edit:</b>
  I have removed this line from every entry in the log:</p>
  
  <pre><code>-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
  </code></pre>
  
  <p>The Context Log is as follows:</p>
  
  <pre><code>// This query is to fetch all the clients of Type One (corresponding to _cr.GetRetailClientNames() )
  SELECT [t0].[id], [t0].[name]
  FROM [genii].[retail_client_tb] AS [t0]
  
  // This query is to fetch all the clients of Type Two (corresponding to _cr.GetCorporateClientNames() )
  SELECT [t0].[id], [t0].[company_name] AS [name]
  FROM [genii].[corporate_client_tb] AS [t0]
  
  // This is the main query (loading roughly 250 records) which fetchs all Visits
  SELECT [t0].[id], [t0].[client_type], [t0].[client_id], [t0].[machine_type], [t0].[machineID], [t0].[visit_type], [t0].[scheduledon], [t0].[arrivedon], [t0].[completedon], [t0].[reported_problem], [t0].[diagnosed_problem], [t0].[action_taken], [t0].[visit_status], [t0].[engineer_id], [t0].[reference_id], [t0].[addedby], [t0].[addedon], [t0].[modifiedby], [t0].[modifiedon]
  FROM [genii].[service_visit_tb] AS [t0]
  
  // These next queries are not being manually called by me, I assume they are being
  // called when the Razor view is compiled since I am calling the name value of a linked table as such:
  // @item.service_visit.engineer_tb.name
  SELECT [t0].[id], [t0].[type]
  FROM [genii].[visit_type_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [8]
  
  SELECT [t0].[id], [t0].[status]
  FROM [genii].[visit_status_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
  
  SELECT [t0].[id], [t0].[name]
  FROM [genii].[engineer_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
  
  SELECT [t0].[id], [t0].[type]
  FROM [genii].[visit_type_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [11]
  
  SELECT [t0].[id], [t0].[name]
  FROM [genii].[engineer_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
  
  SELECT [t0].[id], [t0].[type]
  FROM [genii].[visit_type_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [7]
  
  SELECT [t0].[id], [t0].[type]
  FROM [genii].[visit_type_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
  
  SELECT [t0].[id], [t0].[type]
  FROM [genii].[visit_type_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6]
  
  SELECT [t0].[id], [t0].[type]
  FROM [genii].[visit_type_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
  
  SELECT [t0].[id], [t0].[name]
  FROM [genii].[engineer_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [5]
  
  SELECT [t0].[id], [t0].[name]
  FROM [genii].[engineer_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [4]
  
  SELECT [t0].[id], [t0].[status]
  FROM [genii].[visit_status_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [8]
  
  SELECT [t0].[id], [t0].[status]
  FROM [genii].[visit_status_tb] AS [t0]
  WHERE [t0].[id] = @p0
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
  </code></pre>
  
  <p>Addendum to the question: Is there a better way to pull this data? I always presumed (never checked) that the foreign key based data access that LINQ context gave me was as good as it gets, but seeing these additional queries, I'm not so sure anymore!</p>
  
  <p>Will post speeds for the second part of execution later today (its a long weekend here in Mumbai but we are working right through)</p>
  
  <p><b>End Edit</b></p>
  
  <p><b>Third Edit</b>
  (I am considering response from webserver to client, since all computation / fetching / binding / etc etc should be accounted for.)
  <br></p>
  
  <p><b>Method One : 6.85 seconds</b> (Call from 3 tables and then do casting into View-Model using C#)</p>
  
  <pre><code>public IEnumerable&lt;service_visit_tb&gt; GetAllServiceVisits()
  {
      var _visits = _db.service_visit_tbs;
      _db.Log = new DebuggerWriter();
      return _visits.AsEnumerable();
  }
  
  public ActionResult Index()
  {
      var _retailclients = _cr.GetRetailClientNames().ToArray();
      var _corporateclients = _cr.GetCorporateClientNames().ToArray();
      var _visits = _db.GetAllServiceVisits();
      List&lt;ServiceVisitViewModel&gt; _temp = new List&lt;ServiceVisitViewModel&gt;();
      foreach (service_visit_tb v in _visits)
      {
          _temp.Add(new ServiceVisitViewModel { service_visit = v, client = (v.client_type ? _corporateclients.Where(p =&gt; p.id == v.client_id).First().name : _retailclients.Where(p =&gt; p.id == v.client_id).First().name) });
      //}
      return View(_temp);
  }
  </code></pre>
  
  <p><b>Method Two : 8.59 seconds</b> (Call from 3 tables and then do casting into View-Model using LINQ)</p>
  
  <pre><code>public IEnumerable&lt;service_visit_tb&gt; GetAllServiceVisits()
  {
      var _visits = _db.service_visit_tbs;
      _db.Log = new DebuggerWriter();
      return _visits.AsEnumerable();
  }
  
  public ActionResult Index()
  {
      var _retailclients = _cr.GetRetailClientNames().ToArray();
      var _corporateclients = _cr.GetCorporateClientNames().ToArray();
      var _visits = _db.GetAllServiceVisits();
      var _temp = _visits.Select(o =&gt; new ServiceVisitViewModel
      {
          service_visit = o,
          client = (o.client_type ? _corporateclients.Where(p =&gt; p.id == o.client_id).First().name : _retailclients.Where(p =&gt; p.id == o.client_id).First().name)
      });
      return View(_temp);
  }
  </code></pre>
  
  <p><b>Method Three : 5.76 seconds</b> (Everything in a single LINQ query - executed on Database)</p>
  
  <pre><code>public IEnumerable&lt;ServiceVisitViewModel&gt; GetAllServiceVisitsNew()
  {
      var _visits = _db.service_visit_tbs.Select(o =&gt; new ServiceVisitViewModel
      {
          service_visit = o,
          client = (o.client_type ? _db.corporate_client_tbs.Where(c=&gt; c.id == o.client_id).First().company_name : _db.retail_client_tbs.Where(c=&gt; c.id == o.client_id).First().name)
      });
      _db.Log = new DebuggerWriter();
      return _visits;
  }
  
  public ActionResult Index()
  {
      var _visits = _db.GetAllServiceVisitsNew();
      return View(_visits());
  }
  </code></pre>
  
  <p>Guess that decides it. Thanks to everyone for the help. I am marking Jon as correct answer since his approach of doing everything on database side is what brought home the bacon. 
  Many thanks to anyone and everyone who took the trouble to reply.</p>
  
  <p><b> End Edit </b></p>