SubSonic condition expressions

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.

This entry was posted on Wednesday, July 8th, 2009 at 3:07 pm and is filed under .Net, Development, SubSonic. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.

3 comments

  1. Mike Griffin
    January 13, 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
    January 13, 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
    January 13, 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 comment