Group By Question

Reply

cenk1536

Member

526 Points

2726 Posts

Group By Question

Aug 26, 2019 11:12 AM|cenk1536|LINK

Hi,

I have an asp.net MVC core application. I am trying to query database in one of my controller's action. How can I add this dto into my View? Or is there any other to retrieve data by grouping and display on the view.

Here is my action:

namespace GameMonitor.Controllers
{
    public class GameBanksController : Controller
    {
        private readonly GameContext _context;

        public GameBanksController(GameContext context)
        {
            _context = context;
        }

        // GET: GameBanks
        public async Task<List<GameBanksDto>> Index()
        {
            //Group By games
            var games = await _context.GameBanks.GroupBy(g =>new {g.ProductCode,g.UnitPrice,g.ProductDescription})
                .Select(gcs => new GameBanksDto()
                {
                    ProductCode = gcs.Key.ProductCode,
                    ProductDescription = gcs.Key.ProductDescription,
                    UnitPrice = gcs.Key.UnitPrice,
                    Quantity = gcs.Sum(g => g.Quantity)


                }).ToListAsync();

            return View(games);
        }


        
    }
}

Here is my View:

@model IEnumerable<GameMonitor.Models.GameBanks>


@{
    ViewData["Title"] = "Index";
}

<h2>Games</h2>

@*<p>
        <a asp-action="Create">Create New</a>
    </p>*@
<table class="table">
    <thead>
        <tr>

            <th>
                @Html.DisplayNameFor(model => model.ProductCode)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Quantity)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.RequestDateTime)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Used)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.ProductDescription)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.UnitPrice)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Status)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>

                <td>
                    @Html.DisplayFor(modelItem => item.ProductCode)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Quantity)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.RequestDateTime)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Used)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ProductDescription)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.UnitPrice)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Status)
                </td>
                <td>
                    <a asp-action="Details" asp-route-id="@item.GameBankId">Details</a>

                </td>
            </tr>
        }
    </tbody>
</table>

Keep your friends close and your enemies even closer

Reply

Koss86

Member

11 Points

19 Posts

Re: Group By Question

Aug 26, 2019 12:56 PM|Koss86|LINK

other ways to upload data to DTO Class?

you can use custom mapping in SqlConnection. Still have to use grouping.

Reply

Koss86

Member

11 Points

19 Posts

Re: Group By Question

Aug 26, 2019 01:03 PM|Koss86|LINK

See! I use sql.

public List<GameBanksDto> GetGameBanksDto()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
            {
                string work = string.Format(@"SELECT ProductCode, sum(Quantity) Quantity From GameBanks group by ProductCode ");

                conn.Open();
                SqlCommand cmd = new SqlCommand(work, conn);

                cmd.CommandTimeout = 0;

                try
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    var tmp_fromMap = reader.MapToModel<GameBanksDto>();

                    return tmp_fromMap;

                }
                catch(Exception ex)
                {
                    return new List<GameBanksDto>();
                }

            }
}

( reader.MapToModel<SchedulerDTO>(); - my custom mapper from IDataReader to DTOClass)