I have a Referrals database table. Here is part of the model:

using System.ComponentModel.DataAnnotations;

namespace Referrals.Model
{
    public class Referral
    {
        [Key]
        public int Id { get; set; }
        [Display(Name = "Referral No (###.#-YYYY)")]
        public string RefNoComplete { get; set; }
        [Display(Name = "Date Received")]
        [DataType(DataType.Date)]
        public DateTime DateReceived { get; set; }
        [Display(Name = "Meeting Month")]
        public string? BoardMtgMonth { get; set; }
        [Display(Name = "Meeting Year")]
        public string? BoardMtgYear { get; set; }
        [Display(Name = "Application Type")]
        public string? ApplicationType { get; set; }

I need to get counts for each application type for each 'BoardMtgYear'.

The format I would like it to return is (preferably right justified):

I need to get this onto my razor page and need to have a way to input the year parameters. So, the user would enter 2019, 2020, 2021 (or whatever years they need - it just needs to compare 3 years)

Here is what I have on my view. I know I have butchered the syntax, but that is what I need help with.

It would also be great to have a total of each year column at the bottom of the table, but I don't even know where to start with html for that!

@page
@model Referrals.Pages.Reports.AppTypeByYear.IndexModel

<partial name="_Notification" />

<div id="" >


    <br /><br />

    <form asp-page="./Index" method="post">
        <div >
            <p>
                Input First Year: <input type="text" name="firstyear" value="" />, Second Year: <input type="text" name="secondyear" value="" /> and Last Year: <input type="text" name="thirdyear" value="" />
                <input type="submit" value="Search"  />
                <a asp-page="./Index" >Return to Full List</a>
            </p>
        </div>

        <div id="AppTypeTable">
            <div >
                <h2  align="center">Planning Board</h2>
                <h3  align="center">Referral Applications By Type By Year</h3>
            </div>

            <table id="ReferralTable"  style="width:100%">
                <thead>
                    <tr>
                        <th>
                            Referral Type
                        </th>
                        <th>
                            First Year
                        </th>
                        <th>
                            Middle Year
                        </th>
                        <th>
                            Last Year
                        </th>
                    </tr>
                </thead>
                <tbody>

                    @foreach (var obj in Model.Referral)
                    {
                        <tr>
                            <td width="60%">@obj.ApplicationType</td>
                            <td width="20%">count(@obj.BoardMtgYear.Where <param name="firstyear" />)</td>
                            <td width="20%">count(@obj.BoardMtgYear.Where<param name="secondyear" />)</td>
                            <td width="20%">count(@obj.BoardMtgYear.Where<param name="thirdyear" />)</td>
                        </tr>
                    }
                </tbody>
            </table>
        </div>
    </form>
</div>

Thanks for any and all assistance. I am still a beginner at both C# and razor pages.

EDITED TO ADD: Revised .cs:

using Referrals.Data;
using Referrals.Model;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Referrals.Pages.Reports.AppTypeByYear;

public class IndexModel : PageModel
{

    private readonly ApplicationDbContext _db;

    public IEnumerable<Referral> Referral { get; set; }
    public List<Referral> BoardMtgYear1 { get; set; }
    public List<Referral> BoardMtgYear2 { get; set; }
    public List<Referral> BoardMtgYear3 { get; set; }

    public void OnGet()
    {

    }
    public void OnPost(string firstyear, string secondyear, string thirdyear)
    {
        BoardMtgYear1 = Referral.Where(a => a.BoardMtgYear == firstyear).ToList();
        BoardMtgYear2 = Referral.Where(a => a.BoardMtgYear == secondyear).ToList();
        BoardMtgYear3 = Referral.Where(a => a.BoardMtgYear == thirdyear).ToList();
    }

}

CodePudding user response：

It seems you want to enter the three year's values as search condition. Then you can get counts for each Applicationtype for each 'BoardMtgYear'. Also get count for each year in the last row.

A whole working demo you could follow:

Page

@page
@model IndexModel
<div id="" >   

