title: Improve performance on LINQ Query
body: <p>My linq query goes slow when I try to loop through the results to create an Xelement, which I later process XSLT based on the XElement. </p>
  
  <p>Here is my code</p>
  
  <pre><code>public override XElement Search(SearchCriteria searchCriteria)
      {
          XElement root = new XElement("Root");
          using (ReportOrderLogsDataContext dataContext = DataConnection.GetLinqDataConnection&lt;ReportOrderLogsDataContext&gt;(searchCriteria.GetConnectionString()))
          {
              try
              {
  
  
                  IQueryable&lt;vw_udisclosedDriverResponsePart&gt; results = from a in dataContext.vw_udisclosedDriverResponseParts
                                where
                                    (a.CreateDt.HasValue &amp;&amp;
                                     a.CreateDt &gt;= Convert.ToDateTime(searchCriteria.BeginDt) &amp;&amp;
                                     a.CreateDt &lt;= Convert.ToDateTime(searchCriteria.EndDt))
                                select a;
  
                  if (!string.IsNullOrEmpty(searchCriteria.AgentNumber))
                  {
                      results = results.Where(request =&gt; request.LgAgentNumber == searchCriteria.AgentNumber);
                  }
                  if (!string.IsNullOrEmpty(searchCriteria.AgentTitle))
                  {
                      results = results.Where(a =&gt; a.LgTitle == searchCriteria.AgentTitle);
                  }
                  if (!string.IsNullOrEmpty(searchCriteria.QuotePolicyNumber))
                  {
                      results = results.Where(a =&gt; a.QuotePolicyNumber == searchCriteria.QuotePolicyNumber);
                  }
                  if (!string.IsNullOrEmpty(searchCriteria.InsuredName))
                  {
                      results = results.Where(a =&gt; a.LgInsuredName.Contains(searchCriteria.InsuredName));
                  }
  
                  foreach (var match in results) // goes slow here, specifically times out before evaluating the first match when results are too large.
                  {
                      DateTime date;
                      string strDate = string.Empty;
                      if (DateTime.TryParse(match.CreateDt.ToString(), out date))
                      {
                          strDate = date.ToString("MM/dd/yyyy");
                      }
  
                      root.Add(new XElement("Record",
                                            new XElement("System", "Not Supported"),
                                            new XElement("Date", strDate),
                                            new XElement("Agent", match.LgAgentNumber),
                                            new XElement("UserId", match.LgUserId),
                                            new XElement("UserTitle", match.LgTitle),
                                            new XElement("QuoteNum", match.QuotePolicyNumber),
                                            new XElement("AddressLine1", match.AddressLine1),
                                            new XElement("AddressLine2", match.AddressLine2),
                                            new XElement("City", match.City),
                                            new XElement("State", match.State),
                                            new XElement("Zip", match.Zip),
                                            new XElement("DriverName", string.Concat(match.GivenName, " ", match.SurName)),
                                            new XElement("DriverLicense", match.LicenseNumber),
                                            new XElement("LicenseState", match.LicenseState)));
                      ;
                  }
              }
              catch (Exception es)
              {
  
                  throw es;
              }
          }
          return root;
          // return GetSearchedCriteriaFromStoredPocedure(searchCriteria);
      }
  </code></pre>
  
  <p>I assume there is a better way to convert the results object into an XElement. Processing the view itself only takes about 2 seconds. Trying to loop through the results object is resulting in a timeout, even when many results are not returned. </p>
  
  <p>Any help would be appreciated.</p>
  
  <p>Thanks!</p>
  
  <p>-James</p>
  
  <p>AMENDED 7/10/2012</p>
  
  <p>The issue is not with the linq query itself but its with the execution of the view when specifying a date range. Executing the view by itself takes about 4-6 seconds. When a small date range (07/05/2012 - 07/10/2012) is used the view takes around 1:30. Does anyone have any suggestions of how to increase performance of the query with a date range specified. Its faster if I got all of the results and looped through them checking the date.</p>
  
  <p>i.e.</p>
  
  <pre><code>    IQueryable&lt;vw_udisclosedDriverResponsePart&gt; results = from a in dataContext.vw_udisclosedDriverResponseParts select a;
  
                  foreach (var match in results) //results only takes 3 seconds to enumerate, before would timeout
                  {
                      // eval search criteria date here.
                  }
  </code></pre>
  
  <p>I can code it like I suggested above, but does anyone have a better way?</p>