Help with COUNT mysql and Asp Net Core

Question

In my system I have a form for opening calls, in which each call has the following status: Aberto, Agendado, Em Atendimento and Finalizado.

I would like to exhibit in home the amount of each one currently called, for this I created the following mysql script:

string sql = "SELECT STATUS, COUNT(STATUS) AS quantidade FROM chamados GROUP BY STATUS";

In mysql I get the correct return, for example :

3 Open
2 In attendance
1 Scheduled
1 Completed

My doubt now and how using . Net Core I can display this record in my home.

As requested by colleagues, follows my most detailed problem: In the print below I have these cards where I would like to display the count of records made with each status type:

The connection to the Database has already been made, makes the Insert, update and delete normally, the problem is on how to make my sql query return the counts and can display it in the Home.

In Model I used the following code (I just added the same query)

 public void Contagem()
    {
        string sql = "SELECT STATUS, COUNT(STATUS) AS quantidade FROM chamados GROUP BY STATUS";
    }

Then I need to record this in some variable and then display this variable in my Index.

          <div class="col-lg-3 col-xs-6">
      <div class="small-box bg-green">
        <div class="inner">
          <h3>40</h3> //Aqui iria retornar a quantidade de chamados em atendimento

          <p>Chamados em Atendimento</p>
        </div>
        <div class="icon">
          <i class="fa fa-headset"></i>
        </div>
        <a href="~/Chamados/Index" class="small-box-footer">Ver todos <i class="fa fa-arrow-circle-right"></i></a>
      </div>
    </div>

DAL file connecting to the database

using System.Data;
using MySql.Data.MySqlClient;

namespace HelpDesk.Uteis
{
    public class DAL 
    {
        private static string Server = "localhost";
        private static string Database = "helpdesk";
        private static string User = "root";
        private static string Password = "root";
        private static string ConnectionString = $"Server={Server};Database={Database};Uid={User};Pwd={Password};Sslmode=none;Charset=utf8;";

        private static MySqlConnection Connection;

        public DAL()
        {
            Connection = new MySqlConnection(ConnectionString);
            Connection.Open();
        }

        //Espera um parâmetro do tipo string 
        //contendo um comando SQL do tipo SELECT
        public DataTable RetDataTable(string sql)
        {
            DataTable data = new DataTable();
            MySqlCommand Command = new MySqlCommand(sql, Connection);
            MySqlDataAdapter da = new MySqlDataAdapter(Command);
            da.Fill(data);
            return data;
        }

        //Espera um parâmetro do tipo string 
        //contendo um comando SQL do tipo INSERT, UPDATE, DELETE
        public void ExecutarComandoSQL(string sql)
        {
            MySqlCommand Command = new MySqlCommand(sql, Connection);
            Command.ExecuteNonQuery();
        }
    }
}

Answer 1

Make an object that receives this 2 information and then send it to the View

Model

public class ObjStatus
{
    public int quantidade{get; set;}
    public string status {get; set;}
}

Repository

public class RpoStatus 
{
    ...
    //Se for uma lista usar List<ObjStatus>, se for somente popular objeto usar ObjStatus e troque .ToList() para .FirstOrDefault()
    public List<ObjStatus> mtdListarStatus(){
        string sql = "SELECT STATUS, COUNT(STATUS) AS quantidade FROM chamados GROUP BY STATUS";
        return dbContext.Database.SqlServer<ObjStatus>(sql).ToList();
    }
}

Controller

ObjStatus obj = new ObjStatus();
RpoStatus rpo = new RpoStatus();
obj = rpo.mtdListarStatus();
return View(obj);

View (Edit as needed)

 @model.namespace.ObjStatus

// Se for uma lista, fazer o foreach, caso contrario desta forma:
<div class="inner">
    @Html.LabelFor(model => model.quantidade) 
    @Html.LabelFor(model => model.staus)
</div>