CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Aqua267
Aqua267

Reputation: 953

LINQ to SQL query Multiply two columns and calculate SUM

Here's the SQL query

Select sum(f.Acres*m.credit1), sum(f.Acres*m.credit2), sum(f.Acres*m.credit3)
from first f join model m on f.Id = m.fID
where m.year == 2012

How to write this above SQL in LINQ?

Thanks!

Upvotes: 2

Views: 10539

Answers (1)

Magnus
Magnus

Reputation: 46997

This is a little tricky to do in Linq since you need a group to be able to do aggregates. By grouping on a dummy value like below it'll work:

var q = from f in first
        join m in model on f.Id equals m.fID
        where m.year == 2012
        group new { f, m } by 1 into g
        select new 
        { 
           credit1 = g.Sum(x => x.f.Acres * x.m.credit1),
           credit2 = g.Sum(x => x.f.Acres * x.m.credit2),
           credit3 = g.Sum(x => x.f.Acres * x.m.credit3)
        };

EDIT
From reading your comments. If you want it group per year, this is how you do it:

var q = from f in first
        join m in model on f.Id equals m.fID
        where m.year >= 2014 && m.year <= 2020
        group new { f, m } by m.year into g
        select new 
        { 
           year    = g.Key,
           credit1 = g.Sum(x => x.f.Acres * x.m.credit1),
           credit2 = g.Sum(x => x.f.Acres * x.m.credit2),
           credit3 = g.Sum(x => x.f.Acres * x.m.credit3)
        };

Upvotes: 2

Related Questions