Composable LINQ to SQL query with dynamic OrderBy

  • Article

It is a pretty common need to be able to compose a query with multiple "where" parameters at runtime.

There are are scenarios where you want to pass multiple unrelated parameters for your selection that user will chose only at runtime.

Lets take an example.
In our HOL for Silverlight https://blogs.msdn.com/swiss_dpe_team/archive/2008/04/17/silverlight-2-beta-1-end-to-end-hands-on-lab.aspx we have the functionality to search for used Cars by multiple criteria like kilometers, price, model, etc...
Here the search UI:

slchallenge

Normally in LINQ you will write a query like this:

var query = from ad in carfinderDB.ExpandedAds
where ad.Name.StartsWith("P") && ad.Price <= 10000
orderby ad.Price
select ad;

But view that we need to pass any parameters independently or combined (e.g. Kilometers and Price) in the HOL we chose a "bad approach" to use Dynamic SQL to query the DB.
Although it works, it exposes the Web Service to SQL injection attack and we loose all the cool intellisense that LINQ give us. 

Here you can see the "bad" implementation:

public List<ExpandedAd> GetExpandedAds(string sqlQuery)
{
var ads = carfinderDB.ExecuteQuery<ExpandedAd>(sqlQuery);
return ads.ToList<ExpandedAd>();

}

Now what about doing it right with LINQ to SQL.

The first help here comes from the capability of LINQ to do query composition and deferred execution. So for the Where clause is very trivial:

var query = from ad in carfinderDB.ExpandedAds
select ad;

if (!string.IsNullOrEmpty(name)) query = query.Where(ad => ad.Name.StartsWith(name));

if (!string.IsNullOrEmpty(carModelName)) query = query.Where(ad => ad.Name.StartsWith(carModelName));

if (minPrice !=null) query = query.Where(ad => ad.Price >= (minPrice));

if (maxPrice != null) query = query.Where(ad => ad.Price <= (maxPrice));

if (firstRegistration != null) query = query.Where(ad => ad.FirstRegistration >= firstRegistration);

string command = carfinderDB.GetCommand(query).CommandText;

// query is composed and executed at this point
return query.ToList();

In fact you can continue to add new where statements until you execute the query. This is the SQL command that will be sent to SQL Server:

SELECT [t0].[AdID], [t0].[FirstRegistration], [t0].[Kilometers], [t0].[Price], [t0].[ExteriorColor], [t0].[AdType], [t0].[HasNavigationSystem], [t0].[HasAirconditioning], [t0].[HasCruiseControl], [t0].[Description], [t0].[Remarks], [t0].[CarMakeID], [t0].[Name], [t0].[CarModelID], [t0].[CarModelName], [t0].[Doors], [t0].[Seats], [t0].[OriginalPrice], [t0].[DriveTrain], [t0].[FuelType], [t0].[Transmission], [t0].[Cylinders], [t0].[HorsePower], [t0].[VendorID], [t0].[VendorName], [t0].[VendorType], [t0].[Address], [t0].[ZipCode], [t0].[City], [t0].[GeoPosLat], [t0].[GeoPosLng], [t0].[GeoPosQuality], [t0].[PictureID], [t0].[Size], [t0].[ContentType]
FROM [dbo].[ExpandedAd] AS [t0]
WHERE ([t0].[FirstRegistration] >= @p0) AND (([t0].[Price]) <= @p1) AND (([t0].[Price]) >= @p2) AND ([t0].[Name] LIKE @p3)

The challenging part is the dynamic OrderBy. I would love to be able to write something like this query.OrderBy("Price", Desc). For doing this with LINQ you need to write your own OrderBy in form of Extension Method. Here how you can implement it:

public static class DynamicOrderBy
{

           public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string orderByProperty,
bool desc) where TEntity : class
{

               string command = desc ? "OrderByDescending" : "OrderBy";

               var type = typeof(TEntity);

               var property = type.GetProperty(orderByProperty);

               var parameter = Expression.Parameter(type, "p");

               var propertyAccess = Expression.MakeMemberAccess(parameter, property);

               var orderByExpression = Expression.Lambda(propertyAccess, parameter);

               var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },

                                      source.Expression, Expression.Quote(orderByExpression));

               return source.Provider.CreateQuery<TEntity>(resultExpression);

           }

}

Now you can simply use it this way:

query = query.OrderBy("Price", false);

you can even use it with multiple columns:

query = query.OrderBy("Price", false).OrderBy("Name", true);

Here the final service implementation with multi parameters, dynamic orderby and paging:

       //service call from the client
GetExpandedAds("P",null,1000,100000,new DateTime(2000,1,1),"Price",true,0,10);

       public List<ExpandedAd> GetExpandedAds(string name, string carModelName, int? minPrice, int? maxPrice,
DateTime firstRegistration, string orderByColumName, bool orderDescending, int skip, int take)
{
using (ComparisCarfinderDBDataContext carfinderDB = new ComparisCarfinderDBDataContext())
{
var query = from ad in carfinderDB.ExpandedAds
select ad;

               if (!string.IsNullOrEmpty(name)) query = query.Where(ad => ad.Name.StartsWith(name));
if (!string.IsNullOrEmpty(carModelName)) query = query.Where(ad => ad.Name.StartsWith(carModelName));
if (minPrice !=null) query = query.Where(ad => ad.Price >= (minPrice));
if (maxPrice != null) query = query.Where(ad => ad.Price <= (maxPrice));
if (firstRegistration != null) query = query.Where(ad => ad.FirstRegistration >= firstRegistration);
if (!string.IsNullOrEmpty(orderByColumName)) query = query.OrderBy(orderByColumName, orderDescending);

               //code to anylize the SQL statement
string command = carfinderDB.GetCommand(query).CommandText;

               return query.Skip(skip).Take(take).ToList();
}
}

That will generate this SQL statement:

SELECT [t0].[AdID], [t0].[FirstRegistration], [t0].[Kilometers], [t0].[Price], [t0].[ExteriorColor], [t0].[AdType], [t0].[HasNavigationSystem], [t0].[HasAirconditioning], [t0].[HasCruiseControl], [t0].[Description], [t0].[Remarks], [t0].[CarMakeID], [t0].[Name], [t0].[CarModelID], [t0].[CarModelName], [t0].[Doors], [t0].[Seats], [t0].[OriginalPrice], [t0].[DriveTrain], [t0].[FuelType], [t0].[Transmission], [t0].[Cylinders], [t0].[HorsePower], [t0].[VendorID], [t0].[VendorName], [t0].[VendorType], [t0].[Address], [t0].[ZipCode], [t0].[City], [t0].[GeoPosLat], [t0].[GeoPosLng], [t0].[GeoPosQuality], [t0].[PictureID], [t0].[Size], [t0].[ContentType]
FROM [dbo].[ExpandedAd] AS [t0]
WHERE ([t0].[FirstRegistration] >= @p0) AND (([t0].[Price]) <= @p1) AND (([t0].[Price]) >= @p2) AND ([t0].[Name] LIKE @p3)
ORDER BY [t0].[Price] DESC

Much cleaner than the dynamic SQL approach and easy to use with no risk of SQL Injection attacks

Ciao

Ronnie Saurenmann