Using SubSonic Query to on multiple tables

Question

I want to select rows from multiple tables using subsonic. For one table I can use Query object, but I don't know how I can add more than one tables to query.

Answer 1

You neet to join them, much like you would do in SQL. If you have a foreign key relationship in the schema, Subsonic is smart enough to figure the joins directly:

DataSet DS = DB.Select().From<Table1>().InnerJoin<Table2>().ExecuteDataSet();

If you don't have a FKI between the tables, you need to manually specify the columns from each table to create the join:

DataSet DS = DB.Select().From<Table1>().InnerJoin(Table1.FKIColumn,Table2.IDColumn).ExecuteDataSet();

Similarly you can create the Left/Right Outer joins,etc.

Remeber you can only join them on simple FKI constraints. For example I found no easy way to do "INNER JOIN Table2 on Table1.FKI = Table2.ID and Table2.CreateDate>Table1.CreateDate" directly from SubSonic.

And a big downside to using SubSonic multiple table joins is that you will run into troubles if you have identically named columns in both tables.