title: LINQ Select Dynamic Columns and Values
body: <p>For various reasons I need to be able to allow the user to select an item from a database based on their choice of columns and values. For instance, if I have a table:</p>
  
  <pre><code>Name   | Specialty       | Rank
  -------+-----------------+-----
  John   | Basket Weaving  | 12
  Sally  | Basket Weaving  | 6
  Smith  | Fencing         | 12
  </code></pre>
  
  <p>The user may request a 1, 2, or more columns and the columns that they request may be different. For example, the user may request entries where <code>Specialty == Basket Weaving</code> and <code>Rank == 12. What I do currently is gather the user's request and create a list of</code>KeyValuePair<code>where the</code>Key<code>is the column name and the</code>Value` is the desired value of the column:</p>
  
  <pre><code>class UserSearch
  {
      private List&lt;KeyValuePair&lt;string, string&gt; criteria = new List&lt;KeyValuePair&lt;string, string&gt;&gt;();
  
      public void AddTerm(string column, string value)
      {
          criteria.Add(new KeyValuePair&lt;string, string&gt;(column, value);
      }
  
      public void Search()
      {
          using (var db = new MyDbContext())
          {
              // Search for entries where the column's (key's) value matches
              // the KVP's value.
              var query = db.MyTable.Where(???);
          }
      }
  }
  
  /* ... Somewhere else in code, user adds terms to their search 
   * effectively performing the following ... */
  UserSearch search = new UserSearch();
  search.Add("Specialty", "Basket Weaving");
  search.Add("Rank", "12");
  </code></pre>
  
  <p>Using this list of <code>KeyValuePair</code>'s, how can I most succinctly select database items which match all the criteria?</p>
  
  <pre><code>using (var db = new MyDbContext)
  {
      // Where each column name (key) in criteria matches 
      // the corresponding value in criteria.
      var query = db.MyTable.Where(???);
  }
  </code></pre>
  
  <p>EDIT: I would like to use EntityFramework instead of raw SQL if I can help it.</p>
  
  <p><strong>UPDATE 3</strong>: I am getting closer. I have discovered a way to use LINQ once I've downloaded
  all the values from the table. This is obviously not super ideal because it downloads
  everything in the table. So I guess the last step would be to figure out a way where 
  I don't have to download the whole table every time. Here is an explanation of what I am doing: </p>
  
  <p>For every row in the table </p>
  
  <pre><code>db.MyTable.ToList().Where(e =&gt; ...
  </code></pre>
  
  <p>I make a list of bools representing if the column matches the criteria.</p>
  
  <pre><code>criteria.Select(c =&gt; e.GetType()?.GetProperty(c.Key)?.GetValue(e)?.ToString() == c.Value)
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                           Basically just gets the value of specific column
                                              by string
  </code></pre>
  
  <p>Then I check to see if this bool list is all true</p>
  
  <pre><code>.All(c =&gt; c == true)
  </code></pre>
  
  <p>An example of the full code is below:</p>
  
  <pre><code>// This class was generated from the ADO.NET Entity Data Model template 
  // from the database. I have stripped the excess stuff from it leaving 
  // only the properties.
  public class MyTableEntry
  {
      public string Name { get; }
      public string Specialty { get; }
      public string Rank { get; }
  }
  
  class UserSearch
  {
      private List&lt;KeyValuePair&lt;string, string&gt; criteria = new List&lt;KeyValuePair&lt;string, string&gt;&gt;();
  
      public void AddTerm(string column, string value)
      {
          criteria.Add(new KeyValuePair&lt;string, string&gt;(column, value);
      }
  
      public async Task&lt;List&lt;MyTableEntry&gt;&gt; Search()
      {
          using (var db = new MyDbContext())
          {
              var entries = await db.MyTable.ToListAsync();
              var matches = entries.Where(e =&gt; criteria.Select(c =&gt; e.GetType()
                                                                    ?.GetProperty(c.Key)
                                                                    ?.GetValue(e)
                                                                    ?.ToString() == c.Value)
                                                        .All(c =&gt; c == true));
  
              return matches.ToList();
          }
      }
  }
  </code></pre>
  
  <p>It seems as if my problem lies with this segment of code:</p>
  
  <pre><code>e.GetType()?.GetProperty(c.Key)?.GetValue(e)?.ToString()
  </code></pre>
  
  <p>I am unfamiliar with Expression trees so perhaps the answer lies in them. I may also try Dynamic LINQ.</p>