    <br /><br />   
    <form asp-page="./Index" method="post">
        <div >
            <p>
                Input First Year: <input type="text" name="firstyear" value="" />, Second Year: <input type="text" name="secondyear" value="" /> and Last Year: <input type="text" name="thirdyear" value="" />
                <input type="submit" value="Search"  />
                <a asp-page="./Index" >Return to Full List</a>
            </p>
        </div>
        @if (Model.Referral != null && Model.Referral.Count() != 0)
        {
            <div id="AppTypeTable" >
                <div >
                    <h2  align="center">Planning Board</h2>
                    <h3  align="center">Referral Applications By Type By Year</h3>
                </div>

                <table id="ReferralTable"  style="width:100%">
                    <thead>
                        <tr>
                            <th>
                                Referral Type
                            </th>
                            <th>
                                First Year
                            </th>
                            <th>
                                Middle Year
                            </th>
                            <th>
                                Last Year
                            </th>
                        </tr>
                    </thead>
                    <tbody>

                        @foreach (var obj in Model.Referral.Select(a=>a.ApplicationType).Distinct())
                        {
                            <tr>
                                <td width="60%">@obj</td>
                                <td>@Model.BoardMtgYear1.Where(a=>a.ApplicationType==obj).Count()</td>
                                <td>@Model.BoardMtgYear2.Where(a=>a.ApplicationType==obj).Count()</td>
                                <td>@Model.BoardMtgYear3.Where(a=>a.ApplicationType==obj).Count()</td>
                            </tr>
                        }
                        <tr>
                            <td width="60%">Total</td>
                            <td>@Model.BoardMtgYear1.Count()</td>
                            <td>@Model.BoardMtgYear2.Count()</td>
                            <td>@Model.BoardMtgYear3.Count()</td>
                        </tr>
                    </tbody>
                </table>
            </div>
            
        }

    </form>
</div>
@section Scripts
{
    <style>
        tr{
            text-align:right;
        }
    </style>
}

PageModel

public class IndexModel : PageModel
{
    public List<Referral> Referral { get; set; } 
    public List<Referral> BoardMtgYear1 { get; set; }
    public List<Referral> BoardMtgYear2 { get; set; }
    public List<Referral> BoardMtgYear3 { get; set; }

    public void OnGet()
    {
        
    }
    public void OnPost(string firstyear,string secondyear,string thirdyear)
    {
        //hard-coded the data for easy testing....
        Referral  = new List<Referral>()
        {
            new Referral(){BoardMtgYear="2019",ApplicationType="aa"},
            new Referral(){BoardMtgYear="2019",ApplicationType="aa"},
            new Referral(){BoardMtgYear="2019",ApplicationType="aa"},
            new Referral(){BoardMtgYear="2019",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2019",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2019",ApplicationType="cc"},
            new Referral(){BoardMtgYear="2019",ApplicationType="cc"},
            new Referral(){BoardMtgYear="2019",ApplicationType="cc"},
            new Referral(){BoardMtgYear="2020",ApplicationType="aa"},
            new Referral(){BoardMtgYear="2020",ApplicationType="aa"},
            new Referral(){BoardMtgYear="2020",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2020",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2020",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2020",ApplicationType="cc"},
            new Referral(){BoardMtgYear="2021",ApplicationType="aa"},
            new Referral(){BoardMtgYear="2021",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2021",ApplicationType="bb"},
            new Referral(){BoardMtgYear="2021",ApplicationType="cc"},
            new Referral(){BoardMtgYear="2021",ApplicationType="cc"},
            new Referral(){BoardMtgYear="2021",ApplicationType="cc"},
        };
        BoardMtgYear1 = Referral.Where(a => a.BoardMtgYear == firstyear).ToList();
        BoardMtgYear2 = Referral.Where(a => a.BoardMtgYear == secondyear).ToList(); 
        BoardMtgYear3 = Referral.Where(a => a.BoardMtgYear == thirdyear).ToList();         
    }       
}

Result