sql - ServiceStack.OrmLite.JoinSqlBuilder 是否允许构建一个简单的查询

Question

我想知道 ServiceStack.OrmLite 的 JoinSqlBuilder 是否允许构建以下简单查询:

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

问题是构建 (a.Column2 = 2 OR b.Column3 = 3)部分。
JoinSqlBuilder 有一个方法列表，例如 Where<T>, And<T>, Or<T>允许为查询添加条件。

例如，如果我这样做:

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

我会得到:

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

有什么办法可以建 a.Column1 = 1 AND (a.Column2 = 2 OR b.Column3 = 3)使用 ServiceStack.OrmLite？

我知道我可以用原始 sql 来做，但这不是一个选项，因为我不想失去类型安全和方言独立性。

Answer 1

我同意 kunjee 的观点，这并不是 Micro-orm 真正适合的东西。话虽如此，我可以想到 2 个潜在的选择......这两个选项都不是我真正推荐的，而不是一个成熟的 ORM(EF 或 nHibernate)作为解决方案。但是，也许这将有助于征求更好的选择。

选项 1 - 使用反射建立一个“Where 子句字符串”以保持一些“类型安全”。您仍然需要编写一些 SQL。

示例

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 类

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;
    }
}

选项 2 - 弄乱/污染您的类并关闭 ExpressionVisitor 中的表前缀以允许生成正确的 SQL。如果 2 个类具有相同的属性并在 Where 子句中使用，这将完全失败。

//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;