Ling SQL组与连接

Question

我有一个linq sql查询:

var orderitems =
    from orderItem in Order_ProductItems
    //join style in Products_Styles on orderItem.Style equals style.Index
    where orderItem.SalesOrderID == 123
    group orderItem by orderItem.FrameNo into grp
    select new
    {
        FrameNo = grp.Key,
        Count = grp.Select(x => x.FrameNo).Count(),
        TotalCost = grp.Sum(x=>x.CostPrice),
        OverAllWidth = grp.Single(x=>x.HardwareType==3).OverallHeight,
        //Name = style.Name, 
        //ImagePath = style.External_Image_Path
    };

我正试图从Product_Styles获得数据，但它不工作…

上面的工作，但不能取消上面的注释....

Answer 1

在您的组中包括style，然后在您的select中引用orderItem和style:

var orderitems =
    (from orderItem in Order_ProductItems
     join style in Products_Styles on orderItem.Style equals style.Index
     where orderItem.SalesOrderID == 123
     group new { orderItem, style } by orderItem.FrameNo into grp
     select new
     {
         FrameNo = grp.Key,
         Count = grp.Select(x => x.orderItem.FrameNo).Count(),
         TotalCost = grp.Sum(x => x.orderItem.CostPrice),
         OverAllWidth = grp.Single(x => x.orderItem.HardwareType == 3).OverallHeight,
         Name = grp.Select(x => x.style.Name).First(),
         ImagePath = grp.Select(x => x.style.External_Image_Path).First()
      };

您可能必须调整它以满足您的需要，并且它可能不会完全按原样编译，因为我没有设置一个环境来测试它，但它应该给您一个大致的概念。

Answer 2

根据@GrantWinney的帖子，你需要像这样选择Name和ImagePath:

Name = grp.FirstOrDefault().style.Name,
ImagePath = grp.FirstOrDefault().style.External_Image_Path