Variable not defined in context when using Sql.In like an EXISTS clause

ServiceStack.OrmLite
1

I have the following query:

var q = _db.From<LRAAnalisi>()
           .Where(ana => ana.ContenitoreId == idContenitore)
           .And(ana => !Sql.In(ana.Id, _db.From<LRARisultato>()
                                          .Where(ris => ris.AnalisiId == ana.Id)
                                          .Select(ris => new { Id = ris.AnalisiId })
                              )
               )
           .Select(x => new { A = 1 });

The query returns

System.InvalidOperationException: 'variable 'ana' of type 
'LabReboot.ServiceModel.DBO.LRAAnalisi' referenced from scope '', but it
 is not defined'

We have similar queries written with a Sql.In that have a SqlExpression as a second parameter, but this is the only one that actually uses the outer parameter ana inside the subquery. This is a valid operation in every DBMS.
I’m expecting the following result in SQL:

SELECT 1 AS A
FROM LRAAnalisi AS ana
WHERE ana.ContenitoreId = @IdContenitore
AND ana.Id NOT IN(SELECT ris.AnalisiId AS Id
                  FROM LRARisultato AS ris
                  WHERE ris.AnalisiId = ana.Id)

The NOT IN clause actually represent a NOT EXISTS clause, that is not supported by OrmLite.

2

OrmLite doesn’t support nested typed sub expressions that uses an external referenced lambda variable. I’ll look to see if it’s something we can easily add support for, but architecturally it may not be something OrmLite’s Typed Expressions can support.

In the meantime you can use Custom SQL Condition, e.g:

var q = _db.From<LRAAnalisi>()
    .Where(ana => ana.ContenitoreId == idContenitore)
    .And("LRAAnalisi.Id NOT EXISTS (SELECT *
                  FROM LRARisultato
                  WHERE AnalisiId = LRAAnalisi.Id)");
3

Thanks for the Custom SQL condition.

If I may, you probably “just” need to pass the ParameterExpression of ana (in my case) to the function that elaborate the subexpression of the Sql.In.

In more abstract terms, you should have the same collection of ParameterExpression for the entire time you are visiting the lambda expression.
I had similar errors while playing with OrmLite APIs and everytime was because I wasn’t handling my ParameterExpressions correctly

Probably, this is the same issue related to Selecting a type instead of an anonymous type, that I asked you a couple of weeks ago.

4

Right the issue is that you can’t compile a lambda expression that uses captured lambda parameters in the sub method call expression and the original lambda parameter list doesn’t exist when the expression visitor visits the method call expression.

I’ve added a fallback in this commit that tries to compile the method expression with the original lambda expression parameters if calling expression.Compile() fails. While the workaround appears to work, it’s not a supported solution so anything that doesn’t work with this fallback wont be supported.

This change is available from v5.1.1 that’s now available on MyGet.

5

Thanks a lot, we will check if it works.

Does this change also fix the not-anonymous projection like .Select(x => new NotAnonymoustype { ObjectId = x.Id })? The exception was practically the same.