ServiceStack OrmLite LeftJoin Issue

Question

I'm using ServiceStack OrmLite JoinSQLBuilder with a left join and have found an issue. Suppose I have 2 tables, TableA and TableB and wanted to join on more than a single value.

In SQL I would do something like this:

SELECT  
    TableA.Name, 
    TableB.Value
FROM    
    TableA
LEFT JOIN 
    TableB 
ON     
    TableB.AId = TableA.Id
    AND TableB.PostCode = '12345'  

Now the JoinSQLBuilder only allows joins on a single column and generates SQL like so

SELECT  
    TableA.Name, 
    TableB.Value
FROM    
    TableA
LEFT JOIN 
    TableB 
ON     
    TableB.AId = TableA.Id
WHERE
    TableB.PostCode = '12345'  

Which is not the same thing at all!

Is there any way around this in ServiceStack OrmLite? Here is an example of left joins with where clauses: Left Join With Where Clause

Answer 1

You should be able to use the new support for JOIN's in OrmLite's Typed SqlExpressions. It's best to use the latest v4.0.23 release on MyGet which includes improved support for selecting multiple columns across joined tables.

With the new JOIN API's you can do what you need with something like:

public class TableA
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class TableB
{
    public int AId { get; set; }
    public string PostCode { get; set; }
    public string Value { get; set; }
}

public class TableABFields
{
    public string TableAName { get; set; }
    public string TableBValue { get; set; }
}

var results = db.Select<TableABFields,TableA>(q =>
    q.Join<TableA,TableB>((a,b) => b.AId == a.Id && b.PostCode == "12345"));

Alternative API using an explicit SQL Expression:

var q = db.From<TableA>();
// or 
var q = OrmLiteConfig.DialectProvider.SqlExpression<TableA>();


q.Join<TableA,TableB>((a,b) => b.AId == a.Id && b.PostCode == "12345");

var results = db.Select(q);