SQLExpression.Where: Variable 'x' of type 'PROD' referenced from scope '', but it is not defined

ServiceStack.OrmLite
1

We have some code that tries to build a SQL Expression using OrmLite.

For some reason it is failing when it gets to our extension method WithChangedColumns<T> where it fails at the following line expression.Where(x => x.UpdateColumns.ContainsAny(formatted) with the following exception:

variable 'x' of type 'Deltas.PROD' referenced from scope '', but it is not defined

public class QueryableService : Service
{
    public object Any(PRODRequest request) 
    { 
        return new PRODResponse 
        { 
          Records = Db.DeltaQuery<PROD>(request) 
         }; 
    }
}

public static class SqlExpressionExtensions
{
   public static T[] DeltaQuery<T>(
        this IDbConnection connection,
        AbstractDeltaRequest request) where T : IAuditRecord =>
            connection.Select(connection.From<T>()
                .WithActionType(request.ActionType)
                .FromAuditDate(request.EffectiveFrom)
                .WithChangedColumns(request.Columns)
                .Ordered(request.Direction)
                .Paged(request)).ToArray();

    public static SqlExpression<T> WithChangedColumns<T>(this SqlExpression<T> expression, string[] updatedColumns) where T : IAuditRecord
    {
        var formatted = updatedColumns?.Select(c => $$"[{c}]").ToArray();

        return formatted.IsNullOrEmpty()
            ? expression : expression.Where(x => x.UpdateColumns.ContainsAny(formatted));
    }
}   

public interface IAuditRecord
{
    string UpdateColumns { get; set; }
}

public class PROD : IAuditRecord  
{
    public string UpdateColumns { get; set; }
}
2

You can’t call a custom method inside a Typed SqlExpression, keep in mind it needs to be parsed, understood and converted into an SQL expression which is then executed on the server - this is why every expression in SqlExpression needs to be known by OrmLite ahead of time and why you’ll need to stick with the examples in the OrmLite docs.

Once you execute the SqlExpression then you have access to the full power of LINQ to objects on the returned dataset as it’s being applied directly to an in-memory list.

Something else to watch out for is generic type T in SqlExpression<T> always needs to be the concrete Type that maps to an OrmLite POCO Table, i.e. It can’t be an interface as then the table cant be inferred.

3

Is there a ServiceStack.OrmLite idiom for generating a Where clause with a Like operator?

5

Comparing strings with StartsWith/EndsWith/Contains will convert to a LIKE statement