SubSonic 3.0.0.3 How do we use joins with the query tool

subsonicsubsonic3

I am trying to use the query tool, but I can't figure out how to specify the correct paremeter for the join. This is as far as I get:

List<Tran> = new Select().From("Trans").LeftOuterJoin(

according to intellisense, parameters of type SubSonic.Schema.IColumn are expected next. How do I supply the correct parameters?

Best Answer

You would provide the columns based on the tables included in the join. For example, if you are joining Table Trans and Table UserTrans on TransId, your statement would be something along the lines of the following: 

SubSonic.SqlQuery query = DB.Select()
    .From(Trans.Schema)
    .LeftOuterJoin(Trans.TransIDColumn, UserTrans.TransIDColumn);

According to the SubSonic Simple Query Tool Docs, you have three options when it comes to Left Outer Joins:

Left Outer Join With Generics 

    SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
        .From<Customer>()
        .LeftOuterJoin<Order>();

Left Outer Join With Schema 

    SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
        .From(Customer.Schema)
        .LeftOuterJoin(Order.CustomerIDColumn, Customer.CustomerIDColumn);

Left Outer Join With Magic Strings 

   SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
        .From("Customers")
        .LeftOuterJoin("Orders");

It appears you are favoring "Left Outer Join With Schema." Note, however, that each of the options above return a SubSonic SqlQuery reference. I'm not sure you can do as you hope, return a List of Tran, with the LeftOuterJoin syntax. You may wish to further consult the docs on this.

UPDATE:

Per your comment, I think we can get you closer to what you want. The .LeftOuterJoin excepts arguements of type TableSchema.TableColumn and you can result a generic list by appending .ExecuteTypedList<> to the select.

List<Tran> result = DB.Select()
    .From<Trans>()
    // parameters are of type TableSchema.TableColumn
    .LeftOuterJoin(Trans.TransIDColumn, UserTrans.TransIDColumn);
    .ExecuteTypedList<Tran>();
Related Topic