How to map query result to a class and then a list to then use to populate listview? can't cast query result to class

Question

I am trying to populate a listview so I am starting by mapping the result from a query to a class model but it keeps giving me this exception

System.Data.DataException Message=Error parsing column 0 (TimeOfAppointment=15:30:00 - Object) Source=Dapper
InvalidCastException: Object must implement IConvertible.

my class is

public class AppointmentModel
    {
        public string PatientName { get; set; }
        public int AppointmentId { get; set; }
        public DateTime Date { get; set; }
        public DateTime TimeOfAppointment { get; set; }
        public int Duration { get; set; }
    }

my query is

CREATE PROCEDURE [dbo].[spGet_Appointments_byDay]
    @DateSelected date
AS
begin
select [dbo].[Appointments].[TimeOfAppointment], [dbo].[Appointments].[Duration], [dbo].[Patients].[Name], [dbo].[Appointments].[Date], dbo.Appointments.AppointmentId
from [dbo].[Appointments]
inner join [dbo].[Patients] on [dbo].[Appointments].PatientId = [dbo].[Patients].PatientID
where [dbo].[Appointments].[Date] = @DateSelected;
end

this is how i call it

public List<AppointmentModel> CreateListViewList(DateTime date)
        {
            List<AppointmentModel> ListForListView;
            var l = new DynamicParameters();
            l.Add("@DateSelected", date);
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
            {
                ListForListView = connection.Query<AppointmentModel>("[Test DB].dbo.spGet_Appointments_byDay", l, commandType: CommandType.StoredProcedure).ToList();
            }

            return ListForListView;
        }

and this is populating the list box

private void button1_Click_1(object sender, EventArgs e)
        {
            foreach (IDataConnection db in GlobalConfig.Connections)
            {
                ListForListView.AddRange(db.CreateListViewList(AppointmentDateBox.Value.Date));
            }
            
            foreach (AppointmentModel RowFromQuery in ListForListView)
            {
                ListViewItem viewItem = new ListViewItem(RowFromQuery.TimeOfAppointment.ToString());
                viewItem.SubItems.Add(RowFromQuery.Duration.ToString());
                viewItem.SubItems.Add(RowFromQuery.PatientName);
                viewItem.SubItems.Add(RowFromQuery.AppointmentId.ToString());

                listView1.Items.Add(viewItem);

            }
        }

my tables

CREATE TABLE [dbo].[Appointments]
(
    [AppointmentId] INT NOT NULL PRIMARY KEY IDENTITY, 
    [PatientId] INT NOT NULL, 
    [Date] DATE NOT NULL, 
    [Duration] INT NOT NULL, 
    [TimeOfAppointment] TIME NOT NULL, 
)

CREATE TABLE [dbo].[Patients]
(
    [PatientID] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] VARCHAR(50) NOT NULL, 
)

I am really new to this so what am I doing wrong?

Answer 1

try to use TimeSpan in your model for Time data type.

//public DateTime TimeOfAppointment { get; set; }

public TimeSpan TimeOfAppointment { get; set; }