JoinSqlBuilder broken since 4.0.52

ServiceStack.OrmLite
1

The old JoinSqlBuilder has been broken since v4.0.52 (after the changes to make all sql expressions parameterized).

I know it’s deprecated, but I have a fair amount of code still relying on it, which would be a big job to refactor.

Can this be fixed?

Here’s an example:

public class TestTable
{
    [PrimaryKey]
    public int Id {get;set;}
	
    public string Name {get;set;}
}

public class TestChildTable
{
    [PrimaryKey]
    public int Id {get;set;}
	
    [References(typeof(TestTable))]
    public int TestTableId {get;set;}
	
    public string Name {get;set;}
}

var jn = new JoinSqlBuilder<TestTable, TestChildTable>();

jn.Join<TestTable, TestChildTable>(x => x.Id, y => y.TestTableId)
  .Where<TestTable>(x => x.Name == "Bob")
  .Or<TestChildTable>(x => x.Name == "Bob")
  .SelectAll<TestTable>()
  .SelectAll<TestChildTable>();
  
var sql = jn.ToSelectStatement();

/*
SELECT
"TestTable"."Id","TestTable"."Name","TestChildTable"."Id","TestChildTable"."TestTableId","TestChildTable"."Name" 
FROM "TestChildTable" 
INNER JOIN  "TestTable" ON "TestTable"."Id" = "TestChildTable"."TestTableId"  
WHERE ("TestTable"."Name" = @0) OR ("TestChildTable"."Name" = @0) 
*/
2

It’s a losing proposition trying to maintain JoinSqlBuilder it was never created, maintained, documented or even used by us and was contributed before the typed SqlExpressions added support for joins and relied on the non-parameterized behavior of Typed SqlExpressions so it no longer works which we’re not going to revert back to. Only thing I’d be able to do would be to collect the params created during the join expression and make it available on the instance but I’d prefer to remove the JoinSqlBuilder class instead since it doesn’t fit with the rest of OrmLite.

But your best path forward is to convert it to use SqlExpressions as JoinSqlBuilder should no longer be necessary. It looks like this query would benefit from our new SelectMulti support which should be able to be rewritten in something like:

var q = db.From<TestTable>()
    .Join<TestTable, TestChild>((x,y) => x.Id == y.TestTableId)
    .Where(x => x.Name == "Bob")
    .Or<TestChild>(x => x.Name == "Bob");

List<Tuple<TestTable,TestChild>> rows = db.SelectMulti<TestTable,TestChild>(q);
3

Actually JoinSqlBuilder was already removed in this latest release on April 29 since it’s no longer valid after having removed non parameterized query support from SqlExpressions.

For preservation it’s available in ServiceStack.OrmLite.Tests/Support/JoinSqlBuilder.cs.

Prior to the next release you can revert to non-parameterized queries with:

OrmLiteConfig.UseParameterizeSqlExpressions = false;.

However this option (and all dependent functionality) has been removed from next release, so it’s recommended to migrate to using Typed SQL Expressions instead.

4

OK, I’ll have to start replacing the JoinSqlBuilder with typed SqlExpressions instead.

The one feature I miss most from there is the ability to select from an arbitrary number of tables, e.g.:

.Select<Customer>(x => new { x.Id, x.Name })
.Select<CustomerAddress>(y => new { y.AddressLine1, y.AddressLine2 })

The SqlExpression allows at most 3 tables:

.Select<Table1, Table2, Table3>

Would you consider adding overloads to allow selecting from 4 or 5 tables? That would cover most of my queries.

I could use SelectMulti but that would require mapping each element from the tuple onto my query DTO. Do you have any suggestions to make this easier?

5

To select multiple tables you would do:

.Select<Customer,CustomerAddress>((x,y) => new { x.Id, x.Name, y.AddressLine1, y.AddressLine2 })

I had already expanded it to 4, but I’ve now expanded it to 7 tables in this commit.

Not following where the friction is with SelectMulti as it auto populates your existing table POCO types but this answer shows different ways of accessing custom fields.

There’s also support for .Select<dynamic> added in the latest release that lets you access custom fields using dynamic, e.g:

var q = db.From<Employee>()
    .Join<Department>()
    .Select<Employee, Department>(
        (e, d) => new { e.FirstName, e.LastName, d.Name });

var results = db.Select<dynamic>(q);

var sb = new StringBuilder();
foreach (var result in results)
{
    sb.AppendLine(result.FirstName + "," + result.LastName + "," + result.Name);
}