Linq to Sql Projection Help

Question

I've reached the end of my Linq rope. Need your help!

Heres my table structure first(all linq to sql objects):

InventoryItems
  -ID
  -AmtInStock

IventoryKits
  -ID

InventoryKits_to_InventoryItems
  -InventoryItemID
  -InventoryKitID

So i need to do a projection like the following

 var q2=from k in GetAllKits()//returns IQueryable<InventoryKit>
                    select new VMPublication()//ViewModel Object
                   {
                       ID = k.ID,
                       Name = k.Name,
                       WebAmountInStock = ,//need to get the Min() AmtInStock from InventoryItems here
                       ItemCode = k.ItemCode,
                       WebAmountOrdered = k.AmtOrdered.ToString(),
                       WebReminderAmount = "",
                       WebAmountWarning="",
                       Type = "Kit"
                   };

i have no idea how to get that Min() of InventoryItem's AmtInStock in that query.

Please help! Very Appreciated!

Answer 1

I'm guessing at your association names, but try something like:

var q2=from k in GetAllKits()//returns IQueryable<InventoryKit>
                select new VMPublication()//ViewModel Object
               {
                   ID = k.ID,
                   Name = k.Name,
                   WebAmountInStock = (from i in k.InventoryKits_to_InventoryItems
                                       select i.InventoryItem.AmtInStock).Min(),
                   ItemCode = k.ItemCode,
                   WebAmountOrdered = k.AmtOrdered.ToString(),
                   WebReminderAmount = "",
                   WebAmountWarning="",
                   Type = "Kit"
               };