Creating Dynamic Search Queries with Entity Framework Web API using passed in parameters as the search fields

Question

Hi I would like to create a dynamic search query using entity with values from the URI to be the search fields.

The code below works but some of the tables I have to search on have over 200 fields and I would like to create something that would take the property name and allow me to search on it.

It also can have multiple search options, for example surname and given name and Date of birth

queryable = queryable.Where(x => x.<PROPERTY.NAME>.Contains(query.<PROPERTY.NAME>));

Is this possible?

This is my code so far.

public List<EMPLOYEE> Get([FromUri] EMPLOYEE query)
    {

        List<EMPLOYEE> emps = new List<EMPLOYEE>();
        var db = AuthHandler.Ent;
        var queryable = db.EMPLOYEES.AsExpandable();

        foreach (var prop in query.GetType().GetProperties())
        {

            if (prop.GetValue(query, null) != null)
            {

                switch (prop.Name)
                {
                    case "EMP_CREATIONDATE":
                        queryable = queryable.Where(x => x.EMP_CREATIONDATE.Equals(query.EMP_CREATIONDATE));
                        break;
                    case "EMP_SURNAME":
                         queryable = queryable.Where(x => x.EMP_SURNAME.Contains(query.EMP_SURNAME));
                        break;
                    case "EMP_GIVENNAMES":
                        queryable = queryable.Where(x => x.EMP_GIVENNAMES.Contains(query.EMP_GIVENNAMES));
                        break;
                }
                queryable = queryable.Where(x => x.EMP_SURNAME.Contains(query.EMP_SURNAME));

            }
        }
        emps = queryable.ToList();

        return emps;

    }

I've just had a look, I've also updated my original post. The examples I have looked at for Odata don't seem to have any examples of dynamic field selection? Is it possible or am I just looking at the wrong examples? I also need multiple fields available for the search which currently works in my code, but I don't want a 200+ switch-case in my code — Imsopov
– Imsopov, Commented Sep 5, 2017 at 6:37
Well, usually you would not need to do it like this, when creating an api it is better to keep in mind separation of concerns and limit what the endpoint gives you. One size fits all is not a very good approach. I would create specific endpoints with proper routing to achieve what you're trying here. — Alin
– Alin, Commented Sep 5, 2017 at 6:40

Theo · Accepted Answer · 2017-09-06 00:13:49Z

I will use a predicate builder like this

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

And use it like this :

public IQueryable<Customer> GetCustomers(CustomerOrderSearchParameters parameters)
        {    
                context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                var predicate = PredicateBuilder.True<Customer>();
                if (!string.IsNullOrEmpty(parameters.FirstName))
                {
                    predicate = predicate.And(x => x.FirstName.Contains(parameters.FirstName));
                }
                if (!string.IsNullOrEmpty(parameters.LastName))
                {
                    predicate = predicate.And(x => x.LastName.Contains(parameters.LastName));
                }
                if (!string.IsNullOrEmpty(parameters.Email))
                {
                    predicate = predicate.And(x => x.email.Contains(parameters.Email));
                }
                if (!string.IsNullOrEmpty(parameters.PhoneNumber))
                {
                    predicate = predicate.And(x => x.MobilePhone.Contains(parameters.PhoneNumber) || x.HomePhone.Contains(parameters.PhoneNumber));
                }
                if (parameters.BrandID != null)
                {
                    predicate = predicate.And(x => x.Logins.Where(l => l.BrandID == parameters.BrandID).Any());
                }
                if (parameters.ShowNoOrders == true)
                {
                    predicate = predicate.And(x => x.Orders.Where(o => o.CustomerID != x.CustomerID).Any());
                }                
                return context.Customers.AsExpandable().Where(predicate);           
        }

Also the CustomerOrderSearchParameters is just a list of simple parameters (equivalent of what you will get from your query)

Vecchiasignora · Accepted Answer · 2017-09-05 08:59:50Z

1

You can use ObjectQuery class and Sql operators to build your Dynamic query, something like this

