LINQ to Entities - Where Muliple And clause in query

I want to retrieve all records of manufacturer in LINQ using a many-to-many join.

Lets consider

Table 'Item':

id | name
----------
1  | A
---------
2  | B
--------
3  | C
--------

Table 'Manufacturer':

ManufactuerId | Name
-------------------
1             | XYZ
-------------------
2             | ABC
--------------------
3             | ZZZ
----------------------

Table 'ManufacturerItem':

ManufacturerItemID | ManufacturerId | ItemId 
-------------------------------------------
1                  | 1              | 1
-------------------------------------------
2                  | 1              | 2
-------------------------------------------
3                  | 2              | 1
------------------------------------------
4                  | 3              | 2
------------------------------------------
5                  | 1              | 3
------------------------------------------

I would like to fetch the records of a manufacturer(s) who has all the items that I have supplied in. So, given an input of {A, B} I would like to get an output of XYZ Manufacturer.

Answers


var tableItems = new[] { 
        new { Id = 1, Name = "A" }, 
        new { Id = 2, Name = "B" }, 
        new { Id = 3, Name = "C" } 
};

var tableManufacturer = new[] { 
    new { ManufacturerId = 1, Name = "XYZ" }, 
    new { ManufacturerId = 2, Name = "ABC" }, 
    new { ManufacturerId = 3, Name = "ZZZ" } 
};

var tableManufacturerItem = new[] 
{
    new {ManufacturerItemID = 1, ManufacturerId = 1, ItemId = 1},
    new {ManufacturerItemID = 2, ManufacturerId = 1, ItemId = 2},
    new {ManufacturerItemID = 3, ManufacturerId = 2, ItemId = 1},
    new {ManufacturerItemID = 4, ManufacturerId = 3, ItemId = 2},
    new {ManufacturerItemID = 5, ManufacturerId = 1, ItemId = 3},
};

var itemsToSearch = new[] { "A", "B" };

var result = tableManufacturerItem
                .GroupBy(x => x.ManufacturerId)
                .Where(m => tableItems.Where(item => itemsToSearch.Contains(item.Name)).Select(x => x.Id)
                                      .Except(m.Select(x => x.ItemId))
                                      .Count() == 0)
                .Select(x => tableManufacturer.First(m => m.ManufacturerId == x.Key))
                .Select(m => m.Name)
                .ToList();

Need Your Help