I have the following method which is inside a database context class for a .net maui project. I have tested the sql query in a dbms without an error so know it should be working, using debug.writeline I have come to the conclusion that the connection.query<> is the issue but can’t find anything online/in documentation about why.

    public List<ExerciseDetail> LoadSessionData(int sessionID)
    {
        Debug.WriteLine($"------------------------------------dbStuff loadsessiondata is called with sessionID: {sessionID}");
        // SQL query to fetch data
        var sql = @"
        SELECT e.ExerciseName,
               s.Repetitions AS Reps,
               s.Weight,
               s.Duration,
               s.Distance
        FROM Sets s
        JOIN Exercises e ON s.ExerciseID = e.ExerciseID
        WHERE s.SessionID = ?";
        Debug.WriteLine($"-----------------------------------------Executing SQL Query: {sql}");
        // Execute the query with parameter sessionID
        
        var results = connection.Query<ExerciseSetDetail>(sql,sessionID );
        Debug.WriteLine($"---------------------------------------------Query executed successfully. Rows returned: {results.Count()}");

        // Process results to group by ExerciseName and collect details
        var groupedResults = results
            .GroupBy(r => r.ExerciseName)
            .Select(g => new ExerciseDetail
            {
                ExerciseName = g.Key,
                Sets = g.Select(x => new SetDetails
                {
                    Reps = (int?)x.Reps,
                    Weight = (int?)x.Weight,
                    Duration = (int?)x.Duration,
                    Distance = (double?)x.Distance
                }).ToList<SetDetails>()
            }).ToList();
        Debug.WriteLine($"------------------------------------Reach after grouping");
        return groupedResults;
    }

}


I have the class:
    

public class ExerciseSetDetail
    {
        public string ExerciseName { get; set; }
        public int? Reps { get; set; }
        public double? Weight { get; set; }
        public int? Duration { get; set; }
        public double? Distance { get; set; }
    }

Which i made for the query<> as i was originally doing query which also didn’t work.

my connection happens at the start of the database context:

        public dbStuff()
        {
            string dbPath = Path.Combine(FileSystem.AppDataDirectory, "AppDatabase.db");
            CheckDB(dbPath);

            connection = new SQLiteConnection(dbPath);
           
        }
        private void CheckDB(string dbPath)
        {
            if (!File.Exists(dbPath))
            {
                CopyDatabaseFromResource(dbPath);
            }
        }

        private void CopyDatabaseFromResource(string dbPath)
        {
            var assembly = IntrospectionExtensions.GetTypeInfo(typeof(App)).Assembly;
            using (Stream? stream = assembly.GetManifestResourceStream("GymAppProject.Resources.Database.AppDatabase.db"))
            {
                if (stream == null)
                {
                    throw new FileNotFoundException("Embedded database not found in the assembly resources.");
                }

                using (FileStream fileStream = new FileStream(dbPath, FileMode.Create))
                {
                    stream.CopyTo(fileStream);
                    
                }
            }
        }

I have tried to do the query which gave the same results. One thing I was yet to try was make a new object of SQLConnection but it seemed pointless as I already have a connection and have got data out of tables using said connection already, albeit not querying like this.