How to use LINQ-to-Entity to query by contained objects

Question

Let's say I have a list of Boxes and in a box you can have multiple items.

• Box (id)
• Items (id, boxId)

I'm trying to build a linq to entity query that can return all the boxes that contains ALL specified items.

List<Box> FindBoxContainingAllSpecifiedItems(List<int> itemIds)
{
    var q = from box in ctx.Boxes
            where ???
}

Thanks for the help

Answer 1

It depends on the implementation of boxes. But lets for the moment say it has a property Items with the type IEnumerable<int>. In that case you could use the Intersect extension method to see if the items are all accounted for

var q = from box in ctx.Boxes
        where box.Items.Intersect(itemIds).Count() == itemIds.Count;

Answer 2

Here is what I have found thanks to JaredPar contribution.

List<Location> FindLocationContainingAllItems(List<int> itemIds)
{
    var itemQuery = from item in ctx.Items
                    select item;

    // Workaround the Where In Clause (http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0)
    itemQuery = itemQuery.Where(BuildContainExpression<Items, int>(i=> i.Id, itemIds));

    int itemCount = itemIds.Count();

    var locQuery = from loc in ctx.Locations
                   from box in loc.Boxes
                   where (from items in box.Items select items).Intersect(itemQuery).Count == itemCount
                   select loc;

    return locQuery.ToList();

 }