LINQ Error Invalid Column Name on Group by sum.

Question

For some reason, when using two sums on a group by, i run into the error "invalid column name 'id'. When i only do one sum, it works as expected.

The following fails and throws the error:

from pd in PrDetails.Where(_pd => _pd.PrId == 46)
 group pd by new { pd.ProgramFund, pd.ProjectDetail.CostCenter, pd.ProjectDetail.Wbs }
 into g
select new
 {
  g.Key.ProgramFund,
  g.Key.CostCenter,
  g.Key.Wbs,
  CommittedTotal = g.Sum(_pd => _pd.PrDetailPrAmounts.Sum(_pa => _pa.CommittedAmount)),
  OverheadTotal = g.Sum(_pd => _pd.PrDetailPrAmounts.Sum(_pa => _pa.OverheadAmount))
 }

However, the following does work fine:

from pd in PrDetails.Where(_pd => _pd.PrId == 46)
 group pd by new { pd.ProgramFund, pd.ProjectDetail.CostCenter, pd.ProjectDetail.Wbs }
 into g
select new
 {
  g.Key.ProgramFund,
  g.Key.CostCenter,
  g.Key.Wbs,
  CommittedTotal = g.Sum(_pd => _pd.PrDetailPrAmounts.Sum(_pa => _pa.CommittedAmount))
 }

And also, when I get the overhead total, instead of the committed total, it works great:

from pd in PrDetails.Where(_pd => _pd.PrId == 46)
     group pd by new { pd.ProgramFund, pd.ProjectDetail.CostCenter, pd.ProjectDetail.Wbs }
     into g
    select new
     {
      g.Key.ProgramFund,
      g.Key.CostCenter,
      g.Key.Wbs,
      OverheadTotal = g.Sum(_pd => _pd.PrDetailPrAmounts.Sum(_pa => _pa.OverheadAmount))
     }

Why can't i get the committed total and overhead total at the same time?

I have our basic table structure below:

PrDetails

Id (int)
PrId (int)
ProgramFund (linq class)
ProjectDetail (linq class)

ProjectDetails

Id (int)
CostCenter (linq class)
Wbs (linq class)

PrAmounts

Id (int)
PrDetailId (int)
CommittedAmount (decimal)
OverheadAmount (decimal)

Answer 1

About the only think I can think it might be is a conflict with the names of your parameters. Try this:

from pd in PrDetails.Where(_pd => _pd.PrId == 46)
group pd by new { pd.ProgramFund, pd.ProjectDetail.CostCenter, pd.ProjectDetail.Wbs }
into g
select new
{
    g.Key.ProgramFund,
    g.Key.CostCenter,
    g.Key.Wbs,
    CommittedTotal = g.Sum(_pd1 => _pd1.PrDetailPrAmounts.Sum(_pa1 => _pa1.CommittedAmount)),
    OverheadTotal = g.Sum(_pd2 => _pd2.PrDetailPrAmounts.Sum(_pa2 => _pa2.OverheadAmount))
}

Answer 2

You should be starting a SQL profiler and checking the exact SQL being produced to start with and that should head you in the right direction