Subsonic query: issue with the produced query

Question

I'm having a problem with subsonic query. The deal is, I have a view and I want to query it's data to produce something like the following SQL statement:

select * 
from myView
where (col1 like '%a%' or col2 like '%a%' or col3 like '%a%') 
  and col4 = 1 and col5 = 2

But instead the query that is submited to the DB is something like this:

select * 
from myView
where col1 like '%a%' or col2 like '%a%' or col3 like '%a%' 
  and col4 = 1 and col5 = 2

Is there a way to do something like the fisrt query?

Please note I'm using .net 2.0 and subsonic 2.2

Thank you in advance.

Even do, Subsonic rules!

Answer 1

You need to use the Constraint Expressions: WhereExpression, AndExpression, OrExpression, EndExpression.

Anything following “WhereExpression” (or Or/AndExpression) will be wrapped in parentheses. You can close the expression by using “CloseExpression()”, or it will be closed for you if another is started (as with OrExpression above) or if the query ends.

Also see: Using Nested Where/And/Or.

Answer 2

If you post your current code maybe I will be able to give more specific answer but basically you have to start WhereExpression. For example code like this:

var usersQuery = new Select(new SubSonic.TableSchema.TableColumn[] { User.UserIdColumn, User.NameColumn })
            .From(User.Schema)            
            .WhereExpression(User.UserIdColumn.ColumnName).IsEqualTo(1).Or(User.UserIdColumn).IsEqualTo(2)
            .CloseExpression()
            .And(User.NameColumn).Like("a")

Gives query like:

SELECT [dbo].[Users].[UserId], [dbo].[Users].[Name]
 FROM [dbo].[Users]
 WHERE ([dbo].[Users].[UserId] = @UserId0 OR [dbo].[Users].[UserId] = @UserId)
 AND [dbo].[Users].[Name] LIKE @Name