Need help Linq query join + count + group by

Question

I have two table

First table

BID Town
1   ABC
2   ABC2
3   ABC

Second Table

PID BID AmountFirst AmountSecond AmountThird Minority
1   1   1000       1000          1000        SC
2   2   2000       1000          2000        ST
3   3   1000       1000          1000        SC

BID is foreign key in Second table. I want sum AmountFirst + AmountSecond + AmountThird for individualTown e.g for ABC town answer should be : 6000 (summation of PID 1 and PID 2) I want Linq query for this..Please help

Answer 1

Try something like:

(from bid in db.Bids
Where bid.Town == "ABC"
select bid.SecondTable.AmoundFirst + bid.SecondTable.AmountThird + bid.SecondTable.AmountSecond).Sum();

Answer 2

Untested, but something like this should work. See hooked on linq - GroupBy operator for groupby syntax.

from bid in db.Bids
group by bid.Town into g
select new
{
  Town = g.Key,
  Total = g.Sum(x => x.AmountFirst + x.AmountSecond + x.AmountThird)
}

Town is now a number, you can also do:

Town = g.Key.Town