LINQ: How to build dynamic query?

[Guest]

Hello,
played a little with orcas and went into a problem by building dynamic
queries. I want to build a dynamic where clause but only with parameters that
are not empty => (TextBox.Text != "").
Doing this by building a string is very simple, but how to do it with LINQ?

string sql = "";
if (TextBox1.Text != "")
sql += "Columns_1='"+TextBox1.Text+"'";
if (TextBox2.Text != "")
sql += "Columns_2='"+TextBox2.Text+"'";


How to include the if query to linq???

var query = from c in db.MyTable
where c.Column_1 == TextBox1.Text &&
c.Column_2 == TextBox2.Text
select c;

Thank you!

-Stefan

 

[Marc Gravell]

Queries are "composable" - hence you can do things like the following:

var query = from s in dataContext.SomeEntities
select s;

if (!string.IsNullOrEmpty(inputA)) {
query = from x in query
where x.PropertyA == inputA
select x;
}
if (!string.IsNullOrEmpty(inputB)) {
query = from x in query
where x.PropertyB == inputB
select x;
}

Then enumerate query.

Marc

 

[Marc Gravell]

(slightly simpler):

if (!string.IsNullOrEmpty(inputA)) {
query = query.Where(x => x.PropertyB == inputA);
}
if (!string.IsNullOrEmpty(inputB)) {
query = query.Where(x => x.PropertyB == inputB);
}

Marc

 

[Marc Gravell]

[watch for typo - should have been PropertyA]

 

