Does ServiceStack.OrmLite.JoinSqlBuilder allow you to create a simple query

Question

I am wondering if ServiceStack.OrmLite JoinSqlBuilder allows you 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 the part (a.Column2 = 2 OR b.Column3 = 3) . JoinSqlBuilder has a list of methods, such as Where<T>, And<T>, Or<T> , which allow you to add conditions to the 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 a way to build a.Column1 = 1 AND (a.Column2 = 2 OR b.Column3 = 3) with ServiceStack.OrmLite?

I know that I can do this with raw sql, but this is not an option, because I do not want to lose type safety and dialect independence.

Answer 1

I agree with kunjee that this is actually not something like Micro-orm. With that said, I can present two possible options ... neither what I would recommend in a full-blown ORM (EF or nHibernate) as a solution. But perhaps this will help request the best options.

Option 1 Create the line “Where clause” using reflection to preserve some “type safety”. You 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 / Mute your classes and disable table prefixes in ExpressionVisitor to allow the creation of the correct SQL. This will explode completely if 2 classes have the same property and are used in the 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;