Dynamic Sort With LINQ To SQL

November 19th, 2008 | 9 Comments | Posted in Programming

Sometime ago I wrote a post showing how to do dynamic sort with LINQ. That approach worked well with LINQ To Objects. Today I spotted a question on the forums where Levi asked a question about doing dynamic sorts with LINQ To SQL. My earlier approach will work once all the data is retrieved on the client side. This of course is not an ideal way. A better option is to do sorting on database. Here is the question as it was posted on the forum.

C# Visual Studio .Net Code

I thought this will work

My initial reaction was to modify the code I wrote earlier to dynamically create a query. This is what I did.

C# Visual Studio .Net Code

and I used this code to call my method

LINQ To SQL is not able to generate a query and instead throws an exception when I try to access the results in debugger.

So what can be done?

The key here is to generate the query dynamically. And we can use the built-in Expression type to do just that. So I gave it another go with this code.

C# Visual Studio .Net Code

This method takes in a column name to sort by and returns IQueryable<T>.

It works but looks like a lot of effort and appears miles away from any kind of normal LINQ code. Fortunately I can achieve the same results with this code.

C# Visual Studio .Net Code

To sort by Title, I can call the method like this.

This gives me exactly what I want and partially answers the question posted on the forum. Partially, because I have not addressed dynamic grouping. I assume that it is doable with a similar approach.

Leave a Reply 7477 views, 1 so far today |

Tags: LINQ

Follow Discussion

9 Responses to “Dynamic Sort With LINQ To SQL”

Levi Says:
November 20th, 2008 at 11:47 am
Wow, thanks so much for putting this together. I’ll give this a shot and let you know how it goes!
Deepak Says:
November 20th, 2008 at 12:10 pm
Levi,

Thanks for kind words.
Fatih Says:
November 20th, 2008 at 8:41 pm
Hi,
Thanks for this great code but i’ve a problem with this.

When i call GetSortedEmployees(x=>x.Title),i’m getting an error like ” cannot access non-static method ‘GetSortedEmployees’ in static context”.How can i get rid of this problem?

Thx.
Deepak Says:
November 20th, 2008 at 9:21 pm
Fatih,

You are trying to access GetSortedEmployees method from a static method. You can either make GetSortedEmployees a static method or move your data access code to another class and call the method on an instance of that class.

If this does not help you then post some code and I’ll have a look.
Levi Says:
November 21st, 2008 at 8:58 am
Hello Deepak,

For some reason I can’t get this to compile. Could you tell me what DLL references and namespaces I should be using? I think i may be missing one but can’t seem to figure out which one.

Thanks!
Levi
Deepak Says:
November 21st, 2008 at 9:21 am
Hi Levi,

Make sure that you have reference to System.Data.Linq

and following using statements other than the ones in a standard Windows Forms application (I used Win Forms app as my test harness)

System.Linq
System.Linq.Expressions
Levi Says:
December 6th, 2008 at 8:11 am
Hello again Deepak,
Is there any way you could post the sample project or perhaps send it to me? My business partner and I are still struggling to get sorting to work with our linq statements and it would help us understand if there is something we are missing. I know it may be a lot to ask, so for your troubles I’d be happy to give you one free icon collection if you are interested. My site is http://www.professional-icons.com >> Thanks!

Levi
Deepak Says:
December 7th, 2008 at 9:29 pm
Levi,

I will send you the sample project shortly.
Dhaval Says:
June 21st, 2009 at 3:54 pm
Deepak,

There seems to be big difference between your first GetSortedEmployees function and your last one, the one that takes Lamda Expression as an argument. The problem with the last one, you can’t simply pass in a string columnname and simply do the sort. for example.

if my function was simply sending in columnname as string:

Function GetEmployees(sortBy as String) as List(Of Employees)

‘I would have to do a select case to use your lamda expression function

SELECT CASE sortBy

case “CreatedDate”

return GetSortedEmployees(Of Employee)(Function(e) e.CreatedDate).ToList()

Case “Name”

return GetSortedEmployees(Of Employee)(Function(e) e.Name).ToList()

End Select

End Function

So, your last function altough simple, isnt truely dynamic. I am wondering if there is a way to write the last function so that it takes sortby columnname as string and returns a sorted iQueryable.

Thanks.

Dhaval

Get Updates By Email