title: Working With Temporary Tables in ASP.NET MVC
body: <h2>Background:</h2>
  
  <p>I am currently writing a web front for analysis of our web logs which get inserted into SQL Server.</p>
  
  <p>One of the most basic features is that it you can search for a set of results by specifying a time frame (This is generally a tool for troubleshooting recent traffic, not deep data mining) and then various other fields, such has the ClientIP, Uri, etc.</p>
  
  <p>I originally did this with LINQ, but started to get annoyed with it and decided for me it would be easier to construct queries wtih StringBuilder and use <a href="http://code.google.com/p/dapper-dot-net/" rel="nofollow">Dapper.NET</a>. So I still se the dmbl generated for my table (there are actually 31 tables, one for each day, but a view brings them together). And then create queries in my controller that go something like:</p>
  
  <pre><code>public ActionResult Index(SearchParams sp)
  {
  
      var db = new LogTableDataContext();
      var query = new StringBuilder();
      ...
      var fields = " Time, ClientIp, Method, Uri, Bytes, Server, Tr, TermState, UserAgent, Host ";
      query.Append(" SELECT ");
  
      if (sp.top.HasValue)
          query.Append(" Top ").Append(sp.top);
      query.Append(fields);
      query.Append(" from Logs ");
  
      query.Append(" WHERE 1=1 ");
      if (wherestrings.HasValue())
          query.Append(wherestrings);
      var resultsModel = new UberRows { rows = results, generated_query = query.ToString(), query_params = sp.GetPropertyNamesAndValues() };
      return View(resultsModel);
  </code></pre>
  
  <p>Where WHERE clauses are generated based on the GET parameters passed to the controller, something like:</p>
  
  <pre><code>public string GenerateWhereString()
  {
      var wherestring = new StringBuilder();
      if (this.Uri.HasValue())
          wherestring.Append(" AND Uri = @Uri ");
      if (this.ClientIp.HasValue())
          wherestring.Append(" AND ClientIp = @ClientIP ");
      if (this.Server.HasValue())
  </code></pre>
  
  <p>UberRows simply has <code>public List&lt;HAProxyLogViewer.Log&gt; rows;</code> and I just render the results in div with jquery and the DataTable plugin since the result sets should generally be small.</p>
  
  <h2>Now, to my question:</h2>
  
  <p>What I would also like to have his a bunch of queries that query the result set of the above search (How many hits to each Server, Average Response Time, etc).</p>
  
  <p>I am guessing that the way to do this would be create a temp table with the results (since if I didn't I would have to generate the base results for each of those queries on things like response time for the result set), but since I am new to that, c#, and asp.mvc I am not sure in either the big picture of how that would work and be tied into MVC, my ORM, and the dmbl classes. Assuming I am correct about the temp table, how would I work with that?</p>
  
  <p>So the pure SQL would be something like:</p>
  
  <pre><code>SELECT Time, ClientIp, Method, Uri, Bytes, Server, Tr, TermState, UserAgent, Host
  INTO #resultsTable 
  FROM Logs
  WHERE 1=1 AND Time BETWEEN dateadd(minute, -1440, getutcdate()) and getutcdate()
  AND UserAgent = 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)';
  
  SELECT Time, ClientIp, Method, Uri, Bytes, Server, Tr, TermState, UserAgent, Host
  FROM #resultsTable;
  
  SELECT Server, COUNT(*)
  FROM #resultsTable
  GROUP BY Server;
  
  SELECT AVG(Tr)
  FROM #resultsTable;
  
  DROP TABLE #resultsTable;
  </code></pre>