CODEWITHSUNDEEP
c#linq
pdiddy
pdiddy

Reputation: 6297

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

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

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

Upvotes: 4

Views: 467

Answers (2)

pdiddy
pdiddy

Reputation: 6297

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();

 }

Upvotes: 0

JaredPar
JaredPar

Reputation: 754893

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;

Upvotes: 4

Related Questions