Filtering data based on data through multiple relationships

So I have this in my DB:

CommodityCategories
-------------------
CommodityCategoryID (PK)
Name

Commodities
-------------------
CommodityID (PK)
CommodityCategoryID (FK)
Name

VendorsCommodities
-------------------
VendorID (PK, FK)
CommodityID (PK, FK)

Vendors
-------------------
VendorID
Name

Basically, a Commodity has a Commodity Category, and a Vendor has multiple Commodities.

I'd like to display the name of each Commodity Category, and underneath display the name of each Vendor that has a Commodity related to that Commodity Category. Here's what I have:

@foreach (var commodityCategory in Model.CommodityCategories)
{
    <h3><a href="#">@commodityCategory.Name</a></h3>
    <div>
        @foreach (var vendor in Model.Vendors)
        {开发者_JAVA技巧
            <span>@vendor.Name</span>
        }
    </div>
}

I need to filter Model.Vendors by the current Commodity Category that I'm looping through. I tried Model.Vendors.Where(v => v.Commodities.CommodityCategories.Contains(commodityCategory)), but CommodityCategories isn't a property of v.Commodities.

Is there any way for me to do this?

This could work:

Model.Vendors.Where(v => v.Commodities.Any(c => c.CommodityCategory.Id ==
                                                          commodityCategory.Id))

I recreated your db schema and this is what worked for me ...

@foreach (var catagory in Model)
{
   <h3><a href="#">@catagory.Name</a></h3>

   if (catagory.Commodities.Any(commodity => commodity.Vendors.Count > 0))
   {
      <div>
      @foreach (var vendor in catagory.Commodities.SelectMany(c => c.Vendors).Distinct())
      {
         <span>@vendor.Name</span><br />
      }
      </div>
   }
}