how to convert a sql query into linq to entity query?

Question

  select sr.Name as Sale_Rep,ag.Name as Agent ,
         sum(tr.Amount) Debit_tran 
  from DebtorTransactions as tr 
       join Warranty as wr on tr.ProductID=wr.Id 
       join Agents as ag on ag.pkfAgentID=wr.fldAgentID 
       join SalesReps as sr on wr.fldSrId =sr.pkfSrID 
  where tr.Product=0  
  group by ag.Name, sr.Name

i want to convert this query into Linq to Entity but can't ... can some convert this query into Linq to entity for me plz

This is what I've tried:

var abc= from tr in db.DebtorTransactions 
         from wr in db.Warranties 
         from sr in db.SalesReps 
         from ag in db.Agents 
         where tr.ProductID==wr.Id 
            && ag.pkfAgentID==wr.fldAgentID 
            && wr.fldSrId ==sr.pkfSrID 
            && tr.Product==0 
         select new { ag.Name, sr.Name, tr.Amount };

But i don't get any output

var abc= from tr in db.DebtorTransactions from wr in db.Warranties from sr in db.SalesReps from ag in db.Agents where tr.ProductID==wr.Id && ag.pkfAgentID==wr.fldAgentID && wr.fldSrId ==sr.pkfSrID && tr.Product==0 select new { ag.Name, sr.Name, tr.Amount }; — R K Sharma
– R K Sharma, Commented Oct 10, 2013 at 8:25
i have tried this ...but i don't get any output...plz help me — R K Sharma
– R K Sharma, Commented Oct 10, 2013 at 8:26

Steve Ruble · Accepted Answer · 2013-10-11 10:29:46Z

1

To match your SQL query you'll need to group by the same columns:

from tr in db.DebtorTransactions 
join wr in db.Warranties on tr.ProductID equals wr.Id
join ag in db.Agents on wr.fldAgentID equals ag.pkfAgentID
join sr in db.SalesReps on wr.fldSrId equals sr.pkfSrID 
group tr 
    by new { Sale_Rep = sr.Name, Agent = ag.Name }
    into transactions
select new {
    transactions.Key.Sale_Rep,
    transactions.Key.Agent,
    Debit_tran = transactions.Sum(tr => tr.Amount)
}

answered Oct 11, 2013 at 10:29

Steve Ruble

3,90523 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexandre Rondeau · Accepted Answer · 2013-10-10 18:31:10Z

0

var abc = from tr in db.DebtorTransactions 
          join wr in db.Warranties tr.ProductID equals wr.Id
          join sr in db.SalesReps wr.fldSrId equals sr.pkfSrID 
          join ag in db.Agents wr.fldAgentID equals ag.pkfAgentID
          where tr.Product==0 
          select new { ag.Name, sr.Name, tr.Amount };

answered Oct 10, 2013 at 18:31

Alexandre Rondeau

2,68725 silver badges31 bronze badges

Collectives™ on Stack Overflow

how to convert a sql query into linq to entity query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related