CODEWITHSUNDEEP
linq
stpdevi
stpdevi

Reputation: 1114

How to subtract the two columns sum of amount using linq

Below my query which not working properly could any one help here?

enter code here 
       var collectionamount = (from u in db.bank_deposit
                                    join pr in db.collections on u.AgentID equals pr.AgentID into g
                                    join agentname in db.agent_master on u.AgentID equals agentname.AgentID
                                    group u by new { u.AgentID } into x
                                    select new collectionwithdeposit
                                    {
                                        AgentName = agentname.AgentName,
                                        DepositedAmount = g.Sum(m => m.DepositedAmount),
                                        CollectionAmount = g.Sum(z => z.AmountReceived),
                                        Balance = g.Sum(u => u.DepositedAmount) - g.Sum(v => v.AmountReceived)
                                    }).ToList();

            lstdataModel.Add(dataModel);

        }

Upvotes: 0

Views: 2586

Answers (1)

Aron
Aron

Reputation: 15772

The variable g does not exists in the scope of your select. This is because of the into keyword. What you are writing is in effect..

var tempEnumerable = from u in db.bank_deposit
                     join pr in db.collections on u.AgentID equals pr.AgentID into g
                     join agentname in db.agent_master on u.AgentID equals agentname.AgentID
                     group u by new { u.AgentID };
var collectionamount = from x in tempEnumerable
                                    select new collectionwithdeposit
                                    {
                                        AgentName = agentname.AgentName,
                                        DepositedAmount = g.Sum(m => m.DepositedAmount),
                                        CollectionAmount = g.Sum(z => z.AmountReceived),
                                        Balance = g.Sum(u => u.DepositedAmount) - g.Sum(v => v.AmountReceived)
                                    }).ToList();

The into keyword in Linq is for creating a new scope.

In fact you really don't want to use into as often as you do

Try this

var collectionAmount = from u in db.bank_deposit
                     where db.collections.Any(pr => pr.AgentID == u.AgentID)
                     join agentname in db.agent_master on u.AgentID equals agentname.AgentID
                     group u by agentname into g
                     select new collectionwithdeposit
                     {
                         AgentName = g.Key.AgentName,
                         DepositedAmount = g.Sum(m => m.DepositedAmount),
                         CollectionAmount = g.Sum(z => z.AmountReceived),
                         Balance = g.Sum(u => u.DepositedAmount) - g.Sum(v => v.AmountReceived)
                     }

EDITED:

Maybe actually what you want is this...I don't know your table structures. But I am going to assume you have no idea about Linq, and your tables are sane (since my last answer assumed the opposite).

var collectionAmount = from agent in db.agent_master
                       let depositedAmount = db.bank_deposit
                               .Where(d => d.AgentID == agent.AgentID)
                               .Sum(d => c.DepositedAmount)
                       let collectionAmount = db.collections
                               .Where(c => c.AgentID == agent.AgentID)
                               .Sum(c => c.AmountReceived)
                       select new collectionwithdeposit
                       {
                         AgentName = agent.AgentName,
                         DepositedAmount = depositedAmount,
                         CollectionAmount = collectionAmount,
                         Balance = depositedAmount - collectionAmount
                       }

Upvotes: 1

Related Questions