Predicate Building with subquery using LinqKit

Question

I am trying to work out how to build a predicate with a subquery that references the parent query.

Example:

I have 2 classes (Foo and Bar, of course).

The ReferenceId of Bar refers to (in this particular instance) Foo's Id.

public class Foo
{
    public int Id { get; set; }

    public bool IsGood { get; set; }
}

public class Bar
{
    public int Id { get; set; }

    public int ReferenceId { get; set; }

    public bool IsSomething { get; set; }
}

I have a DB context.

public class MyContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }
    public DbSet<Bars> Bars { get; set; }
}

I want to use a predicate builder (if possible) to create a function like this... which checks for the existence of a Bar with the Foo's Id.

I do not have the Bar as a navigation property so I need to access the context directly.

public static IQueryable<Foo> GetFooQuery(IQueryable<Foo> query, MyContext context)
{
    query = query.Where(f => f.IsGood)
                 .Where(f => context.Bars.Where(b => b.ReferenceId == f.Id)
                                         .Where(b => b.IsSomething)
                                         .Any());

    return query;
}

I found this, which (I think) is similar to what I want, but not the same.

Entity Framework Code First 4.3 / LINQKit predicate for related table

I wrote this

public static IQueryable<Foo> GetFooQuery(IQueryable<Foo> query, MyContext context)
{
    var barPredicateBuilder = PredicateBuilder.True<Bar>();
    barPredicateBuilder = barPredicateBuilder.And(b => b.IsSomething);

    var fooPredicateBuilder = PredicateBuilder.True<Foo>();
    fooPredicateBuilder = fooPredicateBuilder.And(f => f.IsGood);
    fooPredicateBuilder = fooPredicateBuilder.And(f => context.Bars
                                                              .Where(b => b.ReferenceId == f.Id) // A
                                                              .Where(barPredicateBuilder).AsExpandable() // B
                                                              .Any());

    query = query.Where(fooPredicateBuilder).AsExpandable();

    return query;
}

but I am getting the error: The parameter 'f' was not bound in the specified LINQ to Entities query expression.

If I remove either line A or B, it executes with error. With them both, it throws the exception.

Can anyone please offer some input on what I should be doing to achieve this?

Thanks

score 0 · Accepted Answer · answered Apr 28 '17 at 15:02

Seems I was almost there. I just needed to flip lines A and B.

public static IQueryable<Foo> GetFooQuery(IQueryable<Foo> query, MyContext context)
{
    var barPredicateBuilder = PredicateBuilder.True<Bar>();
    barPredicateBuilder = barPredicateBuilder.And(b => b.IsSomething);

    var fooPredicateBuilder = PredicateBuilder.True<Foo>();
    fooPredicateBuilder = fooPredicateBuilder.And(f => f.IsGood);
    fooPredicateBuilder = fooPredicateBuilder.And(f => context.Bars
                                                              .Where(barPredicateBuilder).AsExpandable() // B
                                                              .Where(b => b.ReferenceId == f.Id) // A
                                                              .Any());

    query = query.Where(fooPredicateBuilder).AsExpandable();

    return query;
}

Predicate Building with subquery using LinqKit

1 Answers1