Using SubSonic Query for Multiple Tables

You can join them just like in SQL. If you have a foreign key relationship in your schema, Subsonic is smart enough to map joins directly:

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

      

        
        
        
      

    

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

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

      

        
        
        
      

    

In a similar way, you can create Left / Right Outer connections, etc.

Remember that you can only join them with simple FKI restrictions. For example, I haven't found an easy way to make "INNER JOIN Table2 to Table1.FKI = Table2.ID and Table2.CreateDate> Table1.CreateDate" directly from SubSonic.

And the big drawback to using joins with multiple SubSonic tables is that you run into problems if you have the same column names in both tables.