lambda expression to query a collection by a collection

Question

I want to create a lambda expression to query a collection by a collection

In a EF code first environment, I have the following data objects

I have a class named price

public class Price
{
    public int Id { get; set; }
    public string DisplayName { get; set; }
    public double Amount { get; set; }
    public bool IsActive { get; set; }

    public int ItemId { get; set; }
    [ForeignKey("ItemId")]
    public virtual  Item Item  { get; set; }

    public ICollection<PriceOption> PriceOptions { get; set; }
}

And a related class named

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


    public int PriceId { get; set; }
    [ForeignKey("PriceId")]
    public virtual Price Price { get; set; }

    public int OptionId { get; set; }
    [ForeignKey("OptionId")]
    public virtual Option Option { get; set; }

}

I have two search criteria

int ItemId

List<int> optionIds

I want to create a lambda expression to select all prices that are equil to the ItemId (easy) and where the PriceOptions collection contains all of the optionIds.

The Idea is something like this but of course this it just to show what I am trying to achive.

List<Price> prices = _priceRepository.FindAll().Where(x => x.ItemId == item.Id && x.PriceOptions.All(y => y.OptionId == optionIds)).ToList();

Thank you for your help

Earl

Answer 1

The following LINQ query based on Contains and Count methods produces a simpler (thus eventually faster) SQL query:

var matchCount = optionIds.Count;
var prices = _priceRepository.FindAll()
              .Where(p => p.ItemId == ItemId &&
                  p.PriceOptions.Count(po => optionIds.Contains(po.OptionId)) == matchCount)
              .ToList();