Hi All,

Hope you guys are doing well. Here I have an issue with my LINQ query basically I need to get the sum of a column and some other columns values and for that the SQL query would be like this.

Can you guys please help me with the LINQ query for this.

Thanks

So you would need something like this: 

using (DataContext dc = new DataContext())
{
    var query = from p in dc.Project
    join pf in dc.ProjectFund on p.ProjectID equals pf.ProjectID
    join f in dc.Fund on p.ProjectID equals f.ProjectID 
                select new
                {
                    p.Name,
                    p.Description,
                    Sum = dc.Project.Sum(i => i.TotalAmount)
                };
}

Give that a try. I can't test it because I don't have your tables.

 Hey

Thanks for your reply. 

It helped me a lot better.. but the thing s its returning the sum value for all the records... its not summing up for all individual records (Projects). Basically, I need to get the Fund Amount for each project.. Our query you explained above is returning the sum of the fund amount for all projects.. so  its returning the same value for all records (Projects).

If possible can you please help me out of this.

BTW I have also modified your query like this:

var query = from p in db.Projects
                        join pf in db.ProjectFunds on p.ProjectID equals pf.ProjectID
                        join f in db.Funds on pf.FundID equals f.FundID
                        where pf.ProjectID==p.ProjectID
                        orderby p.Name
                        select new
                        {
                            p.Name,
                            p.Description,
                            p.ProjectID,
                            p.DistrictID,
                            Sum = db.ProjectFunds.Sum(i => i.Fund.TotalAmount)
                        };

In that case you need to use a group by.  Something like this:

var query = from p in db.Projects
                        join pf in db.ProjectFunds on p.ProjectID equals pf.ProjectID
                        join f in db.Funds on pf.FundID equals f.FundID
                        where pf.ProjectID==p.ProjectID
                        group p by new {dc.Projects.ProjectID} in g
                        orderby p.Name
                        select new
                        {
                            g.Key.ProjectID,
                            Count = g.Count,
                            Sum = db.ProjectFunds.Sum(i => i.Fund.TotalAmount)
                        }; 

 Ok...Let me try with that ... I think I have basic Idea what you are saying.. I have one more question .. How can we get the DISTINCT records from the above query ..

For a distinct try this:

var query = (from p in db.Projects
                        join pf in db.ProjectFunds on p.ProjectID equals pf.ProjectID
                        join f in db.Funds on pf.FundID equals f.FundID
                        where pf.ProjectID==p.ProjectID
                        group p by new {dc.Projects.ProjectID} in g
                        orderby p.Name
                        select new
                        {
                            g.Key.ProjectID,
                            Count = g.Count,
                            Sum = db.ProjectFunds.Sum(i => i.Fund.TotalAmount)
                        }).Distinct(); 

 Hey,

Yeah.. that distinct works but now its not ordered with the "Name" .. Although I have included the statement OrderBy p.Name

Okay try this:

var query = (from p in db.Projects
                        join pf in db.ProjectFunds on p.ProjectID equals pf.ProjectID
                        join f in db.Funds on pf.FundID equals f.FundID
                        where pf.ProjectID==p.ProjectID
                        group p by new {dc.Projects.ProjectID, p.Name} in g                        
                        select new
                        {
                            g.Key.ProjectID,
                            g.Key.Name,
                            Count = g.Count,
                            Sum = db.ProjectFunds.Sum(i => i.Fund.TotalAmount)
                        }).Distinct().OrderBy(o => o.Name); 

 Hey Finally I got the Sum for each record:

var query = (from p in db.Projects
                        join pf in db.ProjectFunds on p.ProjectID equals pf.ProjectID
                        join f in db.Funds on pf.FundID equals f.FundID
                        //where pf.ProjectID==p.ProjectID                    
                        orderby p.Name descending                       
                        select new
                        {
                            p.Name,
                            p.Description,
                            p.ProjectID,
                            p.DistrictID,
                            Sum = db.ProjectFunds.Where(i => i.ProjectID==p.ProjectID).Sum(i => i.Fund.TotalAmount)
                        }).Distinct();

Basically I have added another where in the sub query ,...

 Anyways thanks man you have been great help for me.. I have marked your reponses as answers twice ..