OrmLite SqlExpression Extensions that adds LeftOuterJoin

_README.md

LeftOuterJoin Extension Methods for ServiceStack.OrmLite

ServiceStack.OrmLite's SqlExpression<T> class provides several join methods like Join, LeftJoin, RightJoin, etc., but does not include a LeftOuterJoin implementation. This extension provides that functionality.

Usage

Basic Left Outer Join

using YourNamespace;

// Join Patient with PatientDetails
var query = db.From<Patient>()
    .LeftOuterJoin<Patient, PatientDetails>((p, d) => p.Id == d.PatientId);

// Produces SQL like:
// SELECT * FROM Patient 
// LEFT OUTER JOIN PatientDetails ON (Patient.Id = PatientDetails.PatientId)

With Table Alias

var options = new TableOptions { Alias = "details" };
var query = db.From<Patient>()
    .LeftOuterJoin<Patient, PatientDetails>(
        (p, d) => p.Id == d.PatientId, 
        options
    );

// Produces SQL like:
// SELECT * FROM Patient 
// LEFT OUTER JOIN PatientDetails AS details ON (Patient.Id = details.PatientId)

With Different Source Table

When you need to join using a different source table than the main table:

var query = db.From<Patient>()
    .Join<Doctor>((p, d) => p.DoctorId == d.Id)
    .LeftOuterJoin<Patient, Doctor, DoctorGroup>(
        (d, g) => d.GroupId == g.Id
    );

// Produces SQL like:
// SELECT * FROM Patient 
// INNER JOIN Doctor ON (Patient.DoctorId = Doctor.Id)
// LEFT OUTER JOIN DoctorGroup ON (Doctor.GroupId = DoctorGroup.Id)

Why Use LeftOuterJoin?

While OrmLite's LeftJoin method works similarly, using LeftOuterJoin makes your code more explicit and matches the actual SQL being generated. This is especially helpful when:

1. Porting SQL queries that explicitly use LEFT OUTER JOIN
2. Making code more readable and self-documenting
3. Maintaining consistency with database-specific optimizations that might treat LEFT OUTER JOIN differently

Implementation Details

The extension methods work by:

1. Getting the target table name using OrmLite's metadata
2. Converting the join expression to SQL
3. Constructing a raw SQL LEFT OUTER JOIN clause
4. Using CustomJoin to apply the join

This ensures that the generated SQL exactly matches what you'd write by hand, while maintaining all the benefits of OrmLite's fluent interface and type safety.

OrmLiteLeftOuterJoinExtensions.cs

using System;
using System.Linq.Expressions;
using System.Reflection;
using ServiceStack.Model;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Support;

namespace YourNamespace
{
    public static class LeftOuterJoinExtensions
    {
        public static SqlExpression<T> LeftOuterJoin<T, Target>(
            this SqlExpression<T> sqlExpression,
            Expression<Func<T, Target, bool>> joinExpr
        )
        {
            var targetTableName = sqlExpression.DialectProvider.GetQuotedTableName(
                typeof(Target).GetModelMetadata()
            );
            var onClause = sqlExpression.Visit(joinExpr).ToString();
            var joinSql = $"LEFT OUTER JOIN {targetTableName} ON {onClause}";
            return sqlExpression.CustomJoin(joinSql);
        }

        public static SqlExpression<T> LeftOuterJoin<T, Target>(
            this SqlExpression<T> sqlExpression,
            Expression<Func<T, Target, bool>> joinExpr,
            TableOptions options
        )
        {
            var logger = RXNT.Providers.Provider.Logger;
            var targetTableName = sqlExpression.DialectProvider.GetQuotedTableName(
                typeof(Target).GetModelMetadata()
            );
            logger.Info("TargetTableName", new { TargetTableName = targetTableName });
            var alias = sqlExpression.DialectProvider.GetQuotedName(options.Alias);
            var onClause = sqlExpression
                .Visit(joinExpr)
                .ToString()
                ?.Replace(targetTableName, alias);
            var joinSql = $"LEFT OUTER JOIN {targetTableName} {alias} ON {onClause}";
            return sqlExpression.CustomJoin(joinSql);
        }

        public static SqlExpression<T> LeftOuterJoin<T, Source, Target>(
            this SqlExpression<T> sqlExpression,
            Expression<Func<Source, Target, bool>> joinExpr
        )
        {
            var targetTableName = sqlExpression.DialectProvider.GetQuotedTableName(
                typeof(Target).GetModelMetadata()
            );
            var onClause = sqlExpression.Visit(joinExpr).ToString();
            var joinSql = $"LEFT OUTER JOIN {targetTableName} ON {onClause}";
            return sqlExpression.CustomJoin(joinSql);
        }

        public static SqlExpression<T> LeftOuterJoin<T, Source, Target>(
            this SqlExpression<T> sqlExpression,
            Expression<Func<T, Source, Target, bool>> joinExpr
        )
        {
            var targetTableName = sqlExpression.DialectProvider.GetQuotedTableName(
                typeof(Target).GetModelMetadata()
            );
            var onClause = sqlExpression.Visit(joinExpr).ToString();
            var joinSql = $"LEFT OUTER JOIN {targetTableName} ON {onClause}";
            return sqlExpression.CustomJoin(joinSql);
        }

        public static SqlExpression<T> LeftOuterJoin<T, Source, Target>(
            this SqlExpression<T> sqlExpression,
            Expression<Func<Source, Target, bool>> joinExpr,
            TableOptions options
        )
        {
            var targetTableName = sqlExpression.DialectProvider.GetQuotedTableName(
                typeof(Target).GetModelMetadata()
            );
            var onClause = sqlExpression.Visit(joinExpr).ToString();
            var alias = sqlExpression.DialectProvider.GetQuotedName(options.Alias);
            var joinSql = $"LEFT OUTER JOIN {targetTableName} {alias} ON {onClause}";
            return sqlExpression.CustomJoin(joinSql);
        }
    }
}