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.
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.
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.
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.
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.
9 Responses to Dynamic Sort With LINQ To SQL
Leave a Reply
Connect with me
-
Wow, thanks so much for putting this together. I’ll give this a shot and let you know how it goes!
Levi,
Thanks for kind words.
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.
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.
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
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
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
Levi,
I will send you the sample project shortly.
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