[Jon Skeet [C# MVP]]

Queries are "composable" - hence you can do things like the following:

var query = from s in dataContext.SomeEntities
select s;

if (!string.IsNullOrEmpty(inputA)) {
query = from x in query
where x.PropertyA == inputA
select x;
}
if (!string.IsNullOrEmpty(inputB)) {
query = from x in query
where x.PropertyB == inputB
select x;
}

Then enumerate query.

It's actually slightly easier than that if you ignore the query
expression syntax:

var query = dataContext.SomeEntities.AsQueryable();
if (!string.IsNullOrEmpty(inputA))
{
query = query.Where(x => x.PropertyA==inputA);
}
if(!string.IsNullOrEmpty(inputB))
{
query = query.Where(x => x.PropertyB==inputB);
}

You could even write a method to abstract it out if necessary.

Query expression syntax is nice in many cases, but just to add a
"where" clause I prefer to use the direct method call.

Jon

 

[Chris Dunaway]

It's actually slightly easier than that if you ignore the query
expression syntax:

var query = dataContext.SomeEntities.AsQueryable();
if (!string.IsNullOrEmpty(inputA))
{
query = query.Where(x => x.PropertyA==inputA);}

if(!string.IsNullOrEmpty(inputB))
{
query = query.Where(x => x.PropertyB==inputB);

}

You could even write a method to abstract it out if necessary.

Query expression syntax is nice in many cases, but just to add a
"where" clause I prefer to use the direct method call.

Jon

Hi Jon.

I've pre-ordered your book from Amazon but was wondering if it will
cover LINQ in much detail?

Thanks,

Chris

 

[Jon Skeet [C# MVP]]

I've pre-ordered your book from Amazon

Yay - thanks very much

but was wondering if it will cover LINQ in much detail?

It covers the translation of query expressions into "normal" method
calls, and it covers the standard query operators, but it only skims
over the different LINQ providers. Going into any level of detail
about LINQ basically requires a book in its own right - and that's
where "LINQ in Action" (also from Manning) comes into play. I confess
I haven't looked at any *other* LINQ books, but LINQ in Action is
good

Jon

 

[Marc Gravell]

It's actually slightly easier than that if you ignore the query

expression syntax:
...
Query expression syntax is nice in many cases, but just to add a
"where" clause I prefer to use the direct method call.

Yes - I posted that right after my initial post (albeit with a
typo)... I agree.

You could even write a method to abstract it out if necessary.

Care to expand on this? I can think of a few ways of interpreting
this, but I'm just interested in how you mean it...?

Marc

 

[Guest]

Query expression syntax is nice in many cases, but just to add a
"where" clause I prefer to use the direct method call.

Jon

Hi Jon and Marc,
thank you for this quick post!

@Jon: What do you mean with "the direct method call"?

@all: Now I can filter with linq dynamically, but what is with ORDER BY?
string s = "select * from mytable order by " + DropDownList1.SelectedValue;

-Stefan

 

[Jon Skeet [C# MVP]]

Care to expand on this? I can think of a few ways of interpreting
this, but I'm just interested in how you mean it...?

Well, you could have (off the top of my head - apologies if
Predicate<T> should be Func<T,bool>)
(Further apologies for the formatting - Google groups uses a
proportional font)

IQueryable<T> ConditionalWhere<T>(static this IQueryable<T> query,
string value,
Expression<Predicate<T>> predicate)
{
if (!string.IsNullOrEmpty(value))
{
return query.Where(predicate);
}
}

then call it with:

var query = query.ConditionalWhere(valueA, x => x.PropertyA==valueA)
.ConditionalWhere(valueB, x => x.PropertyB==valueB);

Jon

 

[Jon Skeet [C# MVP]]

thank you for this quick post!

@Jon: What do you mean with "the direct method call"?

I mean explicitly calling Where instead of letting the query
expression translation do it for you.

@all: Now I can filter with linq dynamically, but what is with ORDER BY?
string s = "select * from mytable order by " + DropDownList1.SelectedValue;

What do you mean by "what is with ORDER BY"? Do you mean you want to
express the ordering in LINQ? If so, you'll need to use the OrderBy
method or "order by" part of the query expression syntax - but that
will be more strongly typed than your SQL example.

Jon

 

[Guest]

What do you mean by "what is with ORDER BY"? Do you mean you want to
express the ordering in LINQ? If so, you'll need to use the OrderBy
method or "order by" part of the query expression syntax - but that
will be more strongly typed than your SQL example.

Jon

I want to change the orderby dynamic:

var q = from f in DB.Firmas
where f.PLZ == 89077
orderby DropDownList1.SelectedValue
select f;

E.g. I have a filtermask for a grid, the user should be able to enter
several optional filter parameter. This can be done with you first example.
But maybe I want to set a dropdownlist to allow the user to sort the query
result (e.g. sortby column_1, or sortby column_4 or sortby column_5).

The only (complicated) way to do this is:

if (DropDownList1.SelectedValue=="column_1")
{
var q = from f in DB.Firmas
where f.PLZ == 89077
orderby f.column_1
select f;
}
elseif (DropDownList1.SelectedValue=="column_4")
{
var q = from f in DB.Firmas
where f.PLZ == 89077
orderby f.column_4
select f;
}

There must be a more simple way.

-Stefan

 

[Jon Skeet [C# MVP]]

On Nov 15, 10:48 am, Stefan Wilhelm

I want to change the orderby dynamic:

var q = from f in DB.Firmas
where f.PLZ == 89077
orderby DropDownList1.SelectedValue
select f;

It can be dynamic, but it has to be verifiable at compile time. In
other words, you need to do things based on the drop down list, not
just use the text of it directly.

E.g. I have a filtermask for a grid, the user should be able to enter
several optional filter parameter. This can be done with you first example.
But maybe I want to set a dropdownlist to allow the user to sort the query
result (e.g. sortby column_1, or sortby column_4 or sortby column_5).

The only (complicated) way to do this is:

if (DropDownList1.SelectedValue=="column_1")
{
var q = from f in DB.Firmas
where f.PLZ == 89077
orderby f.column_1
select f;}

elseif (DropDownList1.SelectedValue=="column_4")
{
var q = from f in DB.Firmas
where f.PLZ == 89077
orderby f.column_4
select f;

}

There must be a more simple way.

Yes - do something like:

var query = ... // unordered

switch (DropDownList1.SelectedValue)
{
case "column_1":
query = query.OrderBy (firm => firm.SomeProperty);
break;
...
}

Again, this uses the composability of queries.

Jon

 

[Guest]

Yes - do something like:

var query = ... // unordered

switch (DropDownList1.SelectedValue)
{
case "column_1":
query = query.OrderBy (firm => firm.SomeProperty);
break;
...
}

Again, this uses the composability of queries.

Jon

Is there no other way to sort? This is too complex, I think. When I will
have 20 columns to sort, I have to build 20 switch/case.
In pure sql, I can do this by "select * from myTable order by " +
DropDownList1.SelectedValue - no switsch/case - this is what I'm missing.
No way to get it work?

 

[Jon Skeet [C# MVP]]

On Nov 15, 12:46 pm, Stefan Wilhelm

Is there no other way to sort? This is too complex, I think. When I will
have 20 columns to sort, I have to build 20 switch/case.
In pure sql, I can do this by "select * from myTable order by " +
DropDownList1.SelectedValue - no switsch/case - this is what I'm missing.
No way to get it work?

You *can* do that in SQL, but that doesn't mean it's a good idea.
Hopefully ASP.NET will do some validation of the value that the client
claims is selected, but I'm not sure I'd rely on it. What happens when
the client comes back with a selected value of "foo; drop table
myTable"? Basically you're using user input in a raw SQL form, which
is just asking for an injection attack.

To avoid putting it into a switch/case block, you could have a
dictionary - but the problem is that unless *all* your ordering
columns use the same type, you won't know what the signature of the
delegate involved is I suppose you could have a few well known
types with a set of dictionaries, but that's not terribly pleasant...

Jon

 

[Guest]

You *can* do that in SQL, but that doesn't mean it's a good idea.
Hopefully ASP.NET will do some validation of the value that the client
claims is selected, but I'm not sure I'd rely on it. What happens when
the client comes back with a selected value of "foo; drop table
myTable"? Basically you're using user input in a raw SQL form, which
is just asking for an injection attack.

OK, in ASP.Net this will not a good idea, but in Windows.Forms this should
be not a problem.

To avoid putting it into a switch/case block, you could have a
dictionary - but the problem is that unless *all* your ordering
columns use the same type, you won't know what the signature of the
delegate involved is I suppose you could have a few well known
types with a set of dictionaries, but that's not terribly pleasant...

Maybe it will work over reflection?
It is a little bit pity that there is no
query.OrderBy<Firma>("columnAsString");

Thank you for your answers!!!

 

[Jon Skeet [C# MVP]]

OK, in ASP.Net this will not a good idea, but in Windows.Forms this should
be not a problem.

I still don't think it's a good idea, whatever platform you're on, to
be honest.

Maybe it will work over reflection?

You probably *could* do it with reflection, although it wouldn't be
particularly simple. You'd have to look up the property, find out the
type, create the appropriate (typed) expression tree, look up OrderBy
and create the appropriate generic version, then call it. Not nice.
I'll look into it at home if you're particularly interested in going
this way, but I think the way using "proper" lambda expressions is a
better idea.

It is a little bit pity that there is no
query.OrderBy<Firma>("columnAsString");

Except that at that point you no longer have any compile-time safety,
which is half the point of LINQ.

Jon

 

[Marc Gravell]

You could presumably put an expression tree and lambda together
through code (using Expression.Property(...) at some point), but it
wouldn't be pretty. The switch would be easier to maintain.

Marc

 

[Marc Gravell]

Maybe it will work over reflection?
See my other reply

but in Windows.Forms this should
be not a problem.

winform is easy to abuse too... at least with ASP.NET the user doesn't
tend to have a direct connection to the database...

Marc

 

[Andrus]

You could presumably put an expression tree and lambda together through

code (using Expression.Property(...) at some point), but it wouldn't be
pretty. The switch would be easier to maintain.

Some columns in my winform datagridview are defined by user in runtime.
So I cannot use hard-coded swith with all column names.

My grid columns can be string, bool and decimal types only.
Maybe it is possible to use switch for types only like

swith (SortExpression.GetTypeCode) {
case TypeCode.String:
....

case TypeCode.Bool:
...

case TypeCode.Decimal:
....

default:
MessageBox.Show("Cannot sort by this column");
}

Andrus.