LINQtoSQL Retreiving a table according to the values of another table where it has a many to many relationship with

Answer 1

Something like:

var items = inventoryItems.Where(
  invItem => invItem.InventoryItemPoints.Contains(
    iip => (iip.pointsName == "Buy Price" || iip.PointsName == "Level") &&
    iip.pointsValue == maxStoreLevel);

but that's a bit of a guess based on what you've posted, if that's not it, a screenie of the dbml would be useful ;)

Update: OK - that's clearly rubbish (sorry, couple of glasses of wine too many :)

try this:

var items = dataContext.inventoryItems.Where(
    invItem => ivnItem.InventoryItemPoints.Select(
        iip => iip.Point.PointName).Contains(
        "Buy Price")
        && invItem.InventoryItemPoints.Select(
            iip => iip.pointsValue).Contains(maxStoreLevel));

Adding in the other pointNames should be easy enough...

Answer 2

OK, so this isn't as pretty as the lambda expressions and I'm still a little fuzzy on what exactly the where clause needs to be since PointsName can't be Level and Buy Price at the same time, but I needed to start the conversation somewhere. I'm guessing that you'll need to do 2 joins on the points table but since you know your setup better than I, I'm guessing you'll be able to take this and modify it as needed. Let me know what I'm missing...

var items = (From items in context.InventoryItems
             join itemPoints in context.InventoryItemPoints on items.InventoryItemID equals itemPoints.InventoryItemID
             join points in context.Points on itemPoints.pointsID equals points.pointsID
             where (points.pointsName == "Level" && itemPoints.pointsValue == maxStoreLevel) && points.pointsName == "Buy Price"
             select items).Distinct();

I knew the original wouldn't return rows as there was no way for the points name to have both values but based off your subsequent update, I think what you need is:

var items = (From items in context.InventoryItems
             join levelItemPoints in context.InventoryItemPoints on items.InventoryItemID equals levelItemPoints.InventoryItemID
             join levelPoints in context.Points on levelItemPoints.pointsID equals levelPoints.pointsID
             join priceItemPoints in context.InventoryItemPoints on items.InventoryItemID equals priceItemPoints.InventoryItemID
             join pricePoints in context.Points on priceItemPoints.pointsID equals pricePoints.pointsID
             where (levelPoints.pointsName == "Level" && levelItemPoints.pointsValue == maxStoreLevel) && pricePoints.pointsName == "Buy Price"
             select items).Distinct();

Answer 3

While you can't double where clauses, you can use sub-selects and use Count to see that they contain values. Try:

var items = from item in context.InventoryItems
    where (from iip in context.InventoryItemPoints
     join p in context.Points on p.pointsId equals iip.pointsId
     where iip.InventoryItemId == item.InventoryItemId 
                    && p.pointsName == "Level" && iip.pointsValue == maxStoreLevel
     select iip).Count() > 0
    && (from iip in context.InventoryItemPoints
     join p in context.Points on p.pointsId equals iip.pointsId
     where iip.InventoryItemId == item.InventoryItemId 
                    && p.pointsName == "Buy Price"
     select iip).Count() > 0
    select item;