LINQ中两个表的列和

本文关键字：两个 LINQ | 更新日期: 2023-09-27 18:14:25

我在MS SQL中有两个相关的表

Example...
Table A – Room
RoomId
RoomCost
Table B – RoomItem
RoomItemId
ItemName
ItemCost
RoomId

我需要帮助编写一个LINQ代码，将添加RoomCost然后Sum ItemCost(如果他们有相同的RoomId)，并在WPF文本框中发布结果。到目前为止，我已经尝试了

DataclassesDataContext dc= new DataclassesDataContext();
var RoomAmount = (from dbo in dc.Room select dbo.RoomCost).Single();
var ItemAmount = (from itm in dc.RoomItem select itm.ItemCost).Single();
decimal RmAmount = Convert.ToDecimal(RoomAmount);
decimal ItmAmount = Convert.ToDecimal(ItemAmount);
decimal TAmount=RmAmount+ItmAmout;
txtTotalAmount.Text=TAmount

我是c#的初学者，请帮助。如果我说得不够清楚，尽管问。由于

LINQ中两个表的列和

如何使用Sum()这样(还没有测试过):

假设房间。RoomCost是的十进制和RoomItem。ItemCost也是十进制， myRoomId是您要汇总的房间的id:

var roomAmount = dc.Room.Single(r => r.RoomId == myRoomId).RoomCost;
var itemAmount = dc.RoomItem.Where(i => i.RoomId == myRoomId).Sum(r => r.ItemCost);
txtTotalAmount.Text= roomAmount + itemAmount ;

假设房间和房间项目的关联正确。

var foo = from room in dc.Room
          select new { room , TotalCost = room.RoomCost + room.RoomItems.Sum(item => item.ItemCost) };
var bar = foo.Single(room => room.RoomId == myRoomId);

您可以通过linq内部连接和分组来做到这一点LINQ: Using INNER JOIN, Group and SUM，并在这里输入链接描述要添加两列，请参见示例http://forums.asp.net/t/1728644.aspx/1

所以从这个链接你的查询像

var total = from room in dc.Room
        join item in dc.RoomItem on room.RoomID equals item.RoomID
        group item by room.RoomID into g
        select new { 
            RoomID = room.RoomID, 
            RoomCost = (room.RoomCost + g.Sum(t => t.ItemCost))
        };
相关文章：