Linq to sql/C# - multiple where search off key/value pairs

Question

I'm new to Linq to Sql and am trying to do the following in C#.

I have a table that's a key/value store (that also has an fk to a user). I want to do a search off the key names and values as an or. On the front end, I allow them to add "filters" that are key names, then they can search for a value. So they can search N items, where N is the number of filters.

In plain sql, where N=3, it'd look like the following. datakey and datavalue are columns in the table (varchar(255)).

SELECT * from table
 where (datakey='option1' and datavalue='value1')
    or (datakey='option2' and datavalue='value2')
    or (datakey='option3' and datavalue='value3')

Answer 1

Use C#'s equivalent operators, && and ||:

from t in context.table
where (t.datakey == "option1" && datavalue == "value1")
   || (t.datakey == "option2" && datavalue == "value2")
   || (t.datakey == "option3" && datavalue == "value3")
select t

Answer 2

Theres a technology called DynamicQuery which would allow you to build up LINQ expressions via a string, which you may find helpful

Answer 3

Yes, as jasper said you can use the Dynamic LINQ Query Library to accomplish what you want.

Here is a quick and dirty example (note: I have not tested this exact code):

List<string> options = new List<string>();
List<string> values = new List<string>();

options.Add("option1");
values.Add("value1");
options.Add("option2");
values.Add("value2");

StringBuilder queryString = new StringBuilder();

for (int i = 0; i < options.Count; i++)
{
    if (queryString.Length > 0)
        queryString.Append(" Or ");

    queryString.Append(string.Format("(datakey = \"{0}\" And dataValue = \"{1}\")", options[i], values[i]));
}

var query = context.YourTable.Where(queryString.ToString());