public List<EMPLOYEE> Get([FromUri] EMPLOYEE query)
        {

            List<EMPLOYEE> emps = new List<EMPLOYEE>();
            var db = AuthHandler.Ent;
            var queryable = (ObjectQuery<EMPLOYEE>)db.EMPLOYEES.AsExpandable();

            string condition = "CONTAINS(@column, @search)";

            foreach (var prop in query.GetType().GetProperties())
            {
                var value = prop.GetValue(query, null);
                if (value != null)
                {
                    queryable = queryable.Where(string.Format(condition, prop.Name, value));
                }
            }
            emps = queryable.ToList();

            return emps;

        }

am i right?

answered Sep 5, 2017 at 8:59

Vecchiasignora

1,3157 silver badges6 bronze badges

2 Comments

Imsopov Over a year ago

I'm getting the error {"Unable to cast object of type 'LinqKit.ExpandableQueryOfClass1[WebApplication1.EMPLOYEE]' to type 'System.Data.Entity.Core.Objects.ObjectQuery1[WebApplication1.EMPLOYEE]'."} I also tried var queryable = (ObjectQuery<EMPLOYEE>)db.EMPLOYEES.AsQueryable(); But this would also not let me cast.

Imsopov Over a year ago

Unable to cast object of type 'System.Data.Entity.DbSet1[WebApplication1.EMPLOYEE]' to type 'System.Data.Entity.Core.Objects.ObjectQuery1[WebApplication1.EMPLOYEE]

josibu · Accepted Answer · 2023-03-11 14:22:19Z

I know this threat is old and there are probably already a ton of libraries out there which you could use for it but it might still help someone:

[ApiController]
public class HrController : Controller
{
    /*Housekeeping stuff*/

    [HttpGet]
    [Produces("application/json")]
    [Route("api/[controller]/[action]/")]
    public async Task<IActionResult> Persons()
    {
        List<Person> ppr = await _context.People
            .AsNoTracking().ToListAsync();

        if (HttpContext.Request.Query != null)
        {
            foreach (var query in HttpContext.Request.Query)
            {
                if (typeof(PeoplePoolResource).GetProperty(query.Key) != null)
                {
                    if (query.Value.First() == null) continue;
                    if (query.Value.First().StartsWith('@')) //wildcard search
                    {
                        ppr = ppr.Where(a => a.GetType().GetProperty(query.Key).GetValue(a) != null &&
                            a.GetType().GetProperty(query.Key).GetValue(a).ToString().Contains(query.Value.First().Substring(1), System.StringComparison.OrdinalIgnoreCase)).ToList();
                        continue;
                    }
                    if (typeof(PeoplePoolResource).GetProperty(query.Key).PropertyType == typeof(int))
                    {
                        ppr = ppr.Where(a => (int)a.GetType().GetProperty(query.Key).GetValue(a) == int.Parse(query.Value)).ToList();
                    }
                    else if (typeof(PeoplePoolResource).GetProperty(query.Key).PropertyType == typeof(bool))
                    {
                        ppr = ppr.Where(a => (bool)a.GetType().GetProperty(query.Key).GetValue(a) == bool.Parse(query.Value)).ToList();
                    } //add other types like double
                    else
                    {
                        ppr = ppr.Where(a => a.GetType().GetProperty(query.Key).GetValue(a) != null &&
                            a.GetType().GetProperty(query.Key).GetValue(a).ToString().Equals(query.Value, System.StringComparison.OrdinalIgnoreCase)).ToList();
                    }
                }
            }
        }

        return Json(ppr);
    }
}

You can then send a get request with queries like so:

http://localhost:50409/api/hr/persons?Id=252 (this gets you the person with Id 252)

http://localhost:50409/api/hr/persons?FirstName=@John (this gets you all persons containing the letters John (case insensitive) in their FirstName field

Pros:

Extremely generic
Could be expanded to accept regex as well
Could easily be changed to a totally generic function which takes in a List<T> and returns a List<T>

Cons:

This approach is not very performance intensive
Has limitations, e.g. you cannot search using greater than or smaller than etc., which could be added though
The search is a logical AND search and cannot be changed to a logical OR or others.

Collectives™ on Stack Overflow

Creating Dynamic Search Queries with Entity Framework Web API using passed in parameters as the search fields

3 Answers 3

Comments

2 Comments

Pros:

Cons:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Pros:

Cons:

Comments

Your Answer

Sign up or log in

Post as a guest

Related