LINQ query for Sum and view using View Model Class

Question

My class along with sample data,

public class BudgetModels
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int BudgetId { get; set; }
        public int BudgetType { get; set; }
        public string BudgetName { get; set; }
        public decimal BudgetAmount { get; set; }
    }
BudgetId    BudgetType      BudgetName          BudgetAmount
1              101              B1              5,00,000
2              201              B2              10,00,000   
    
public class SchoolModels 
    {
        [Key]        
        public int SchoolId { get; set; }       
        public string SchoolName { get; set; }
    }
SchoolId        SchoolName
1                 NJ.EBS
2                 LA.EBS

    
public class MappedModels
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int MappedId { get; set; }
        public int MappedType { get; set; } // quarterly basis first quarter, second quarter, third quarter
        public decimal MappedAmount { get; set; }
        [ForeignKey("BudgetId")]
        public BudgetModels Budget { get; set; } 
        public int BudgetId { get; set; }
        
        [ForeignKey("SchoolId")]
        public SchoolModels School { get; set; }
        public int SchoolId { get; set; }
    }

Data for MappedModels,

MappedId    MappedType      MappedAmount    BudgetId    SchoolId
1               1               1,00,000        1           1
2               1               3,00,000        1           2
3               2               6,00,000        2           2
4               2               2,00,000        2           1
5               2               1,00,000        1           1

I have to make a report such that how much amount from each budget is given to schools in quarterly basis.

Report Format::

BudgetName  BudgetType  BudgetAmount    MappedType      TotalMappedAmount       RemainingAmount
B1              101         5,00,000        1                4,00,000               1,00,000
B1              101         5,00,000        2                1,00,000                   0
B2              201         10,00,000       1                   0                   10,00,000               
B2              201         10,00,000       2                8,00,000               2,00,000

I have created the view model class for this report::

public class BudgetQuarterlyReportViewModel
    {
        public int BudgetId { get; set; }
        public string BudgetName { get; set; }       
        public int BudgetType { get; set; }
        public decimal BudgetAmount { get; set; }
        public int MappedType { get; set; }
        public decimal TotalMappedAmount { get; set; }
        public decimal RemainingAmount { get; set; }
    }

What have I tried::

// BudgetModels Controller
public ActionResult Index()
        {

            var result = (from n in db.Mappeds
                          join k in db.Budgets on n.BudgetId equals k.BudgetId
                          //group n by new { n.MappedType} into g
                          select new BudgetQuarterlyReportViewModel()
                          {
                              BudgetId = k.BudgetId
                              , BudgetType = k.BudgetType
                              , BudgetName = k.BudgetName
                              , BudgetAmount = k.BudgetAmount
                              , MappedType = n.MappedType
                              , TotalMappedAmount =
                              ,
                          }

                         ).ToList();
            return View(result);
        }

I am facing problem with this query, like I am unable to do sum of the MappedAmount and when I uncomment group statement I am getting error in select block. I am not sure also whether my LINQ statement is correct or not.

Answer 1

Well, let's first understand what's happening when you are grouping by MappedType.
As a result of your grouping, you are getting a Key as MappedType and, as a result, a collection of Mapped items. That is the reason why your model BudgetQuarterlyReportViewModel will not fit here.

But instead, if you can define a model like this:

class BudgetQuarterlyReportGroupModel 
{
    public int MappedType { get; set; }
    public List<BudgetQuarterlyReportViewModel> Reports {get; set; }
    public decimal TotalMappedAmount { get; set; }
    public decimal RemainingAmount { get; set; }
}

Then you will be able to do this:

var result = (from n in db.Mappeds
    join k in db.Budgets on n.BudgetId equals k.BudgetId
    group n by n.MappedType into g
    select new BudgetQuarterlyReportGroupModel
    {
        MappedType = g.Key,
        Reports = g.Select(i => new BudgetQuarterlyReportViewModel
        {
            MappedType = i.MappedType,
            BudgetAmount = i.Budget.BudgetAmount,
            BudgetName = i.Budget.BudgetName,
            BudgetType = i.Budget.BudgetType,
            BudgetId = i.BudgetId
        }).ToList(),
        TotalMappedAmount = g.Sum(m => m.MappedAmount),
        RemainingAmount = // some logic here 
    });

---

Notes:

• For the above grouping you don't need to group by new operator unless you want to group by several properties, like:

...
group n by new {n.MappedType, n.Budget.BudgetName} into g
...

• Consider also using GroupJoin, for cases when you want to combine grouping and joining as a single operation.
• Before even writing a query, always write down your desired results and step by step, analyze your query trying to feet into your design.

Hope this will help ))