ASP.NET Web API: Perform Search on Table using HTTP GET Method and Linq to SQL Dynamically

Question

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 Date == 1/23/2018 as well as Status == Active I would like it to return all agreements that meet that criteria even though the other possible parameters may equal null.

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.

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.

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.

Controller

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);
        }
    }
}

Service

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 => ??????);
            AGREEMENT[] agreements = query.ToArray<AGREEMENT>();

            return agreements;
        }
    }
}

EDIT 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.

EDIT 2 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.

public AGREEMENT[] GetAgreement(AGREEMENT agmt)
{
    Type type = agmt.GetType();
    PropertyInfo[] properties = type.GetProperties();
    Dictionary<string, object> propDictionary = new Dictionary<string, object>();
    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 => A.GetType().GetProperty(prop.Key).GetValue(A,null) == prop.Value);
    }

    AGREEMENT[] agreements = query.ToArray<AGREEMENT>();

    return agreements;
}

Does your reflection solution work if you actually save the result of `query.Where(...)` back into `query`? — npearson, Jan 27 '18 at 00:57
You can create the query at run-time using methods of `Expression`. **But** keep in mind, in a real world application, the criteria for each field can be different, for example for a string property you may need `StartsWith`, for another one you may need `Contains`, for an int property you may need range using `>=` and/or `<=`, for another int property you may want to check for exact value and so on. — Reza Aghaei, Jan 28 '18 at 12:32
Instead of trying to write a general purpose logic, consider creating a specific search logic. You can see good examples around, for example: [Filter/Search using Multiple Fields - ASP.NET MVC](https://stackoverflow.com/q/33153932/3110834) or [Filtering the data at the controller before it is rendered in a view](https://stackoverflow.com/q/34343635/3110834). — Reza Aghaei, Jan 28 '18 at 12:33

score 2 · Answer 1 · answered Jan 23 '18 at 18:42

2

You can do a null check for each optional parameter e.g:

var query = from a in db.AGREEMENTS select a;

query = query.Where(a => a.something == mandatoryParamter);

if(optionalParameter1 != null)
{
    query = query.Where(a => a.something == optionalParameter1);
}

if(optionalParameter2 != null)
{
    query = query.Where(a => a.something == optionalParameter2);
}

etc.

answered Jan 23 '18 at 18:42

SBFrancies

3,987
2
14
37

1) Does this append to the query every time or overwrite? 2) I was hoping for something cleaner than coding out every parameter (there's 40). Though I understand this may be wishful thinking..Thanks for the input! – LCaraway Jan 23 '18 at 19:06
It appends to the query each time. – SBFrancies Jan 23 '18 at 19:08

score 2 · Answer 2 · answered Jan 24 '18 at 11:28

You can add a where clause like below if nothing has been supplied then optional paremater will be null.

var query = from a in db.AGREEMENTS select a;

query = query.Where(a => a.something == mandatoryParamter)
    .Where(optionalParameter1 == null ||
           a => a.something == optionalParameter1)
    .Where(optionalParameter2 == null ||
           a => a.something == optionalParameter2);

score 1 · Accepted Answer · answered Jan 29 '18 at 08:15

From my point of view the easiest and most powerful approach would be to use OData. You can get started at https://learn.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options

If you want to only apply filter and then display and format results in custom way, I would recommend "Invoking Query Options Directly" section from reference above.

score 0 · Answer 4 · answered Jan 25 '18 at 22:18

You can use Nullable Type operator and run query with the optional parameter:

    public AGREEMENT[] GetAgreement(AGREEMENT agmt)
    {
        var query = from a in db.AGREEMENTS select a;
        query.Where(a => a.col1 ?? default(col1 Type) && a.col2 ?? default(col2 Type));
        AGREEMENT[] agreements = query.ToArray<AGREEMENT>();

        return agreements;
    }

Here you can add as many parameters as you want to use. I hope this helps.

Reza Aghaei · Answer 5 · 2019-08-06T08:32:12.287

You can create the query at run-time using methods of Expression class. The core logic of the answer would be:

Create the IQueryable<Model> result = dbContext.Models.AsQueryable();
Detect which properties should participate in query, using keys which exists in query string or route values if you use them.

For each property which should be included in criteria:

result = result.Where(x=> create a criteria for the property);

At last, return result or result.ToList() or whatever type which you need as result.

Important Note - Before jumping to the example

Before reading the example, please keep in mind:

In a real world application, the criteria for each field can be different, for example for a string property you may need StartsWith, for another one you may need Contains, for an int property you may need range using >= and/or <=, for another int property you may want to check for exact value and so on.
Instead of trying to write a general purpose logic, consider creating a specific search logic. You can see good examples around, for example:
- Filter/Search using Multiple Fields - ASP.NET MVC
- Filtering the data at the controller before it is rendered in a view
If the number of properties and number of entity classes are too much, what you can do to facilitate the development, is generating the search logic using code generation (for example using T4 templates) at design/coding time. Then you can change them simply to what you need later.

Example

In the following example, I've implemented a search method which allows you to search between products, based on the passed values using query string. You may want to include route values as well.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Net.Http;
using System.Web.Http;
using WebApiTest.Models;
public class ProductsController : ApiController
{
    [HttpGet]
    public IEnumerable<Product> Search([FromUri] Product product)
    {
        var parameters = Request.GetQueryNameValuePairs().Select(x => x.Key.ToLower());
        using (var db = new TestDBEntities())
        {
            var result = db.Products.AsQueryable();
            foreach (var property in typeof(Product).GetProperties())
            {
                if (parameters.Contains(property.Name.ToLower()))
                {
                    var x = Expression.Parameter(typeof(Product), "x");
                    var propertyExpression = Expression.Property(x, property.Name);
                    var valueExpression = Expression.Convert(
                        Expression.Constant(property.GetValue(product)), 
                            property.PropertyType);
                    var criteria = Expression.Equal(propertyExpression, valueExpression);
                    var lambda = Expression.Lambda<Func<Product, bool>>(criteria, x);

                    result = result.Where(lambda);
                }
            }
            return result.ToList();
        }
    }
}

ASP.NET Web API: Perform Search on Table using HTTP GET Method and Linq to SQL Dynamically

5 Answers5

Important Note - Before jumping to the example