Does anyone know if it is possible to programmatically set the orderby and ascending/descending conditions within a Linq statement (such as passing in values from the method parameter
s list)?
For example, assuming that I am using the following example Linq statement:
IOrderedQueryable<Employees> hireQuery =
from emp in db.Employees
orderby emp.HireDate descending
select emp;
Is it possible to programmatically set the orderby line programmatically by passing in dynamic values for the orderby column and where the column will be ascending/descending, rather than hard-coding such values?
I don't have VS 2008 where I am right now, so my syntax may be off a little, but try something like this:
IQueryable hireQuery = from emp in db.Employees
where some condition is true // otherwise this whole statement isn't really doing anything
select emp;
if (sortAscending)
{
hireQuery = from emp in hireQuery
orderby emp.HireDate ascending
select emp;
}
else
{
hireQuery = from emp in hireQuery
orderby emp.HireDate descending
select emp;
}
I added a where statement to the initial query because without it, it isn't really doing anything.
I know that looks like a lot of code, and may look inefficient because it may look like I query the data once for the where, and again for the ordering, but because that inital query is deferred, you can compose the query over several statements (as long
as you don't call any operators requiring the query to actually execute) without it impacting performance. So, all that code will get translated into a single query once the hireQuery sequence is enumerated.
Thanks for the suggestion. Unfortunately, though this is a way to do a "programmatic" query, it's not really programmatic (at least in the sense I was meaning) or dynamic, and it certainly doesn't scale at all well.
There's a DynamicQuery project in there which explains how to do string-based, parameterized, dynamic, runtime queries using LINQ. It works nicely, though I can't speak to any performance implications. The impressive example they have there is:
var query =
db.Customers.Where("City == @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("New(CompanyName as Name, Phone)");
This is not part of the core LINQ framework (yet): you have to get the sample code and compile into your project.
My apologies. I wasn't intending to discredit your post. You're absolutely correct: your solution is absolutely programmatic and scales fine. I wasn't being very precise in my terminology, and I assumed you'd get my meaning :(
What I ought to have said is that your solution is potentially difficult to maintain. Or, more correctly, your solution used as a solution to my problem would be difficult to maintain.
In my case, I'm trying to change the actual sort field of the orderby clause at runtime, not just the sort order. And, I'm dealing with a system to which we will be adding hundreds, maybe thousands of sort fields. So, I've been looking for a solution which
wouldn't require a switch statement over each sortable field, ultimately because I don't want to have to add a new query each time I add a new sortable field: this is the maintenance problem I'm talking about. I wanted a way to write a single query, and
then "pass" the sort field into it as a parameter. In this sense, your solution doesn't "scale" well for me, though technically, it's not a scaling issue but a maintenance issue. Also, when I said it wasn't "programmatic" I was referring to the parameterization
of the orderby clause. Maybe it would be better so say that your solution wasn't "parameterized".... Your solution is certainly programmatic! In any case, hopefully now you get my point.
Again, apologies. And, thanks again for taking the time to post your solution.
Ya joerattaz, it's definitely not maintable or good practice. Adding in a search parameter later would result in even more nested if statements. Just my humble opinion though ;)
Vancouver Software Development / Business Automation Solutions
This is a post by Scott Guthrie discussing Charlie Calvert's Dynamic Linq class, which I believe, is exactly what you're looking for. They're parameterized, dynamically constructed Linq queries.
Vancouver Software Development / Business Automation Solutions
"You can download the solution and add Dynamic.cs to your SL project. It will not compile right away complaining about
ReaderWriterLock
not being available in SilverLight. I just commented those lines out for now. "
I'm not sure what to comment out in this Dynamic.cs class for the project to build without breaking the class...
I created a ASP.NET Web Application (ie NOT Silverlight) and added the Dynamic.cs class : it builds correctly.
So it looks like the problem comes from Silverlight. Any workaround ? thanks.
LINQorderby dynamic parameterize programmaticdynamicqueryLINQ to Sql
vs2005
Member
20 Points
5 Posts
Programmatically set the orderby and ascending/descending clause in a Linq statement
Sep 06, 2007 11:43 AM|LINK
Hi,
Does anyone know if it is possible to programmatically set the orderby and ascending/descending conditions within a Linq statement (such as passing in values from the method parameter
s list)?
For example, assuming that I am using the following example Linq statement:
IOrderedQueryable<Employees> hireQuery =
from emp in db.Employees
orderby emp.HireDate descending
select emp;
Is it possible to programmatically set the orderby line programmatically by passing in dynamic values for the orderby column and where the column will be ascending/descending, rather than hard-coding such values?
Thanks,
John
LINQ
haughki
Member
6 Points
3 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Sep 12, 2007 06:59 PM|LINK
I have exactly the same question.
orderby dynamic parameterize programmatic
joerattz
Member
685 Points
163 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Sep 13, 2007 07:36 PM|LINK
I don't have VS 2008 where I am right now, so my syntax may be off a little, but try something like this:
IQueryable hireQuery = from emp in db.Employees where some condition is true // otherwise this whole statement isn't really doing anything select emp; if (sortAscending) { hireQuery = from emp in hireQuery orderby emp.HireDate ascending select emp; } else { hireQuery = from emp in hireQuery orderby emp.HireDate descending select emp; }I know that looks like a lot of code, and may look inefficient because it may look like I query the data once for the where, and again for the ordering, but because that inital query is deferred, you can compose the query over several statements (as long as you don't call any operators requiring the query to actually execute) without it impacting performance. So, all that code will get translated into a single query once the hireQuery sequence is enumerated.
http://www.linqdev.com
http://www.netsplore.com
haughki
Member
6 Points
3 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Sep 13, 2007 10:47 PM|LINK
Thanks for the suggestion. Unfortunately, though this is a way to do a "programmatic" query, it's not really programmatic (at least in the sense I was meaning) or dynamic, and it certainly doesn't scale at all well.
I found this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1495265&SiteID=1
Which lead me to get the 2008 Beta 2 samples from here:
http://msdn2.microsoft.com/en-us/bb330936.aspx
There's a DynamicQuery project in there which explains how to do string-based, parameterized, dynamic, runtime queries using LINQ. It works nicely, though I can't speak to any performance implications. The impressive example they have there is:
var query =
db.Customers.Where("City == @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("New(CompanyName as Name, Phone)");
This is not part of the core LINQ framework (yet): you have to get the sample code and compile into your project.
Hawkeye Parker
LINQ dynamic runtime parameterized
joerattz
Member
685 Points
163 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Sep 14, 2007 12:32 AM|LINK
In what way do you mean it doesn't scale well? And, how is it not programmatic? I realize it is a bit verbose, but how is it not prgrammatic?
Also, while it may not be exactly what you want because you lose the language integration, check out the DataContext.ExecuteQuery method.
http://www.linqdev.com
http://www.netsplore.com
haughki
Member
6 Points
3 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Sep 14, 2007 05:11 PM|LINK
joerattaz,
My apologies. I wasn't intending to discredit your post. You're absolutely correct: your solution is absolutely programmatic and scales fine. I wasn't being very precise in my terminology, and I assumed you'd get my meaning :(
What I ought to have said is that your solution is potentially difficult to maintain. Or, more correctly, your solution used as a solution to my problem would be difficult to maintain.
In my case, I'm trying to change the actual sort field of the orderby clause at runtime, not just the sort order. And, I'm dealing with a system to which we will be adding hundreds, maybe thousands of sort fields. So, I've been looking for a solution which wouldn't require a switch statement over each sortable field, ultimately because I don't want to have to add a new query each time I add a new sortable field: this is the maintenance problem I'm talking about. I wanted a way to write a single query, and then "pass" the sort field into it as a parameter. In this sense, your solution doesn't "scale" well for me, though technically, it's not a scaling issue but a maintenance issue. Also, when I said it wasn't "programmatic" I was referring to the parameterization of the orderby clause. Maybe it would be better so say that your solution wasn't "parameterized".... Your solution is certainly programmatic! In any case, hopefully now you get my point.
Again, apologies. And, thanks again for taking the time to post your solution.
Hawkeye Parker
kbach
Member
13 Points
15 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Apr 04, 2008 06:35 PM|LINK
PointyHat Software
www.pointyhat.ca
kbach
Member
13 Points
15 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Apr 04, 2008 08:42 PM|LINK
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
This is a post by Scott Guthrie discussing Charlie Calvert's Dynamic Linq class, which I believe, is exactly what you're looking for. They're parameterized, dynamically constructed Linq queries.
PointyHat Software
www.pointyhat.ca
oudoulj
Member
100 Points
32 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Aug 29, 2008 09:04 AM|LINK
While trying to sort columns in a Silverlight DataGrid using Linq, I ended up on Scott's page :
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
and downloaded the CSharpSamples.zip file.
I then added the Dynamic.cs class (located in LinqSamples\DynamicQuery\DynamicQuery\) to the root of my Silverlight project.
But it does not build [:(]
I have errors on ReaderWriterLock and LockCookie
The type or namespace 'ReaderWriterLock' could not be found (are you missing a using directive or an assembly reference ?)
I googled quite a lot but no one seems to mention that error except here : http://blog.lab49.com/archives/2306
"You can download the solution and add Dynamic.cs to your SL project. It will not compile right away complaining about ReaderWriterLock not being available in SilverLight. I just commented those lines out for now. "
I'm not sure what to comment out in this Dynamic.cs class for the project to build without breaking the class...
I created a ASP.NET Web Application (ie NOT Silverlight) and added the Dynamic.cs class : it builds correctly.
So it looks like the problem comes from Silverlight. Any workaround ? thanks.
LINQ orderby dynamic parameterize programmatic dynamic query LINQ to Sql
Jerome
tsmyrnios
Member
4 Points
2 Posts
Re: Programmatically set the orderby and ascending/descending clause in a Linq statement
Jan 21, 2009 04:07 PM|LINK
If you don't mind working with it as an IEnumerable then you could do this:
bool descending = true;
var hireQuery =
from emp in db.Employees
orderby emp.HireDate ascending
select emp;
if(descending) hireQuery = hireQuery.AsEnumerable().Reverse();