CODEWITHSUNDEEP
c#linqlinq-to-sql
Drahcir
Drahcir

Reputation: 12621

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

I have the following three tables:

alt text http://img16.imageshack.us/img16/5499/linqtosqlquery.jpg

With LinqToSql I would like to retrieve a list of InventoryItems where (pointsName="Level" AND pointsValue <= maxStoreLevel) AND pointsName="Buy Price" and

Note that maxStoreLevel is an Integer and it is the value of the points row that has pointsName = "Level".

Since you can't use a where inside another where in Linq, I do not know how to go about retrieving the mentioned list.

Update: Here is the UML diagram as requested

alt text http://img17.imageshack.us/img17/3403/umldiagram.jpg

To further explain my scenario here's the data in my tables:

alt text http://img4.imageshack.us/img4/7847/linqexample.jpg

Assuming maxStoreLevel is 1, "Hanzo's Helmet" has pointsValue = 1 where pointsName="Level" and also has a point called "Buy Price" therefore it's row should be returned (same goes with any other Inventoryitems with the same criteria)

Upvotes: 1

Views: 144

Answers (3)

AdamB
AdamB

Reputation: 9100

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

Upvotes: 1

Jacob Proffitt
Jacob Proffitt

Reputation: 12768

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;

Upvotes: 0

Whisk
Whisk

Reputation: 3417

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...

Upvotes: 0

Related Questions