Sum of Columns of Two Tables in LINQ

Question

I have two related tables in MS SQL.

Example...
Table A – Room
RoomId
RoomCost

Table B – RoomItem
RoomItemId
ItemName
ItemCost
RoomId

I need help in writing a LINQ code that will add RoomCost then Sum ItemCost (if they have the same RoomId) and post the results in a WPF textbox. So far I have tried

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

I am a beginner in C#, Kindly help. If I haven't been clear enough, just ask. Thanks

Please edit your post rather than adding comments, and format it appropriately. If you click on "edit" there's a question mark in the top right corner which will help you to present your question in a readable way. — Jon Skeet, May 21 '13 at 02:24

Chief Wiggum · Accepted Answer · 2013-05-21T02:37:57.843

0

How about using Sum() like (havent tested it):

Assuming that Room.RoomCost is decimal and RoomItem.ItemCost is decimal as well and myRoomId is the id of the room you want to sum up:

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 ;

edited May 21 '13 at 02:37

answered May 21 '13 at 02:31

Chief Wiggum

2,784
2
31
44

score 0 · Answer 2 · answered May 21 '13 at 02:58

0

Assuming correct associations on the room and room items.

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);

answered May 21 '13 at 02:58

Aron

15,464
3
31
64

score 0 · Answer 3 · edited May 23 '17 at 11:57

You are able to do this by linq inner join and group by see example here : LINQ: Using INNER JOIN, Group and SUM and enter link description here and to add two column see example http://forums.asp.net/t/1728644.aspx/1

so from this links your query like

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))
        };

Sum of Columns of Two Tables in LINQ

3 Answers3