subsonic query problem

Question

I'm using subsonic 2.2 in an app. I'm running a little complicated query in I have used both "And" and "Or" for a field, I'm little confused about how it is going to be translated into sql statement

MytableCollection col = DB.Select().From("mytable").Where("prop1").IsEqualTo(obj.prop1)
                .And("prop2").IsEqualTo(obj.prop2)
                .And("prop3").IsEqualTo(obj.prop3)
                .Or("prop1").IsEqualTo(1)                
                .ExecuteAsCollection<MytableCollection>();

I want to perform query like this.

select * from mytable where (prop1=obj.prop1 or prop1=1)  and prop2=obj.prop2 and prop23=obj.prop3

Answer 1

you can use expression in subsonic 2.2.

MytableCollection col  = new Select(Mytable.Schema)
.WhereExpression("prop1").IsEqualTo(obj.prop1).Or("prop1").IsEqualTo(1)
.AndExpression("prop2").IsEqualTo(obj.prop2)
.AndExpression("prop3").IsEqualTo(obj.prop3)
.ExecuteAsCollection<MytableCollection>();

Answer 2

As Andra says you can use AndExpression. This should do what you want:

MytableCollection col = DB.Select().From(Mytable.Schema)
  .Where(Mytable.Columns.Prop2).IsEqualTo(obj.prop2)
  .And(Mytable.Columns.Prop3).IsEqualTo(obj.prop3)
  .AndExpression(Mytable.Columns.Prop1).IsEqualTo(obj.prop1)
  .Or(Mytable.Columns.Prop1).IsEqualTo(1)                
  .ExecuteAsCollection<MytableCollection>();

N.B. using MyTable.Schema and MyTable.Columns will catch a lot of issues at compile time if you rename tablees and will save errors caused by mistyping

Answer 3

Something that is REALLY useful to know about is also the following two methods to call in your query building:

.OpenExpression()

and

.CloseExpression()

Mix those bad buoys and you have a lot better control over knowing where things start and finish