title: Most efficient way to query a large amount of data using C# and Linq
body: <p>I have a web application (ASP.NET 4.0 in c#) and I am building a pseudo Adhoc component.  This component allows the user to select from a list of predetermined categories (Car Make, Care Model, Car Accessories, etc).  The user can select as many categories as they wish and then they press a "Filter" button which will take the categories that are selected and send them to a new form with the options of each category.  Each category can have multiple options.</p>
  
  <p>Example:
  User selects Car Color, Car accessories.</p>
  
  <p>Filter page shows a form with a drop down pre-populated with colors for Car Color.  Form shows a check box for Satellite Radio, another check box for Leather Interior, etc.  All of these values are predefined.  Once a user specifies all of the items they can filter upon, I am using Linq to query the database.  As it is now, the Linq query is against a View that currently holds 14 Left Outer Joins and, if not filtered at all, returns over 30,000 rows.</p>
  
  <p>I am using dynamic linq to query to database link so:</p>
  
  <pre><code>var data = CMS.Model.Adhoc.GetData().AsQueryable(); //must be AsQueryablefor Dynamic Linq to function
  
      public static List&lt;ViewADHOCContractInfo&gt; GetData()
      {
          PortalDataContext db = new PortalDataContext(AuthenticatedUser.ConnectionString);
  
          return db.ViewADHOCInfos.Select(x=&gt;x).Distinct().ToList();
      }
  </code></pre>
  
  <p>NOTE that GetData() is returning all rows and all columns of the result set, which I do not think is the most efficient way to do this, more on that at the bottom.</p>
  
  <p>From there I am performing a FOREACH statement on the filters that were sent in, and minimizing the result set on each iteration like so (abbreviated):</p>
  
  <pre><code>foreach (Filters filter in filters.OrderBy(x=&gt;x.strOrderNumber))
          {
              SanatizeFilter(filter.strFilter,filter);
              if (filter.strValue != string.Empty &amp;&amp; filter.strValue != null)
              {
                  if (filter.strOperator == "Contains")
                  {
                      data = data.Where(filter.strFilter + "!= NULL and " + filter.strFilter + ".Contains(@0)", filter.strValue).
                          Select(x =&gt; x).ToList().AsQueryable();
                  }
                  else
                  {
                      data = data.Where(filter.strFilter + FormatOperator(filter.strOperator) + "@0", filter.strValue).
                          Select(x =&gt; x).ToList().AsQueryable();
  
  
                  }
              }
          }
  </code></pre>
  
  <p>All of this works fine, however it is slow.  My first question is, how can I use Linq to cut this down a bit.  I have tried a few ways to pull only the data that comes from the filter into the variable "data" as a way to pre-populate, however I can't get that to work with the .AsQueryable().  I would like to be able to get the column identifiers from the filter objects and select only those from the database so that I don't have to deal with all of the overhead, but I can't find a solution that will allow me to do this, any ideas?</p>
  
  <p>Second Question is right now this is all in a view in the database, would it be faster/more efficient to move it to a function or a stored procedure?  Can Linq interact with a stored procedure?</p>
  
  <p>Thanks in advance</p>