I want to reproduce the following query with a Typed List: SELECT * FROM Address WHERE DeactivatedDate IS NULL AND Surname=@Surname AND (Address1 LIKE @House OR Address2 LIKE @House)

The only way I can think of to deal with the brackets is by repeated fills with different filters. This is not too bad for the above query but would get very lengthy if there were more predicates inside brackets. Is there a better way?

My code is as follows:

'Note ResultList is a typed list
ResultList.Clear()

Dim filter1 As IPredicateExpression = New PredicateExpression

filter1.Add(AddressFields.DeactivatedDate = DBNull.Value)

If txtSurname.text.Length <> 0 Then

    filter1.AddWithAnd(AddressFields.Surname = txtSurname.text)
End If

If txtHouse.Text.Length <> 0 Then
    Dim house as string="%" & txtHouse.Text & "%"

    filter1.AddWithAnd(AddressFields.Address1 Mod house)
    filter1.AddWithAnd(Not (AddressFields.Address2 Mod house))

End If

ResultList.Fill(0, Nothing, True, filter1)

Dim filter2 As IPredicateExpression = New PredicateExpression

filter2.Add(AddressFields.DeactivatedDate = DBNull.Value)

If txtSurname.text.Length <> 0 Then

    filter2.AddWithAnd(AddressFields.Surname = txtSurname.text)
End If

If txtHouse.Text.Length <> 0 Then
    Dim house as string="%" & txtHouse.Text & "%"

    filter2.AddWithAnd(AddressFields.Address2 Mod house)
    filter2.AddWithAnd(Not (AddressFields.Address1 Mod house))

End If

ResultList.Fill(0, Nothing, True, filter2)

Dim filter3 As IPredicateExpression = New PredicateExpression

filter3.Add(AddressFields.DeactivatedDate = DBNull.Value)

If txtSurname.text.Length <> 0 Then

    filter3.AddWithAnd(AddressFields.Surname = txtSurname.text)
End If

If txtHouse.Text.Length <> 0 Then
    Dim house as string="%" & txtHouse.Text & "%"

    filter3.AddWithAnd(AddressFields.Address2 Mod house)
    filter3.AddWithAnd((AddressFields.Address1 Mod house))

End If

ResultList.Fill(0, Nothing, True, filter3)

Hello,

In a predicate expression you can add more than one condition but using brackets it's true:

Dim filter1 As IPredicateExpression = New PredicateExpression()
filter1=((AddressFields.DeactivatedDate = DBNull.Value) AND (AddressFields.Surname="" )AND 
    ((AddressFields.Address1 Mod "") Or (Address2 Mod "")))

I agree it's make a lots of brackets but it makes your code shorter.

jbb, I did not know that. Great! Thanks for the help.