Support for SQL JOIN syntax is probably our most requested Query feature addition. As you might imagine, fully supporting JOINs while maintaining the simplicity of the SubSonic query syntax is no small chore.
Rob and I chatted about this late last week, and we both agreed that if SubSonic were to support JOINs, it should do so in a way that makes them easy to work with, and not merely port the SQL syntax into the parser. Personally, I find SQL JOIN syntax convoluted and truly believe that there must a more friendly and readable way to the same ends. In Revision 188, you'll find my first stab at making this happen.
First off, a few caveats:
- The capability is currently supported only on Sql Server
- Supplementary syntax (WHERE, ORDER BY) etc. are not supported. If they do actually work, it's only out of pure luck
- LEFT INNER JOIN support only
Creating a JOINed Query
To facilitate JOIN support, a new Query constructor overload has been added. This overload takes a table schema reference, and one or more table columns as parameters.
Query qry = new Query(Product.Schema, Product.ProductIDColumn,
Product.ProductNameColumn, Supplier.CompanyNameColumn, Category.CategoryIDColumn);
The column properties represent new static properties in the generated classes, so you'll need to regenerate any existing classes in order to perform these queries. This query syntax ultimately generates the following SQL and result set:
SELECT ProductID, ProductName, [J2].[CompanyName], [J3].[CategoryID]
FROM [dbo].[Products]
INNER JOIN [Suppliers] J2 ON [Products].[SupplierID] = [J2].[SupplierID]
INNER JOIN [Categories] J3 ON [Products].[CategoryID] = [J3].[CategoryID]
So consider this just a first pass, with more to come. As always, we welcome any feedback and ideas, so please add your comments!