LINQtoSQL Getting a table according to the values of another table in which it has many different relationships with
I have the following three tables:
alt text http://img16.imageshack.us/img16/5499/linqtosqlquery.jpg
With LinqToSql I would like to get a list of InventoryItems where (pointName = "Level" AND pointsValue <= maxStoreLevel) AND pointsName = "Buy Price" and
Note that this maxStoreLevel
is an integer and this is the value of the dot string that has pointsName = "Level".
Since you can't use somewhere inside another where in Linq, I don't know how to find the retrieved 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 Helmet" has pointsValue = 1 where pointsName = "Level", and also has a point called "Buy Price" so it should return (same thing happens with any other Inventoryitems with the same criteria)
source to share
OK so it's not as pretty as lambda expressions and I'm still a little vague about what exactly should be what should be because PointsName cannot be level and buy price at the same time, but I needed somewhere to start a conversation. I am assuming that you will need to make 2 joins in the point table, but since you know your setup better than me, I am assuming that you can do this and change it as needed. Let me know what I am 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 would not return strings as it didn't make sense for the dot name to have both, but based on your subsequent update, I think you need:
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();
source to share
Sort of:
var items = inventoryItems.Where(
invItem => invItem.InventoryItemPoints.Contains(
iip => (iip.pointsName == "Buy Price" || iip.PointsName == "Level") &&
iip.pointsValue == maxStoreLevel);
but this is a bit of an assumption based on what you posted, if not, dbml screening is helpful;)
Update: OK - this is clearly rubbish (sorry, a couple of glasses of wine is too much :)
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 to another pointNames should be easy enough ...
source to share
While you can't double clauses, you can use subsamples and use Count to see what 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;
source to share