I experienced the exact same scenario. I was able to insert, but not update.
The cause, I found, was that a primary key was not set on the table. Once the primary key was set, I was able to update.
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>();
Best Answer
Reserved words in c# can be handled in code by prepending them with the '@' symbol. For example,
fails with a compiler error, but
is just fine. This works for property names as well. You could probably upgrade your T4 templates to check a list of reserved words, and if there is a match, prepend the '@' symbol in front of the name.
As to the best way of adding this into Subsonic, I'd have to do more digging.