CODEWITHSUNDEEP
linq-to-sqlservicestackormlite-servicestack
sam360
sam360

Reputation: 1131

How to use subqueries in ServiceStack ORMLite

I am using ServiceStack ORMLite and need to perform a query such below:

SqlServerExpressionVisitor<Contact> sql = new SqlServerExpressionVisitor<Contact>();
SqlServerExpressionVisitor<Account> accSql = new SqlServerExpressionVisitor<Account>();

var finalSql = sql.Where(a=> 
   (from a1 in accSql where a1.Type == "Client" 
   && a1.Id==a.AccountId select a1).Any());

When perform this query, i get a lambda error "a" is not defined in the scope. "a" here is the reference to the variable defined for the parent Where method call.

How can I use the ExpressionVisitor to perform subqueries in the WHERE clause?

UPDATE: I created my own SqlServiceExpressionVisitor which allows me to customize how ORM generates the SQL statements. I also added class such as below:

public static class SQL
{
    public static bool ExistsIn<T>(T Value, string subquery, params T[] parameters)
    {
        return true;
    }

    public static bool ExistsIn<T, TItem>(T Value, SqlExpressionVisitor<TItem> innerSql)
    {
        return true;
    }

    public static SqlExpressionVisitor<T> Linq<T>() 
    {
        return OrmLiteConfig.DialectProvider.ExpressionVisitor<T>();
    }
}

Then extended the VisitMethodCall to take my new class into account and call my custom method accordingly:

internal object VisitSQLMethodCall(MethodCallExpression m)
{
    string methodName = m.Method.Name;
    if (methodName == "ExistsIn")
    {
        string quotedColName = Visit(m.Arguments[0] as Expression).ToString();
        dynamic visit = Visit(m.Arguments[1]);

        string innerQuery = (visit is string) ? visit.ToString().Trim('"') : visit.ToSelectStatement();
        if (m.Arguments[2] != null)
        {
            List<object> fields = VisitExpressionList((m.Arguments[2] as NewArrayExpression).Expressions);
            int count = 0;
            foreach (var field in fields)
            {
               innerQuery = innerQuery.Replace("@" + count.ToString(), field.ToString());
               count++;
            }
        }

        return new PartialSqlString(string.Format("{0} IN ({1})", quotedColName, innerQuery));
    }
}

The results are very promising, here is how it can be used:

.Where(a => SQL.ExistsIn(a.AccountId, SQL.Linq<Account>()
    .Where(acc => acc.Name.Contains("a")).Select(acc => acc.Id)))

Above generates the proper inner SQL, however if I include a reference from the parent Query, again the system calls the return Expression.Lambda(m).Compile().DynamicInvoke(); which produce the same error!

SQL.Linq<Contact>().Where(a => SQL.ExistsIn(a.AccountId, SQL.Linq<Account>()
  .Where(acc => acc.Id == a.AccountId).Select(acc => acc.Id)))

The above generates the error: parameter "a" is not defined in the scope. I guess i only need to somehow add a parameter definition to the second Visit call in my custom method but I have not figured out how yet! Any help is appreciated!

Upvotes: 1

Views: 3803

Answers (1)

Guru Kathiresan
Guru Kathiresan

Reputation: 414

There is no documentation in the Wiki; you need to look at the code to see how to use the join feature. For your problem the code should look something like this :

        var jb = new JoinSqlBuilder<Contact, Account>()
            .Join<Contact, Account>(x => x.AccountId, x => x.Id)
            .Where<Account>(x => x.Type == "Client")
            .SelectCount<Contact>(x => x.Id);
        var sqlStr = jb.ToSql();
        bool isAvailable = dbManager.Connection.SqlScalar<int>(sqlStr) > 0;

For complex subqueries, the current version of JoinBuilder is not useful. I personally use DbExtensions from http://www.nuget.org/packages/DbExtensions/ along with Ormlite. Since I have extended Ormlite's T4 to automatically generate the Column name, Table, I use the DbExtension like this :

        SqlBuilder builder = new SqlBuilder();
        builder = builder.SELECT("*").
                FROM(Contact.TABLE_NAME).WHERE(Contact.COLUMN_AccountId +" = " + id.ToString());
        var sql = builder.ToString();
        return dbConnection.FirstOrDefault<Contact>(sql, builder.ParameterValues.ToArrayEx());

Upvotes: 4

Related Questions