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();
}
Subscribe