How to select distinct DATE in MVC?

I want to select distinct date but its not working and duplicated

This is the SELECT I tried it and its working in SQL Server :

SELECT DISTINCT date 
FROM STATISTICS_DAILY 
WHERE hospital_no = 1

This is the output I get:

2023-06-06 00:00:00.000
2023-06-07 00:00:00.000
2023-06-08 00:00:00.000
2023-06-08 15:21:46.000
2023-06-09 00:00:00.000
2023-06-10 00:00:00.000

But in ASP.NET MVC, how to get same result this is Index controller :

 public ActionResult Index()
 {
     int HospitalId = Convert.ToInt32(Session["HospitalID"]);

     var sTATISTICS_DAILY = db.STATISTICS_DAILY
                              .Where(x => x.hospital_no == HospitalId)
                              .OrderBy(d => d.DATE)
                              .Distinct()
                              .ToList();
     return View(sTATISTICS_DAILY.ToList());
 } 

This is the view markup :

@model IEnumerable<warehouse.Models.STATISTICS_DAILY>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_LayoutDashboard.cshtml";
}

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.DATE)
        </th>
       
    
    </tr>

@foreach (var item in Model) {
    <tr>
      
        <td>
            @Html.DisplayFor(modelItem => item.DATE)
        </td>
       
     
        <td>
            @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
            @Html.ActionLink("Details", "Details", new { id=item.ID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.ID })
        </td>
    </tr>
}

</table>

but its not distinct this is the output

how to solve it and select distinct date one time only ?

Your Linq query, as-is, doesn't actually filter rows by any single distinct column value: in your case the .Distinct() step returns distinct outputs, which in this case are not DateTime values but actually objects representing source rows, which are by nature distinct (as objects in-memory tend to be), hence why it isn't filtering.

The solution is to change your query to first extract the date value (which will turn your query into IQueryable<DateTime> instead of IQueryable<STATISTICS_DAILY>; also, because .Distinct() is a reduction operation you will lose access to the other columns in STATISTICS_DAILY, which is something I assume you're already familiar with:

public async Task<ActionResult> Index( CancellationToken cancellationToken )
{
    Int32 hospitalId = Convert.ToInt32( this.Session["HospitalID"] ); // Avoid using Session-state btw.
    List<DateTime> values = await this.db.STATISTICS_DAILY
        .Where( s => s.hospital_no == hospitalId )
        .Select( s => s.DATE )
        .Distinct()
        .OrderBy( dt => dt )
        .ToListAsync( cancellationToken );

     return this.View( values );
} 

You'll need to change your Razor .cshtml file's @model to List<DateTime> btw too.