Web API dynamic parameters for filtering

You can use OData with your WebAPI controllers to dynamically apply: filter, orderby, select, skip, top(take).

For example:

// your API action
public IQueryable<ExampleDataDTO> Get(ODataQueryOptions opts)
{
    // here is some odata settings to validate query params.
    var settings = new ODataValidationSettings()
    {
        // Initialize settings as needed.
        AllowedFunctions = AllowedFunctions.AllMathFunctions
    };

    // validating parameters
    opts.Validate(settings);

    var yourExampleQuery = // some data from db

    // apply all parameters that came within query to your data
    IQueryable results = opts.ApplyTo(yourExampleQuery.AsQueryable());

    // and return this
    return results as IQueryable<Product>;
}

And your query could be something like: http://localhost/ExampleData?$filter=SomeProperty eq 'SomeValue' $orderby=anotherProperty & $skip=10 & $top=10

All query parameters will be parsed into ODataQueryOptions and applied to your API call result.

Hope it will help.

If I understand your question correctly, you might want to do something like this:

public List<ExampleDataDTO> GetExampleData(int offset = 0, int limit = 0, string name = "", string size = "")
{
    using (var db = new Context())
    {
        db.Configuration.ProxyCreationEnabled = false;
        var exampleQuery = db.example
            .Where((x => x.Name == name || name == "") && // If parameter 'name' has a value, filter on that, else ignore it.
                   (x => x.Size == size || size == "")) // If parameter 'size' has a value, filter on that, else ignore it.
            .AsEnumerable()
            .Select(item =>
            new ExampleDataDTO
            {
                //Selects
            });

        if (offset == 0 && limit == 0)
        {
            return exampleQuery.ToList();
        }
        else
        {
            return exampleQuery.Skip(offset).Take(limit).ToList();
        }
    }
}

Make the parameters of your method optional and use them in the LINQ query for filtering.

I know it's a bit late for the answer on this question but wanted to share my response anyways.

I would like to suggest a different approach and that is to move the logic to DB. By doing it, your code will be more adaptable to changes and will require change only in one place, DB. After all, changes in query shouldn't require you to recompile the code. So here's an example how to implement this approach:

1. You should consider using a model for your controller action to collect parameters for your query. The benefit is that you just need to add more properties to model if down the road you decide to add more filters.

Model:

public class ExampleParams 
{
    public int size { get; set; } = 40; // Could be any number 
    public int page { get; set; } = 1; // you can apply offset here but this should be done on db, see below
    public string q { get; set; } = string.Empty;
}

Controller:

public List<ExampleDataDTO> ExampleData([FromQuery] ExampleParams params)
{
    return DataAccessLayer.GetExampleData(params);
}

This will allow you to have dynamic query params, for example:

localhost/api/ExampleData?q=someString will search by param q

localhost/api/ExampleData?q=someString&page=2 will show page 2 for search param q

localhost/api/ExampleData?q=someString&page=2&size=10 will show 10 items on page 2 for search param q

1. I am assuming you are using MySQL, but if not, still concept will remain the same. Offset and Limit could be bad performers on the large amount of data overall and what you are trying to implement is some sort of pagination which you can tackle in 2 different ways.

   • First one would would great if you know your table won't grow extremely large, thus offset and limit could be applied easily and efficiently, through a property "page" in above model, and limit and offset will be dynamic and calculated on the go.

     -- page and size is what you are passing via model
     SET @page := IFNULL(`page`, 1); 
     SET @size := SELECT CASE WHEN IFNULL(`size`, 40) > 40 THEN 40 ELSE IFNULL(`size`, 40) END); -- 40 is some default max limit
     
     SELECT * FROM elementTable LIMIT @size OFFSET ((@page - 1) * @size)
     

   • Second one would be better option if you know your table grow very large over time in which case you would be implementing more efficient "timestamp_id". For more reference click here:

     -- Given that T is the timestamp and I is the id contained in the token.
     SELECT * FROM elementTable
     WHERE (
       timestampColumn > T 
       OR (timestampColumn = T AND idColumn > I)
     )
     AND timestampColumn < now()
     ORDER BY timestampColumn asc, idColumn asc;
     -- The ids in the idColumn must be unique (out-of-the-box for primary keys)
     -- We need an index on both columns timestampColumn and idColumn
     

2. Now when it comes to filtering by fields, you have few options but scaling would be better with dynamic SQL although tedious to write it. Dynamic SQL would create and cache a different plan for each permutation of the Query parameters, but at least each plan will be 'tailored' to the specific query (it doesn't matter whether it is a PROC or Adhoc SQL - as long as they are parameterized queries, they will be cached)

   SET @SQL = 'SELECT * FROM elementTable WHERE 1 = 1'
   IF (`optionalParam1` IS NOT NULL) then
       SET @SQL = CONCAT(@SQL , ' AND myColumn1 = \'', `optionalParam1`, '\' ' );
   IF (`optionalParam2` IS NOT NULL) then
       SET @SQL = CONCAT(@SQL , ' AND myColumn2 = \'', `optionalParam1`, '\' ' );
   

   • The awful 1 == 1 hack can also be avoided if you keep track of whether any predicates have yet been applied or not, and then conditionally apply the first AND only on the second and subsequent predicates. If there are no predicates at all, then WHERE disappears as well.

Hope this answer your question more completely.