ansaurus

Question

SubSonic 3.0.0.3 How do we use joins with the query tool?

Answer 1

+3 A:

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>();

Ben Griswold 2009-08-03 04:48:11

Thanks for the feedback. I guess I was stumped by the fact that my tables objects don't have Schema properties in 3.0.0.3, nor can I find column references of type IColumn. I find the only method that works in version 3 is the one that uses Generics. The reference document seems to only pertain completely to version 2. Thank you for pointing me in the right direction.

jcomet 2009-08-03 13:07:24

@jcomet - Please see my updates inline with my answer. Good luck.

Ben Griswold 2009-08-03 14:46:42

Thanks again Ben, you have been very helpful. I think, though, that your examples are correct for 2.*, but not for 3.*. My LeftOuterJoin argument types are SubSonic.Schema.IColumn. Perhaps my configuration is incorrect.

jcomet 2009-08-03 18:56:13

Answer 2

+1 A:

jcomet, Your configuration is not incorrect. Ben is wrong: his code only works for 2.x and not 3.0.x, which i know first hand because I am having the same problem. Significant changes have been made which cause the above code not to work anymore. To get you one more step ahead in your problem, each table in your database now has two classes; ex: "Customers" and "CustomersTable". You will find that if you can figure out how to instantiate the CustomersTable object with a DataProvider, you can reference columns and it returns them in IColumn format and not string format. Good Luck, and Ben, update your SubSonic before you go trying to answer questions.

2009-08-10 13:41:51

ansaurus

tags:

views:

answers:

SubSonic 3.0.0.3 How do we use joins with the query tool?

related questions