Added Contstraint Based Expressions To Subsonic

0 comments suggest edit

NUnit 2.4 introduces a really nice programming model they call Constraint-Based Assert Model. I believe MbUnit 2.4 will also have this. I really like this approach to building asserts because it reads almost like English.

Assert.That( myString, Is.EqualTo("Hello") );

Look at that fine piece of prose!

I’m so enamored of this approach I thought I’d try to bring it to Subsonic. Here is an example of two existing approaches to create a Query in Subsonic.

new Query("Products").WHERE("ProductId < 5");
new Query("Products").WHERE("ProductId", Comparison.LessThan, 5);

Now what don’t I like about these? Well in the first one, there’s no intellisence to guide me on making sure I choose a valid operator. Not only that, if that 5 is a variable instead, I’m doing some string concatenation, which I find to be ugly and harder to read such as this:

new Query("Products").WHERE("ProductId < " + productId);

The second one is much better in that I get the benefit of Intellisense and it is pretty readable and understandable. But can we do better? I mean, who talks like that? “Hand me the nails where the length is comparison greater than five.”

This is where I find the Constraint Based model to be very elegant and readable.

new Query("Products").WHERE("ProductId", Is.LessThan(5));

Now if you’re looking at this and wondering, where is the intellisense for the table name and column name? Don’t worry, it’s there. I used strings here for brevity. But here’s the final query with everything strongly typed.

new Query(Tables.Product, "Northwind")
  .WHERE(Product.Columns.ProductID, Is.LessThan(5))

This is just my first pass at this for Subsonic. I need to get a better understanding of how these queries are being built so I can add the following syntax next:

