So I have this Table with class Report:

Based on the ProductId and ReportType I want to create a LINQ expression that returns a List<ReportInnerResponse>, where ReportInnerResponse contains the following:

    public class ReportInnerResponse
    {
        public int Id { get; set; }
        public ReportType ReportType { get; set; }
        public int ReportCount { get; set; }
    }

So my final result would be something like this:

Here is my current code, but it does not work properly:

        public ActionResult GetReportsById(int id)
        {
            var reports = DbContext.Reports.GroupBy(x => new {x.ProductId, x.ReportType}).Select(g => g.FirstOrDefault()).Where(x => x.ProductId == id).Select(r =>
            new Models.Response.ReportInnerResponse
            {
                Id = r.Id,
                ReportType = r.ReportType,
                ReportCount = DbContext.Reports.Where(x => x.ReportType == r.ReportType && x.ProductId == id).Count()
            }).ToList();
            return PartialView("_ProductReportDetails", reports);
        }

Main View:

@model IEnumerable<Finder.Models.Response.AdminReportResponse>

@{
    ViewBag.Title = "Report Panel | Finder";
}

<h2>Report Panel</h2>
<table >
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Image)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Title)
        </th>
        <th>
            Seller
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Price)
        </th>
        <th>
            Report Info
        </th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                <img src="@item.Image" height="100px" width="100px" style="object-fit: contain" />
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Title)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.FullName)
            </td>
            <td>
                @{ 
                    var price = item.Price.ToString("C");
                }
                @price
            </td>
            <td>
                @if (item.ReportCount >= 1)
                {
                    <span>@item.ReportCount</span>
                    @Html.Action("GetReportsById", new { id = item.Id })
                }
            </td>
        </tr>
    }

</table>

Partial View:

@model List<Finder.Models.Response.ReportInnerResponse>

<!-- Button trigger modal -->

<button type="button"  data-toggle="modal" data-target="#exampleModalCenter">
    <i  style="margin-right:10px;"></i> See Reports
</button>

<!-- Modal -->
<div  id="exampleModalCenter" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true">
    <div  role="document" style="width: 100%">
        <div >
            <div >
                <h5  id="exampleModalLongTitle">Report Menu</h5>
                <button type="button"  data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
            </div>
            <div >
                @foreach (var item in Model)
                {
                    <p><span>@item.ReportCount</span>@item.ReportType</p>
                }
            </div>
        </div>
    </div>
</div>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>

Any help would be appreciated.

CodePudding user response：

Id property on Models.Response.ReportInnerResponse does not make much sense, TBH, since it represents grouping result (or it should be named ProductId). Remove it and try rewriting your query this way:

var reports = DbContext.Reports
    .Where(x => x.ProductId == id) // filter out not needed data before grouping
    .GroupBy(x => x.ReportType)
    .Select(g => new Models.Response.ReportInnerResponse
        {
            ReportType = g.Key,
            ReportCount = g.Count()
        })
    .ToList();