CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Bahdeng
Bahdeng

Reputation: 434

Linq to SQL: Sum with multiple columns select

I want to convert the following SQL code into linq to sql but can't seem to find a way

select holder_name,agent_code,sum(total) 
from agent_commission
group by agent_code

Can anyone help me? Am kinda stuck with this for quite a while.

Thanks in advance

UPDATE: I tried the following 

var query = (from p in context.Agent_Commissions
               group p by new
               {
                     p.agent_code
               }
               into s
               select new
               {
                    amount = s.Sum(q => q.total),
                }
              );

How do I select the other two columns? What am I missing?

Upvotes: 1

Views: 8191

Answers (2)

King King
King King

Reputation: 63317

In fact your SQL query works only when the corresponding relationship between holder_name and agent_code is 1-1, otherwise the Group by agent_code won't work. So your linq query should be like this:

var query =  from p in context.Agent_Commissions
             group p by p.agent_code into s
             select new {
                holder_name = s.FirstOrDefault().holder_name,
                agent_code = s.Key,
                amount = s.Sum(q => q.total)
             };

Upvotes: 3

vittore
vittore

Reputation: 17579

Here is your linq query

from a in ctx.agent_code 
group a by a.holder_name, a.code into totals 
select { holder_name = a.holder_name, 
         code = a.code, 
         total = totals.Sum(t=>t.total)}

Given that you have linq2sql context in ctx variable and it has your table in it.

Upvotes: 0

Related Questions