locked
LINQ sum and group statement and select view model class RRS feed

  • Question

  • User364607740 posted

    My project contains below classes and 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; }
           
            public BudgetModels Budget { get; set; } 
            public int BudgetId { get; set; }
           
            public SchoolModels School { get; set; }
            public int SchoolId { get; set; }
        }
    
    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 i.e., total amount mapped from each budget headings disregard to school name.

    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 POCO 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; }
        }

    I am trying with the following LINQ Query::

    // 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. Please help. Thank You!!!

    Thursday, August 27, 2020 3:25 PM

All replies

  • User1686398519 posted

    Hi scala_1988,

    1. Is the result of Report Format based on the data of the previous three tables?
      • According to your linq query statement, it seems that you only want to group by MappedType, but the result of the Report Format you provide seems to be grouped by MappedType and BudgetId.
      • According to either of the two grouping methods mentioned above, the results of the report format you provided do not seem to match.
    2. I modified the code according to the two grouping methods mentioned above.
      • You can use the SUM() function to sum.
      • In the modified code, I understand RemainingAmount as BudgetAmount minus TotalMappedAmount.

    More details, you could refer to below code:

    Controller

            //Group by MappedType
    public ActionResult Index() { var result = (from n in db.MappedModels join k in db.BudgetModels on n.BudgetId equals k.BudgetId group new { n, k } by n.MappedType into g select new BudgetQuarterlyReportViewModel { BudgetId = g.FirstOrDefault().k.BudgetId, BudgetType = g.FirstOrDefault().k.BudgetType, BudgetName = g.FirstOrDefault().k.BudgetName, BudgetAmount = g.FirstOrDefault().k.BudgetAmount, MappedType = g.FirstOrDefault().n.MappedType, TotalMappedAmount = g.Sum(t => t.n.MappedAmount), RemainingAmount = g.FirstOrDefault().k.BudgetAmount - g.Sum(t => t.n.MappedAmount) } ).ToList(); return View(result); } //Group by MappedType and BudgetId. public ActionResult Index2() { var result = (from n in db.MappedModels join k in db.BudgetModels on n.BudgetId equals k.BudgetId group new { n, k } by new { n.MappedType, k.BudgetId } into g select new BudgetQuarterlyReportViewModel { BudgetId = g.FirstOrDefault().k.BudgetId, BudgetType = g.FirstOrDefault().k.BudgetType, BudgetName = g.FirstOrDefault().k.BudgetName, BudgetAmount = g.FirstOrDefault().k.BudgetAmount, MappedType = g.FirstOrDefault().n.MappedType, TotalMappedAmount = g.Sum(t => t.n.MappedAmount), RemainingAmount = g.FirstOrDefault().k.BudgetAmount - g.Sum(t => t.n.MappedAmount) } ).ToList(); return View(result); }

    Index(The main code on the Index2 view is the same as on the Index, so it is omitted.)

    @model IEnumerable<WebApplication26.Models.BudgetQuarterlyReportViewModel>
    <table class="table">
        <tr>
            <td>BudgetName</td>
            <td>BudgetType</td>
            <td>BudgetAmount</td>
            <td>MappedType</td>
            <td>TotalMappedAmount</td>
            <td>RemainingAmount</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.BudgetName</td>
                <td>@item.BudgetType</td>
                <td>@item.BudgetAmount</td>
                <td>@item.MappedType</td>
                <td>@item.TotalMappedAmount</td>
                <td>@item.RemainingAmount</td>
            </tr>
    
        }
    </table>
    @Html.ActionLink("Go to Group by MappedType and BudgetId", "Index2", "Test36")

    Here is the result.

    Best Regards,

    YihuiSun

    Friday, August 28, 2020 5:50 AM
  • User364607740 posted

    Thank You Sir for your nice explanation. It is really helpful for novice developers and also I got encouragement to proceed further. I will try the code and let you know. Thank You very much.

    Friday, August 28, 2020 9:10 AM