Build query dynamically based on request object properties

Question

I have a web app where users can get records from the database based on certain filtering criteria. They can get all records, or all records between two dates, or all records of a particular entity type, or all records for a particular user etc.. I'm using EF6 and just standard DbContext classes to query the DB

What I'm trying to do is build a query dynamically based on the request object and what properties aren't null. So if a user selects a from and to date, but no other criteria, the query should go off to the db and get all records between said dates. if the user doesn't select dates but an entity type, then the query should just filter on that property of the request object.

So in pseudo code, how would i do this when building the query

Check all request object properties
Which ever properties aren't null, add them to the query expression as a parameter

Here's my request object

public class ChangeLogRequestDto
{
    public DateTime? FromDate { get; set; }
    public DateTime? ToDate { get; set; }
    public string UserName { get; set; }
    public string CallCenter { get; set; }
    public string EntityType { get; set; }
    public string PropertyName { get; set; }
    public string CompanyId { get; set; }
}

And here's my Service method to take in this request object and build the query to get the records

 public IEnumerable<ChangeLog> GetChangeLogWithFilter(ChangeLogRequest request)
    {
       // check the request object and build a query based on its values.
       return dbContext.ChangeLogs.Where(query)
    }

I've looked up other examples but given I'm unfamiliar with Expressions and Linq they're not making sense to me. Any ideas?

It will be easier to answer if you show an example of how you actually query the DB, like whether you use raw SQL, a stored procedure, or Linq (for an ORM), or .. — stuartd, Oct 31 '18 at 14:42
Possible duplicate of [Creating dynamic queries with entity framework](https://stackoverflow.com/questions/5541234/creating-dynamic-queries-with-entity-framework) — Rui Jarimba, Oct 31 '18 at 14:45
@stuartd I'm using EF6 and have been using straight forward linq queries like this up till now. return dbContext.ChangeLogs.Where(c=> c.ID == request.Id); — Del Habington, Oct 31 '18 at 14:50
Linq != Lambda. They can produce the same results, but they are written differently. `.Where()` is a Lambda. — Erik Philips, Oct 31 '18 at 16:36

Karl Merecido · Accepted Answer · 2018-10-31T16:50:51.487

here is an example of how I would do it (based on what I think you are trying to do)

public IEnumerable<ChangeLog> GetChangeLogWithFilter(ChangeLogRequest request)
{
   var query = dbContext.ChangeLogs;
   if(request.FromDate != null && request.ToDate != null)
   {
      query = query.Where(x => x.DateSubmitted > request.FromDate && x.DateSubmitted <= request.ToDate);
   }

   if(!string.IsNullOrEmpty(request.UserName))
   {
      query = query.Where(x => x.UserName.Equals(request.UserName));
   }
   // then you repeat the above with the rest of the values if they are available
   return query.ToList();
}

hopefully that will take care of your use case. There are more advanced ways to going about building a query, such as expression trees, but I think for the above requirements, you should be able to get by with this.

score 0 · Answer 2 · answered Oct 31 '18 at 20:55

Hi Del I would strongly advise you to use a standard pattern like OData (Open Data Protocol):

This will allow you to perform dynamic queries to your OData endpoints, for example accessing to elements details, sorting the lists, and applying filters like what you want, all of that directly from your HTTP request.

Hope it helps, and please let me know if you need any extra help or clarification

Build query dynamically based on request object properties

2 Answers2