new Query("Products").WHERE("ProductId", Is.In(1,2,3,4,5);
// AND
new Query("Products").WHERE("ProductId", Is.In(new int[]{1,2,3,4});

This code has been committed to the trunk and is not yet in any release. It is pretty simple so far.

I wonder if I should propose the following syntax helper:

Select.From("Products").WHERE("ProductId", Is.LessThan(5));

//Where Select.From is defined as:

public static class Select
  public static Query From(string tableName)
    return new Query(tableName);

Or is that taking this too far. Thoughts?

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.



28 responses

  1. Avatar for May 22nd, 2007

    You've been kicked (a good thing) - Trackback from

  2. Avatar for James Gregory
    James Gregory May 22nd, 2007

    I like it! Syntax sugar tastes nice.

  3. Avatar for Ian
    Ian May 22nd, 2007

    Looks good! I have one very small comment about the uppercase WHERE method; it's SCREAMING AT ME VERY LOUDLY AND DRAWING MY ATTENTION AWAY FROM THE FLOW OF THE "SENTENCE" ;-)
    Is there a "Where" method?

  4. Avatar for Damien Guard
    Damien Guard May 22nd, 2007

    I'd love to see:
    List<Product> products =
    Have to agree with Ian that WHERE violates .NET naming guidelines.

  5. Avatar for Andy Stopford
    Andy Stopford May 22nd, 2007

    Phil it's actually MbUnit Gallio (v3) that will feature this not v2.*. It's likely to based on the 3.5 expression tree's but will as much as possible have allowance for NUnit's expressions.

  6. Avatar for Eric Kemp
    Eric Kemp May 22nd, 2007

    The uppercasing of the WHERE designates that it is equivalent to SQL statement of the same value. The overall idea of the existing Query parser was to create an experience, syntax, and structure that was as close to constructing a SQL query as possible.

  7. Avatar for Chris
    Chris May 22nd, 2007

    I wouldn't do the "SELECT return new Query" thing, just stick with the Query . . . Too many options to do the same thing just muddles the intent and confuses. My first question upon seeing Query and Select would be "How is Query different than Select?" To which the answer would be "None", which would just annoy me. And I would also know that the developer was just a little too in love with his own craftiness (or lack thereof). :)

  8. Avatar for kevin
    kevin May 22nd, 2007

    If you're gonna go this route then I'd even propose:
    new Query("Products").WHERE("ProductId").IsLessThan(5);

  9. Avatar for Steve Owens
    Steve Owens May 22nd, 2007

    I agree with [)amien. The d00dads framework in MyGeneration lets you specify an intellisensible name for just about everything.
    is SO much better than doing
    We had a consultant begin building a web app for us and everything was using datareaders and literals for column names. There were three errors I found (so far) in the spelling of some column names within the literal.
    Life is too short to worry about literals blowing up your query at runtime.

  10. Avatar for Richard
    Richard May 22nd, 2007

    Oren did something similar for NHibernate, but used generated classes and operator overloading so that you can write a query like:
    Where.Post.Blog.Author.Name == "Ayende"

  11. Avatar for kevin
    kevin May 22nd, 2007

    I'd go even further and propose:
    new Query("Products").WHERE("ProductId").IsLessThan(5);
    new Query("Products").WHERE("ProductId").IsEqualTo(5);
    And yes, strongly typed values would be ideal.

  12. Avatar for Michael F. Starke
    Michael F. Starke May 22nd, 2007

    gee... that's starting to look suspiciously like LINQ...

  13. Avatar for Jeff Lewis
    Jeff Lewis May 22nd, 2007

    My homemade DAL uses the following syntax:
    List<Product> products =
    Products.Where.ProductID(12).And.Description(Opr.LIKE, "%Hello%");
    Now that LINQ is just around the corner though, I'm not sure I'd waste the effort.

  14. Avatar for JimShelly
    JimShelly May 22nd, 2007

    "Select.From("Products").WHERE("ProductId", Is.LessThan(5));", I like it.
    Using "Query" is no big deal, but for readability from a sql standpoint, using the suggested syntax makes since.

  15. Avatar for May 22nd, 2007

    You've been kicked (a good thing) - Trackback from

  16. Avatar for Haacked
    Haacked May 22nd, 2007

    I have to be honest, I agree with everyone that WHERE should be Where. It follows naming guidelines and when you're writing code, the WHERE is not special in any way. It's just another method. Why should it violate casing rules.
    Why not apply the same thing to QUERY?

  17. Avatar for Haacked
    Haacked May 22nd, 2007

    Also, not everyone writes their SQL using all caps for WHERE anyways, though it appears to be the standard if you look at MSDN docs.

  18. Avatar for Haacked
    Haacked May 22nd, 2007

    @Kevin - the reason I didn't do that is that it was easier to do it my way given the existing query architecture. Took me all of 15 to 30 minutes.

  19. Avatar for Haacked
    Haacked May 23rd, 2007

    Regarding "WHERE" vs "Where", Rob is asking people to vote on whether we should go through with this breaking change. I say YES!

  20. Avatar for Daniel
    Daniel May 23rd, 2007

    This is actually related to one thing that's held me off on SubSonic: What happens to all this DAL when LINQ to SQL is released?

  21. Avatar for Scott
    Scott May 23rd, 2007

    Is "where" an object I can create and pass into a Query constructor or set on Query member? Or is Query serializable?

  22. Avatar for Haacked
    Haacked May 23rd, 2007

    @Daniel - It'll still be around. Linq can work over collections, so they can work together.
    Also, you can write more productive code in the future or you can start doing it now. Why be the one always waiting for the next thing to come out instead of starting with what is already here. ;)

  23. Avatar for Dave
    Dave May 23rd, 2007

    Why not just implement LINQ to Subsonic? Ayende did this for NHibernate and it seemed pretty straight-up.
    The Catch-22 (33?) w/ a Query Object pattern is:
    1. If you go string based it is (+) shorter and more easily made dynamic (e.g. feed this input value into the Where object constructor's field argument) but (-) breaks automatic refactorings (ReSharper, Refactor, VS).
    2. If you go with constant classes for table and column names these (-) your code become looooong and (+) automated refactorings come back. The last (-) becomes significantly compounded by a Fluent Interface, e.g. This().And().That().Or().TheOtherThing()
    3. If you go with LINQ to Subsonic it (+) gives you very "natural" syntax that is type safe but (-) dynamic-type queries (might be, not sure) are harder than 1. and (-) is a bit harder to implement.
    Of course there's probably no harm in having two Query Objects for different purposes. LINQ to Subsonic would be a tasty thing indeed, but, seriously, why bother until fx 3.5 hits the streets? I'd say that you could probably find a design that lets you do both 1 and 2... maybe with a built-in validation against string-based inputs?

  24. Avatar for Marcos
    Marcos May 24th, 2007

    Yah, I'll accelerate my carpal tunnel so I can type WHERE...

  25. Avatar for James Curran
    James Curran May 24th, 2007

    How 'bout we try this:
    static public class Select
    static public TableList From
    { get { return new TableList(); }

    The TableList would be a generate class, like Tables:

    public class TableList
    Query Product { get {return new Query("Product"); }}
    // other tables here

    That would allow code like
    with full Intellisense.

  26. Avatar for Haacked
    Haacked May 24th, 2007

    @James - I like it. I'm already hammering out that very idea among others with Rob. It's very similar to Richard's comment on what Orein has done.

  27. Avatar for Ayende Rahien
    Ayende Rahien May 24th, 2007

    Why not code generating the whole thing?
    In NHibernate / Active REcord, NHQG will give you:
    Where.Product.ProductId < 5

  28. Avatar for Haacked
    Haacked May 24th, 2007

    @Ayende - Didn't you read my last comment. ;) After reading your post, I do want to do something similar.