LINQ Select Dynamic Columns and Values
<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>