The new join support in SqlExpression<T> is great.
However, there are two things that are not supported yet.
1) The ToCountStatement function is broken for joins if the where expression refers to columns from one of the joined tables.
e.g.
.Join<Table1, Table2>()
.Where<Table2>(x => x.Id == 1)
.ToCountStatement()
The generated SQL will be:
SELECT COUNT(*) FROM Table1 WHERE Table2.Id = 1
(I will report this as a new issue.)
2) There is no way to choose which columns to select, other than from the base table.
e.g. if do .Join<Table1, Table2>(),
I can’t then do .Select<Table2>(x => new { x.Column1, x.Column3 }).
Is this going to be supported in the future?
For the custom select it’s only going to support mapping to a custom POCO combined with fields from each table as described in:
https://github.com/ServiceStack/ServiceStack/blob/master/release-notes.md#selecting-multiple-columns-across-joined-tables
It’s in-line with how the existing API’s work so should be intuitive, without complicating the API surface.
Uri Hendler:
Reported issue for broken ToCountStatement:
https://github.com/ServiceStack/Issues/issues/102
Uri Hendler:
Are there any plans to extend the join support in the JoinSqlBuilder to match what’s now available in SqlExpression i.e. to allow complex join expressions?
I have a bunch of queries where I need complex joins (only works in SqlExpression) and also need to specify which columns to select (only works in JoinSqlBuilder).
I’m currently stuck with building the SQL manually, which is a bit of a maintenance headache.
There aren’t plans to extend SqlExpression to specify an anonymous types for a custom select.
What’s wrong with creating a POCO that specifies the types/columns to Select? It ends up being more re-usable since the schema of anon types aren’t visible beyond method boundaries.
Uri Hendler:
I should have been more clear about my use case.
An example is where I’m joining two tables which have some identically named columns. I need to select some of those columns from the second table. If I create a new POCO and use the SqlExpression convention, it will select the columns from the first table.
I don’t know if this is such a common use case, but I have it often enough to be a pain.
You can use the full-qualified name to select unambiguous column names, e.g: ‘{Table}{Field}’ in addition to just '{Field}'
more info at:
https://github.com/ServiceStack/ServiceStack.OrmLite/#selecting-multiple-columns-across-joined-tables
The version on NuGet just had this fallback for Id’s, but the version on MyGet allows fully qualified names for any column.
Uri Hendler:
Great, that should work.