LINQtoSQL根据与其具有多对多关系的另一个表的值检索表

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

使用LinqToSql我想检索一个InventoryItems列表,其中(pointsName =“Level”和pointsValue <= maxStoreLevel)AND pointsName =“Buy Price”和

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

请注意,maxStoreLevel是一个Integer,它是pointsName =“Level”的points行的值。

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

既然你不能在Linq中的另一个地方使用,我不知道如何去检索提到的列表。

Update: Here is the UML diagram as requested

更新:这是请求的UML图

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)

假设maxStoreLevel为1,“Hanzo's Helmet”的pointsValue = 1,其中pointsName =“Level”,并且还有一个名为“Buy Price”的点,因此应该返回它的行(与具有相同标准的任何其他Inventoryitems相同)

3 个解决方案

#1

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

好吧,所以这不像lambda表达式那么漂亮,我仍然有点模糊where where子句需要是什么,因为PointsName不能同时是Level和Buy Price,但我需要开始某处的谈话。我猜你需要在积分表上做2个连接但是因为你比我更了解你的设置,我猜你能够接受它并根据需要修改它。让我知道我错过了什么......

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

#2

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

但根据你发布的内容有点猜测,如果不是这样的话,dbml的屏幕将是有用的;)

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

添加其他pointNames应该很容易......

#3

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

虽然您不能将where子句加倍,但您可以使用子选择并使用Count来查看它们包含值。尝试:

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;

#1

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