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.

Railroader

As far as I know it is not there. And Micro-orm is good for this kind or things. You can fall back to plain old query for complicated scenarios. Hopefully you know Query<T>() function you can use that and pass a parameter if you wish. – Follow

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; 

Sedgemoor

Thank you for a complete answer. I've come to smth similar to option 1 - to write my own query builder over OrmLite's ExpressionVisitor. I disagree that joins shouldn't be a task for micro orm because joins are actualy the core thing in relational databases itselves. I don't have a very complex join here. The first thing i want from orm is complete dialect independence so raw sql isn't an option. The second one is possible (but not required) typesafety. The second point is just about managing DTOs. It's just an oppinion. Thanks again for an answer. – Railroader