LINQ select problem

Question

from item in db.Items
join ci in db.ChainStoreItems on item.ItemId equals ci.ItemId
where ci.ChainStoreId == 2
select item

The problem is as follows:

Item has a Set of ChainStoreItems. I want to make a qurey which returns a Item which dont have a Set of ChainStoreItems it should hold only the one specific ChainStoreItem for the selected ChainStore.

so i only want do have the additonal columns in item which came from ChainStoreItem how ever this is possible

thats a sql statement which would do what i want

SELECT        ChainStoreItems.ChainStoreId, ChainStoreItems.ItemId, Item.ProcStatus, Item.Del, Item.LastUpdate, ChainStoreItems.AllowToReturn, 
                         ChainStoreItems.AllowToSale
FROM            ChainStoreItems INNER JOIN
                         Item ON ChainStoreItems.ItemId = Item.ItemId
WHERE        (ChainStoreItems.ChainStoreId = 140)

Answer 1

Does ChainStoreItem have an Item property?

In which case, is it just:

from csi in ChainStoreItems
where csi.ChainStoreId == 140
select new { ChainStoreItem = csi, Item = csi.Item }

... or have I misunderstood?

Answer 2

Is this what you are getting at!?

var result = from item in db.Items
                     join ci in db.ChainStoreItems 
                     on item.ItemId equals ci.ItemId 
                     where ci.ChainStoreId == 2                      
                     into itemci // note grouping        
                     select new
                     {
                             //Whatever you want in here
                     };
        return result;

Sorry i'm still not quite seeing what exactly it is you want to achieve. As far as i'm aware it's simply a matter of creating a join and pulling back the appropriate details