CODEWITHSUNDEEP
c#entity-frameworklinqasp.net-web-apilinq-to-entities
Imsopov
Imsopov

Reputation: 155

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

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;

    }

Upvotes: 3

Views: 3466

Answers (3)

josibu
josibu

Reputation: 668

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.

Upvotes: 0

Theo
Theo

Reputation: 473

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)

Upvotes: 2

Vecchiasignora
Vecchiasignora

Reputation: 1315

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?

Upvotes: 1

Related Questions