Get the Average Score and display

Question

Hi everyone,

 

I would like to ask how can I display the service ratings in view.

 

For now, I have put in one SQL Statement in my controller: 

SELECT s.Services_id, s.Service_Name,s.Service_Description,s.Price,AVG(r.Score)
FROM  Rating r
FULL OUTER JOIN Services s ON r.Services_Id = s.Services_Id
WHERE s.Service_Status = 'active'
GROUP BY s.Services_id,r.Services_Id,s.Service_Name,s.Service_Description,s.Price

 

In my model: 

 

public class Services
   {
       public int Services_id {get; set;}
       public string Service_Name { get; set; }
       public string Service_Description { get; set; }
       public Decimal Price { get; set; }
       public string Service_Status { get; set; }
       public int Service_Provider_Id { get; set; }
       public int Currency_Id { get; set; }
       public string Currency_Name { get; set; }
       public int Category_id { get; set; }
       public int Rating_Id { get; set; }
       public int Score { get; set; }
       public int Service_Score { get; set; }
   }

In my view:

 

@{
    Layout = "_Guest_NavBar";
}
@model List<Services>
<title>ServeTheWorld</title>
<link href="~/lib/datatables/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>

<script>
    $(document).ready(function () {
        $('#DataTable').DataTable({
            paging: true,
            ordering: true,
            searching: true,
            info: true,
            lengthMenu: [[6, 10, 20, -1], [6, 10, 20, "All"]]
        });
    });
</script>

<h1>Services Available</h1>
<table id="DataTable" class="table-style table">
    <thead>
        @{
            var sno = 1;
        }
        <tr>
            <th>No.</th>
            <th>Service Name</th>
            <th>Service Description</th>
            <th>Service Price</th>
            <th>Ratings</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (Services s in Model)
        {
            <tr>
                <td>@s.Services_id</td>
                <td>@s.Service_Name</td>
                <td>@s.Service_Description</td>
                <td>@s.Price</td>
                <td>
                    @for (int i = 0; i < (Model.Sum(s=>s.Score)/Model.Count); i++)
                    {
                        <span class="fa fa-star" style="color:darkorange"></span>
                    }

                    @for (int i = (Model.Sum(s => s.Score) / Model.Count); i < 5; i++)
                    {
                        <span class="fa fa-star-o" style="color:darkorange"></span>
                    }
                </td>
                <td><a asp-controller="Homepage" asp-action="ViewServices" asp-route-id="@s.Services_id"><i class="fa fa-info-circle" style="font-size:30px"></i></a></td>
            </tr>
        }
    </tbody>
</table>

 

 

Now, I want to display all the services and their rating score on the webpage. 

 

When I run once with the SQL Statement that I put into my controller action, it does not show the rating on the webpage.

 

For example:

 

In my database, I have record with the Services_Id =  1 and this id has three rating scores(4,2,4) stored inside the rating table:

 

 

So I want to use AVG to calculate the average rating score and it has an average score of 3:

 

 

When I run and on the webpage does not display 3 stars which is the average score for Services_Id = 3:

 

 

How can I achieve this

 

Please help

 

Thank you