Linq to Entity "In" Query with Multiple relationships?

I have this db structure image of structure I am trying to load up a list of the Product_Attributes and include the Product_AttributeItems for a single product.

So Product_Attributes are mapped through the Product_AttributeMap, and then individual Product_AttributeItems are mapped also.

I cant get my head around it! I am guessing it should be some sort of IN statement, i.e. Select the items where they exist in the mapped table.

I have tried using various join statements, but was always ending up with more Product_Attrubutes in the list with each only consisting of one Product_AttributeItem

So instead of having 3 Product_Attributes (Size, Colour, Style); Size attribute containing the Items (S, M, L), Colour attribute containing (Black, White); Style attribute containing (US). I would end up with 3 Size attributes containing 1 of the sizes each, 2 colour attributes containing 1 of the colours each and 1 style attibute (if that makes sense).

开发者_StackOverflow社区

EDIT @Daniel Hilgarth

The code was similar to this

List<Product_Attribute> attributes = 
(from a in     _db.Product_Attribute.Include("Product_AttributeItem")
join item in _db.Product_AttributeItem on a.AttributeId equals item.AttributeId
join aim in _db.Product_AttributeItemMap on item.AttributeItemId equals
aim.AttributeItemId where item.AttributeId == a.AttributeId
select a).ToList();

Results in a list of 5 Product_Attributes rather than the 3 that exist (Size, Colour, Style)

EDIT @Botz3000

Going about it in the other direction

List<Product_AttributeItems> attributeItems =  
( from aim in   _db.Product_AttributeItemMap.Include("Product_AttributeItem.Product_Attribute") 
where aim.ProductId == prodId 
select aim).ToList();

I end up with the data I need, but just in the wrong format. As I need it as a List of the Product_Attributes (each a dropdownlist) containing their Product_AttributeItems (for the options)

---

To me, it sounds like you are querying in the wrong direction.

You could select the AttributeItems for the product and the Attributes for the product, then joining those two.

something like this (probably not exactly like this, just the general idea):

from item in Product.AttributeItemMaps.Select(m => m.AttributeItem)
join attMap in Product.AttributeMaps on item.AttributeId equals attMap.AttributeId
orderby attMap.Order
select new { Name=attMap.Attribute.AttributeName, Value=item.AttributeItemName }

if you need the available options for each attribute, you could try this:

from att in Product.AttributeMap.Select(m => m.Attribute).Include("AttributeItems")
select att