Rocks Thoughts

LINQ TO SQL Dynamic Queries #3 (And's & Or's Together)

I've received a lot of hits and questions about my first two posts on dynamic queries. If you haven't read them yet I suggest you take a look.

LINQ TO SQL Dynamic Queries (And Operators)
LINQ TO SQL Dynamic Queries (Or Operators)

The reason for today's post is I had a reader ask this question the other day:

"Hello Sir,

Thank you for writing such a great post in your site and replying me with answer for my first problem. But i didn't get any answer for my next question, how can i have both AND operator and OR operator in one Dynamic query.

Example:
student table:
id int
name varchar(10)
grade int

i want to write a query like below:

select * from student where ((name LIKE "%a%") AND (grade = '1')) OR ((name LIKE "%a%") AND (grade = '2')) OR ...... OR ((name LIKE "%a%") AND (class = '10'));

I tried to build something like below by seeing your post in your site:

For i As Integer = 1 To 10
predicate = predicate.AND(Function(p As STUDENT) p.name.Contains("a"))
predicate = predicate.OR(Function(p As STUDENT) p.class = i )
Next

Dim result As IEnumerable(Of STUDENT) = db.STUDENTs.Where(predicate)

But the Predicate.AND seems to be not working the debugger is avoiding it, sir can you please solve my problem......

Sitting on the edge of my seat and waiting for your early response........

Thanks and Regards,
ilyas "

As it turns out they're right. For some reason this wasn't working correctly. So I fired up LINQPad and started hammering out some queries. It took some time but here is the result of the trial and error session:

VB

Dim basePredicate As Expression(Of Func(Of Student, Boolean)) = PredicateBuilder.True(Of Student)()
basePredicate = basePredicate.And(function(s) false)

for i As Integer = 1 to 10

     Dim predicate1 As Expression(Of Func(Of Student, Boolean)) = _
     PredicateBuilder.True(Of Student)()

     Dim j as Integer = i
     predicate1 = predicate1.And(function(s) s.StudentName.Contains("a"))
     predicate1 = predicate1.And(function(s) s.Grade.Equals(j))
     basePredicate = basePredicate.Or(predicate1)

Next


Dim result As IEnumerable(Of Student) = db.Students.Where(basePredicate)

C#

var basePredicate = PredicateBuilder.True<Student>();
basePredicate = basePredicate.And(s => false);

for(int i = 1; i<=10; i++)
{
     var predicate1 = PredicateBuilder.True<Student>();
     int j = i;
     
     predicate1 = predicate1.And(s => s.StudentName.Contains("a"));
     predicate1 = predicate1.And(s => s.Grade == j);
     basePredicate = basePredicate.Or(predicate1);
}

IEnumerable<Student> results = db.Students.Where(basePredicate);

Ok, so a little bit of an explanation is in order.

"basePredicate = basePredicate.And(function(s) false)". I knew that I would have to have a base predicatebuilder (e.g. Dim basePredicate As Expression(Of Func(Of Student, Boolean)) = PredicateBuilder.True(Of Student)())

"basePredicate" is it. Now the weird thing is that if I just create a predicatebuilder WITHOUT assigning the predicate a default value using "basePredicate = basePredicate.And(function(s) false)", it will completely ignore any And or Or statements added to the base predicatebuilder. As a test, leave this line of code out and see what query you get back. It's very interesting. Also, if you use "predicate = predicate.And(function(s) true)" you get a completely different result as well. More to come on that in another post.

"Dim j as Integer = i". If you use "i" in the s.Grade.Equals(i) statement, all of the where statements will have "11" as the value. (e.g. select * from students where (studentname like '%a%' and grade = 11) or (studentname like '%a%' and grade = 11))

I believe this has something to do with the actual query not being completed until the "db.Student.Where(predicate)" statement is called.

Using "s.Grade.Equals(j)" some how magically creates the correct statement. (e.g. select * from students where (studentname like '%a%' and grade = 1) or (studentname like '%a%' and grade = 2))

This doesn't make any sense to me as the integer type is value type. I would understand if it was an reference type but I'm not sure how creating a local integer variable within the loop fixes the issue.

The rest is pretty self-explanatory. You create a new predicate to be used to build the "And" statements.

Dim predicate1 As Expression(Of Func(Of Student), Boolean)) = PredicateBuilder.True(Of Student)()
predicate1 = predicate1.And(function(s) s.StudentName.Contains("a"))
predicate1 = predicate1.And(function(s) s.Grade.Equals(j))

Then we add create the "Or" operators between the And statements by using the base predicatebuilder:

basePredicate = basePredicate.Or(predicate1)

So there you go ilyas, I hope this helps...

posted @ Thursday, April 10, 2008 10:52 AM