Linq expression subsonic 3.0.0.3

Question

Hello,

I want to 'build' a combined query for Subsonic 3.0.0.3, what is the best way for this?

I tried;

        Expression<Func<Person, bool>> exp = p => true;
        Expression<Func<Person, bool>> fContinent = p => p.ContinentID == 1;
        Expression<Func<Person, bool>> fType = p => p.TypeID == 1;
        exp = Expression.Lambda<Func<Person, bool>>(Expression.AndAlso(exp, fContinent), exp.Parameters);
        exp = Expression.Lambda<Func<Person, bool>>(Expression.AndAlso(exp, fType), exp.Parameters);
        var personList = Person.Find(exp);

But that will give the exception "The binary operator AndAlso is not defined ..."

I also tried using predicates but that will throw exceptions as well (Expression.Invoke is not supported).

In subsonic 2 I would have used the SqlQuery object, but I would like to know the proper way to do this in version 3 using linq / expressions.

Answer 1

Have you tried And instead of AndAlso?

The right way to do this is to combine the lambda expression bodies, like this:

exp = Expression.Lambda<Func<Person, bool>>(
    Expression.And(exp.Body, fContinent.Body), exp.Parameters);

Even if And is supported by your query provider, you'll also need to replace the parameter references in fContinent's Body with references to the parameter defined in exp--as is, your two expression bodies (combined with And) reference two distinct parameters, each named p.

See my answer to this question for the cleanest method to replace expression parameters.

Answer 2

I asked this question, but I am using the combined query in subsonic just like you.

In short, you want to use a PredicateBuilder to build up the query. When you want to execute it in your subsonic object (assuming ActiveRecord), use code like this:

var predicate = /* Build up predicate with PredicateBuilder */;
var recs = SubsonicClass.All().Where(predicate.Compile()).ToList();