LINQ is awesome solution for creating efficient queries with dynamic WHERE conditions

by jasonvonruden 25. January 2011 14:16

I have been using this method for a long time and I just want to share an example of applying dynamic “WHERE” conditions to make more efficient queries.

For example you could create a query using the “LIKE” verb to handle a query with multiple where clauses when you have the potential of having only 1 parameter and you are expecting the other parameter to wildcard.  You will quickly find out that if field that you are wildcarding with just “%” is null you will not match on it when you where expecting a match. This example of applying dynamic “WHERE” conditions totally solves that solution and it is much cleaner code as an added bonus.

Example of just using ‘LIKE’ conditions:

As you can see the query will be less efficient because you are filtering an a column that you may not need to based on the input parameters provided. Must include the function to convert nulls into empty strings, yuck.

SELECT r
FROM XrfReservation r
WHERE ISNULL(r.ReservationProject,'') like :dd and
      ISNULL(r.ReservationName,'') like :di and
ORDER BY r.ReservationProject, r.ReservationName

Example of of applying dynamic “WHERE” conditions:

As I hope that you will see this code looks nicer and is more efficient!

//*******************************************************************
// Get XrfReservations By ReservationProject and ReservationName
//*******************************************************************
public IList<XrfReservation> GetXrfReservationsByProjectName(string _reservationproject, string _reservationname, int _resultpage, int _resultpagesize)
{
    //Paging Logic
    int firstResult = 0;
    if (_resultpage > 1)
    {
        firstResult = (_resultpage - 1) * _resultpagesize;
    }
 
    //Linq - base query
    var xrfReservations = from r in _Session.Query<XrfReservation>()
                          select r;
 
    //Linq: Apply where clauses
    if (!string.IsNullOrEmpty(_reservationproject))
    {
        xrfReservations = xrfReservations.Where(_w => _w.ReservationProject == reservationProject);
    }
 
    if (!string.IsNullOrEmpty(_reservationname))
    {
        xrfReservations = xrfReservations.Where(_w => _w.ReservationName == _reservationname);
    }
 
    //Linq Apply order by.
    var xrfReservationsOrderBy = from o in xrfReservations orderby o.ReservationProject, o.ReservationName select o;
 
    //Linq: Apply paging.
    IList<XrfReservation> xrfReservationsList = xrfReservationsOrderBy.Skip(firstResult).Take(_resultpagesize);
 
    //Execute Query and return list of data
    return xrfReservationsList.ToList();
}

Tags: , ,

c# | LINQ | NHibernate