title: ASP.NET Web API: Perform Search on Table using HTTP GET Method and Linq to SQL Dynamically
body: <p>I have a table that keeps track of Agreements that I wish to build a Web API around. I would like to be able to use a GET method to be able to search the table. However, I want the parameters to be optional. For example, if I use a GET call and specify <strong>Date == 1/23/2018</strong> as well as <strong>Status == Active</strong> I would like it to return all agreements that meet that criteria even though the other possible parameters may equal null.</p>
  
  <p>I am using binding on the controller to pull from the Uri that expects an Agreement like object. This is simply the data context for the Table Agreement. I've been able to get this to work. The issue comes with the query. If the user just specifies two of the variables the rest of the parameters are set to null. </p>
  
  <p>How would I write the linq to SQL query so that only the non null parameters are used to query? Something to the effect of parsing through possible parameters and then building the query off of the non null values.</p>
  
  <p>Note: I do not want the client to have to specify a value for all parameters. Just the ones relevant to them and then have the controller/ service class sort it out.</p>
  
  <p><strong>Controller</strong></p>
  
  <pre><code>namespace API.Controllers
  {
      public class AgreementsController : ApiController
      {
          private AgreementRepository agreementRepository;
  
          public AgreementsController()
          {
              this.agreementRepository = new AgreementRepository();
          }
  
          public AGREEMENT[] Get([FromUri]AGREEMENT Agreement)
          {
              return agreementRepository.GetAgreement(Agreement);
          }
      }
  }
  </code></pre>
  
  <p><strong>Service</strong></p>
  
  <pre><code>namespace API.Services
  {
      public class AgreementRepository
      {
          private DatabaseEntities db = new DatabaseEntities();
  
          public AGREEMENT[] GetAgreement(AGREEMENT agmt)
          {
              var query = from a in db.AGREEMENTS select a;
              query.Where(a =&gt; ??????);
              AGREEMENT[] agreements = query.ToArray&lt;AGREEMENT&gt;();
  
              return agreements;
          }
      }
  }
  </code></pre>
  
  <p><strong>EDIT</strong>
  I would like to find a way to avoid hard coding parameter values and would rather have the query be built dynamically based upon available parameters. This way changes to the table and/or the data context will directly be reflected and handled appropriately by this code. </p>
  
  <p><strong>EDIT 2</strong>
  Ive taken a crack at this using reflection to build a dictionary and the attempt to build queries dynamically by looping through the dictionary. The query below appears to not be valid as every agreement is being returned.  </p>
  
  <pre><code>public AGREEMENT[] GetAgreement(AGREEMENT agmt)
  {
      Type type = agmt.GetType();
      PropertyInfo[] properties = type.GetProperties();
      Dictionary&lt;string, object&gt; propDictionary = new Dictionary&lt;string, object&gt;();
      foreach (PropertyInfo property in properties)
      {
          var propertyValue = property.GetValue(agmt, null);
  
          if (propertyValue != null)
          {
              propDictionary.Add(property.Name, propertyValue);
          }
      }
  
      var query = from a in db.AGREEMENTS select a;
  
      foreach (var prop in propDictionary)
      {
          query.Where(A =&gt; A.GetType().GetProperty(prop.Key).GetValue(A,null) == prop.Value);
      }
  
      AGREEMENT[] agreements = query.ToArray&lt;AGREEMENT&gt;();
  
      return agreements;
  }
  </code></pre>