How to use System.Linq.Expressions.Expression to filter based on children?

Question

I have a filter that I use across many methods:

Expression<Func<Child, bool>> filter = child => child.Status == 1;

(actually is more complex than that)

And I have to do the following

return db.Parents.Where(parent => parent.Status == 1 &&
                                  parent.Child.Status == 1);

where the condition is the same as in the filter above.

I want to reuse the filter in this method. But I don't know how. I tried

return db.Parents.Where(parent => parent.Status == 1 &&
                                  filter(parent.Child));

but an Expression can't be used as a method

Beriberi

Answer 1

If you want to combine expressions and still be able to use linq-to-sql, you may want to have a look at LinqKit. It walks inside your expression and replaces all the function calls by their contents before the sql conversion.

This way you'll be able to use directly

return db.Parents
       .AsExpandable()
       .Where(parent => parent.Status == 1 && filter(parent.Child));

Romeyn

Answer 2

You can try this:

var compiledFilter = filter.Compile();
foreach (var parent in db.Parents.Where(parent => parent.Status == 1))
    if (compiledFilter(parent.Child))
        yield return parent;

It requires you to pull all of the parents, but unlike @HugoRune's solution, it doesn't require a 1:1 relation of Parent:Child.

I don't think this will be useful for your situation because of the different types involved, but just in case, here is an example of how you can combine Expressions: How do I combine LINQ expressions into one?

Edit: I had previously suggested using Compile(), but that doesn't work over LINQ-to-SQL.

Cornela

I do not think you can compile expressions inside linq-to-sql statements. Lambdas only work in normal linq – Biathlon

Hm, seems you're right, unfortunately. I'll suggest another option. – Cornela

I think I'll use combined expressions or Dinamic Linq, as @Biathlon suggested – Beriberi

Answer 3

Well, if there is a 1:1 relationship between parent and child (unlikely, but the example seems to imply that) then you could do it like this:

  return db.Parents
  .Where(parent => parent.Status == 1)
  .Select(parent => parent.Child)
  .Where(filter)
  .Select(child=> child.Parent);

Otherwise it will be hard.

You could do it with dynamic linq but that is probably overkill.

You could generate your expression tree manually, but that is also quite complicated. I have not tried that myself.

As a last resort you could of course always call yourQuery.AsEnumerable(), this will cause linq-to-sql to translate your query into sql up to this point and perform the rest of the work on the client-side; then you can .compile() your expression. However you lose the performance benefits of linq-to-sql (and compile() itself is quite slow; whenever it is executed, it calls the JIT-compiler):

  return db.Parents
  .Where(parent => parent.Status == 1)
  .AsEnumerable()
  .Where(parent  => filter.Compile().Invoke(parent.Child))

Personally I'd just define the expression twice, once for child and once for parent.child:

   Expression<Func<Child, bool>> filterChild = child => child.Status == 1;
   Expression<Func<Parent, bool>> filterParent = parent => parent.Child.Status == 1;

Might not be the most elegant, but probably easier to maintain than the other solutions

Biathlon

Answer 4

Just come up with this, check if this would work for you

public interface IStatus { public int Status { get; set; } }
public class Child : IStatus { }
public class Parent : IStatus
{public Child Child { get; set; }  }

Func<IStatus, bool> filter = (x) => x.Status == 1;
var list = Parents.Where(parent => filter(parent) && filter(parent.Child));

Hope this helps!

Blum

Func does not work with linq-to-sql, it has to be Expression<Func>. And since parent and child are presumably auto-generated classes based on the db scheme, giving them a common base class is not that simple – Biathlon

Assuming the criteria are really so simply shared, this is actually a good approach: auto-generated classes are declared as partial, so that you can add things like interface declarations/implementations in another file. Since you'll need to use Expression instead of Func, the given code won't work as-is, but you can combine them using methods shown at #1922997. – Cornela

Answer 5

Could you just use the expression as a function instead?

Instead of:

Expression<Func<Child, bool>> filter = child => child.Status == 1;

Use that same expression as a generic function this way:

Func<Child, bool> filter = child => child.Status == 1;

Then you will be able to use the function in just the same way you were trying to use an expression:

return db.Parents.Where(parent => parent.Status == 1 &&
                                  filter(parent.Child));

Edit: I misunderstood the question. This is a bad answer. 6+ years out, I'm still getting comments to the effect that this doesn't work. I'm not sure, from a hygiene perspective, if it would be better to just delete the answer, or add this edit and let the answer stand as an example of something that decidedly doesn't work. I'm open to advisement on that.

Monk

Your solution doesn't translate to SQL. – Beriberi

Yes, I misunderstood the question. This is a bad answer. – Monk

Answer 6

There's no need for external libraries or mucking around with expression trees. Instead, write your lambda functions to use query chaining and take advantage of LINQ's deferred execution.

Instead of:

Expression<Func<Child, bool>> filter = child => child.Status == 1;

Rewrite it as:

Func<IQueryable<Parent>, IQueryable<Parent>> applyFilterOnParent = query => query.Where(parent => parent.Child.Status == 1);

Func<IQueryable<Child>, IQueryable<Child>> applyFilterOnChild = query => query.Where(child => child.Status == 1);

Now, instead of:

return db.Parents.Where(parent => parent.Status == 1 && filter(parent.Child));

You can write:

var query = db.Parents.AsQueryable(); query = applyFilterOnParent(query); return query.Where(parent => parent.Status == 1);

And you can re-use the applyFilter functions in other LINQ queries. This technique works well when you want to use lambda functions together with LINQ-to-SQL, because LINQ will not translate a lambda function to SQL.

Emmalineemmalyn