SubSonic condition expressions

8 Jul

Let’s say I have a table called “Contacts”. I’d like to search that table for the string “michael pardo”, however, I could pass in the string “pardo michael” instead. I’d like it to work either way, so here’s what I think the query should look like:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT * 
FROM Contacts
WHERE 
(
    FirstName LIKE '%michael%' 
    OR LastName LIKE '%michael%'
)
AND 
(
    FirstName LIKE '%pardo%' 
    OR LastName LIKE '%pardo%'
)

Okay, well how do I enclose those condition expressions with SubSonic? We can take advantage of the Expression syntax to make this work. Here’s the SubSonic code, broken up by query parts.

1
2
3
4
5
6
7
8
9
10
11
12

// SELECT * FROM Contacts
var q = new Select().From<Contact>();
 
// WHERE (FirstName LIKE '%michael%' OR LastName LIKE '%michael%')
q.WhereExpression(Contact.Columns.FirstName).Like("%michael%");
q.Or(Contact.Columns.LastName).Like("%michael%");
 
// AND (FirstName LIKE '%pardo%' OR LastName LIKE '%pardo%')
q.AndExpression(Contact.Columns.FirstName).Like("%pardo%");
q.Or(Contact.Columns.LastName).Like("%pardo%");
 
q.ExecuteTypedList<Contact>();

One problem I encountered with this method was with chaining. If I chain the “or” commands instead of calling them separately, SubSonic will short-change you on the constraints it passes in. Some sort of bug (or feature) maybe.

3 Responses to “SubSonic condition expressions”

  1. Mike Griffin 13. Jan, 2010 at 10:58 am #

    Hi pardo, promise I’m not going to spam all your posts, SubSonic is a good architecture, I just though I’d show that same query in EntitySpaces …

    Hopefully the formatting won’t be hosed on this? Notice the syntax is all natural language and easy to read, ie, the solution looks the problem its trying to solve.

    EmployeesQuery q = new EmployeesQuery();
    q.Where
    (
    (q.FirstName.Like(“%michael%”) || q.LastName.Like(“%michael%”))
    &&
    (q.FirstName.Like(“%pardo%”) || q.LastName.Like(“%pardo%”))
    );

    EmployeesCollection coll = new EmployeesCollection();
    coll.Load(q);

  2. Michael Pardo 13. Jan, 2010 at 11:07 am #

    @ Mike I use both SubSonic and EntitySpaces at work, and ES’s dynamic query syntax is very nice. EntitySpaces is easier than SubSonic in many situations — like this one. Many people can’t cough up the $299 for ES though (me included).

  3. Mike Griffin 13. Jan, 2010 at 11:15 am #

    I hear you, I worked on MyGeneration for years and years and that was a 100% free product (still is) but with three kids you soon realize college ain’t free … (LOL)

Leave a Reply