title: Dynamic linq query with multiple/unknown criteria
body: <p>I am looking to implement a system whereby a use that 'build' conditions and then return the resulting data back from the database. At present, there is a stored procedure which generates SQL on the fly and executes it. This is a particular issue that I want to remove.</p>
  
  <p>My problem is coming from the fact that I can have multiple fields within my criteria, and for each of these fields, there could be 1 or more values, with different potential operators. </p>
  
  <p>For example,</p>
  
  <pre><code>from t in Contacts 
  where t.Email == "email@domain.com" || t.Email.Contains ("mydomain")
  where t.Field1 == "valuewewant"
  where t.Field2 != "valuewedontwant"
  select t
  </code></pre>
  
  <p>The field, criteria and operator are stored in the database (and <code>List&lt;FieldCriteria&gt;</code>) and would be some thing like this (based on above);</p>
  
  <pre><code>Email, Equals, "email@domain.com"
  Email, Contains, "mydomain" Field1,
  Equals, "valuewewant" Field2,
  DoesNotEqual, "valuewedontwant"
  </code></pre>
  
  <p>or</p>
  
  <pre><code>new FieldCriteria
  {
  FieldName = "Email",
  Operator = 1, 
  Value = "email@mydomain.com"
  }
  </code></pre>
  
  <p>So using the information that I have, I want to be able to build a query with any number of conditions. I have seen previous links to Dynamic Linq and PredicateBuilder, but am not able to visualise this as a solution to my own problem.</p>
  
  <p>Any suggestions would be appreciated.</p>
  
  <p>Update</p>
  
  <p>Following on from the suggestion about Dynamic Linq, I came up with a very basic solution, using a Single Operator, with 2 Fields and multiple Criteria. A little crude at the moment as coded in LinqPad, but the results are exactly what I wanted;</p>
  
  <pre><code>enum Operator
  {
      Equals = 1,
  }
  
  class Condition
  {
      public string Field { get; set; }
      public Operator Operator { get; set;}
      public string Value { get; set;}
  }
  
  void Main()
  {
      var conditions = new List&lt;Condition&gt;();
  
      conditions.Add(new Condition {
          Field = "Email",
          Operator = Operator.Equals,
          Value = "email1@domain.com"
      });
  
      conditions.Add(new Condition {
          Field = "Email",
          Operator = Operator.Equals,
          Value = "email2@domain.com"
      });
  
      conditions.Add(new Condition {
          Field = "Field1",
          Operator = Operator.Equals,
          Value = "Chris"
      });
  
      var statusConditions = "Status = 1";
  
      var emailConditions = from c in conditions where c.Field == "Email" select c;
      var field1Conditions = from c in conditions where c.Field == "Field1" select c;
  
  
      var emailConditionsFormatted = from c in emailConditions select string.Format("Email=\"{0}\"", c.Value);
      var field1ConditionsFormatted = from c in field1Conditions select string.Format("Field1=\"{0}\"", c.Value);
  
      string[] conditionsArray = emailConditionsFormatted.ToArray();
      var emailConditionsJoined = string.Join("||", conditionsArray);
      Console.WriteLine(String.Format("Formatted Condition For Email: {0}",emailConditionsJoined));
  
      conditionsArray = field1ConditionsFormatted.ToArray();
      var field1ConditionsJoined = string.Join("||", conditionsArray);
      Console.WriteLine(String.Format("Formatted Condition For Field1: {0}",field1ConditionsJoined));
  
  
  
      IQueryable results = ContactView.Where(statusConditions);
  
      if (emailConditions != null)
      {
          results = results.Where(emailConditionsJoined);
      }
  
      if (field1Conditions != null)
      {
          results = results.Where(field1ConditionsJoined);
      }
  
      results = results.Select("id");
  
      foreach (int id in results)
      {
          Console.WriteLine(id.ToString());
      }
  }
  </code></pre>
  
  <p>With an SQL generated of;</p>
  
  <pre><code>-- Region Parameters
  DECLARE @p0 VarChar(1000) = 'Chris'
  DECLARE @p1 VarChar(1000) = 'email1@domain.com'
  DECLARE @p2 VarChar(1000) = 'email2@domain.com'
  DECLARE @p3 Int = 1
  -- EndRegion
  SELECT [t0].[id]
  FROM [Contacts].[ContactView] AS [t0]
  WHERE ([t0].[field1] = @p0) AND (([t0].[email] = @p1) OR ([t0].[email] = @p2)) AND ([t0].[status] = @p3)
  </code></pre>
  
  <p>And Console Output:</p>
  
  <pre><code>Formatted Condition For Email: Email="email1@domain.com"||Email="email2@domain.com"
  Formatted Condition For Field1: Field1="Chris"
  </code></pre>
  
  <p>Just need clean this up and add the other Operators and it is looking good.</p>
  
  <p>If anyone has any comments on this so far, any input would be appreciated</p>