Does ServiceStack.OrmLite.JoinSqlBuilder allow to build a simple query

Question

I'm wondering if ServiceStack.OrmLite's JoinSqlBuilder allow to build the following simple query:

SELECT * FROM Table1 a
  INNER JOIN Table2 b ON ...
  WHERE a.Column1 = 1 AND (a.Column2 = 2 OR b.Column3 = 3);

The problem is to build (a.Column2 = 2 OR b.Column3 = 3) part. JoinSqlBuilder has a list of methods such as Where<T>, And<T>, Or<T> that allow to add conditions for a query.

For example, if i do:

builder
  .Join(...)
  .Where<Table1Poco>(a => a.Column1 == 1)
  .And<Table1Poco>(a => a.Column2 == 2)
  .Or<Table2Poco>(a => a.Column3 == 3)
  ...;

I will get:

... WHERE a.Column1 = 1 AND a.Column2 = 2 OR b.Column3 = 3;

Is there any way to build a.Column1 = 1 AND (a.Column2 = 2 OR b.Column3 = 3) with ServiceStack.OrmLite?

I know that i can do it with raw sql but it's not an option as i don't want to lose type safety and dialect independence.

Answer 1

I agree with kunjee that this is not really something a Micro-orm is good for. With that said, I can think of 2 potential options...neither of which are really something I would recommend over a full-blown ORM (EF or nHibernate) as a solution. But, maybe this will help solicit better options.

Option 1 - Build up a 'Where clause string' using reflection to keep some 'type safety'. You will still need to write a little SQL.

Example

var jn = new JoinSqlBuilder<Table1, Table2>();
jn = jn.Join<Table1, Table2>(s => s.Column1, d => d.Field1);

//using ExpressionVisitor because I didn't see a way to allow a Where clause string parameter to be used 
//on a JoinSqlBuilder method
var ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Table1>();
ev.Where(
    SqlHelper.ToSqlField<Table1>(x => x.Column1) + "={0} AND (" +
    SqlHelper.ToSqlField<Table1>(x => x.Column2) + "={1} OR " + SqlHelper.ToSqlField<Table2>(x => x.Column3) +
        "={2})", "1", "2", "3");

var sql = jn.ToSql() + ev.WhereExpression; 

Helper Class

public static class SqlHelper
{
    public static string ToSqlField<T>(Expression<Func<T, object>> expression)
    {
        //This should return something like 'Table1.Column1'
        return typeof(T).Name + "." + GetMemberInfo(expression).Name;
    }

    // Stolen from FluentNHibernate.ReflectionUtility
    public static MemberInfo GetMemberInfo<TEntity>(Expression<Func<TEntity, object>> expression)
    {
        MemberInfo memberInfo = null;

        switch (expression.Body.NodeType)
        {
            case ExpressionType.Convert:
                {
                    var body = (UnaryExpression)expression.Body;
                    if (body.Operand is MethodCallExpression)
                    {
                        memberInfo = ((MethodCallExpression)body.Operand).Method;
                    }
                    else if (body.Operand is MemberExpression)
                    {
                        memberInfo = ((MemberExpression)body.Operand).Member;
                    }
                }
                break;
            case ExpressionType.MemberAccess:
                memberInfo = ((MemberExpression)expression.Body).Member;
                break;
            default:
                throw new ArgumentException("Unsupported ExpressionType", "expression");
        }

        if (memberInfo == null) { throw new ArgumentException("Could not locate MemberInfo.", "expression"); }

        return memberInfo;
    }
}

Option 2 - Mess/Pollute your Classes and turn off Table prefixes in an ExpressionVisitor to allow the correct SQL to be generated. This will completely blow up if 2 classes have the same property and are used in a Where clause.

//Modify Table1 to include a reference to Table2 
public class Table1
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }

    [ServiceStack.DataAnnotations.Ignore]
    public Table2 Table2 { get; set; }
}

var ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Table1>();
ev.PrefixFieldWithTableName = false;

var jn = new JoinSqlBuilder<Table1, Table2>();
jn = jn.Join<Table1, Table2>(s => s.Column1, d => d.Field1);
ev.Where(x => x.Column1 == "1");
ev.Where(x => x.Column2 == "2" || ((Table2)x.Table2).Column3 == "3"); //do cast to avoid InvalidOperationException

var sql = jn.ToSql() + ev.WhereExpression;