How to filter products in linq to entities and Entity Framework

Question

I have a table that maps Products to Specs and SpecOptions and SpecUnits:

MapProductSpecOptionSpecUnitSpec table:

public class MapProductSpecOptionSpecUnitSpec 
{
    [Key, Column(Order = 0)]
    public int ProductId {get; set;}

    [Key, Column(Order = 1)]
    public int SpecId {get; set;}

    [Key, Column(Order = 2)]
    public int SpecOptionId {get; set;}

    public int? SpecUniId {get; set;}
}

Spec:

public class Spec 
{
    [Key, Column(Order = 0)]
    public int Id {get; set;}

    public virtual ICollection<SpecOption> Options {get; set;}

    public virtual ICollection<MapProductSpecOptionSpecUnitSpec> Maps {get; set;}
}

SpecOption:

public class SpecOption 
{
    [Key, Column(Order = 0)]
    public int Id {get; set;}

    public virtual Spec Spec {get; set;}
    public int SpecId {get; set;}

    public virtual ICollection<MapProductSpecOptionSpecUnitSpec> Maps {get; set;}
}

I get SpecId, SpecOptionId and SpecUnitId from user, it should be noted that SpecUnitId might be NULL.

I need to get those Products that have all my criteria at the same time, not just one of them.

In other words, it must be an AND between Specs and OR between SpecOptions of a Spec.

Example:

SpecId    SpecOptionId    SpecUnit
----------------------------------
15        24              NULL
6         71              6
7         72              2

How I'm doing it now:

var maps = _db.MapProductSpecOptionSpecUnitSpec.AsQueryable();
var specPredicate = PredicateBuilder.New<MapProductSpecOptionSpecUnitSpec>();

foreach (var gp in model.GroupBy(x => x.SpecId)) 
{
    var optionPredicate = PredicateBuilder.New<MapProductSpecOptionSpecUnitSpec>();

    foreach (var map in gp) 
    {
        optionPredicate = optionPredicate.Or(y => y.SpecOptionId == y.OptionId && map.UnitId == map.UnitId);
    }

    specPredicate = specPredicate.And(optionPredicate);
}

var query = maps.AsExpandable().Where(specPredicate).Select(x => x.Product);

Although I have products with those criteria, I get nothing when I execute my query.

How can I achieve this?

Note: I'm using Albahari's PredicateBuilder

Answer 1

Finally I figured it out (using Albahari's PredicateBuilder):

var predicate = PredicateBuilder.New<Product>();

foreach (var filterGp in filters.GroupBy(x => x.SpecId)) {
    var innerPredicate = PredicateBuilder.New<Product>();

    foreach (var filter in filterGp) {
        innerPredicate = innerPredicate.Or(x => x.MapProductSpecOptionSpecUnitSpec.Any(y => y.SpecOptionId == filter.OptionId && y.SpecUnitId == filter.UnitId));
    }

    predicate = predicate.And(innerPredicate);
}

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