.NET MAUI中sqlite-net-pcl的Query<>方法执行失败问题排查

我在.NET MAUI项目的数据库上下文类中编写了LoadSessionData方法用于加载训练会话数据，对应的SQL语句在数据库管理系统中测试正常，但调用connection.Query<>方法时出现异常，导致后续调试代码完全不执行。尝试过Query<dynamic>也没用，已确认数据库连接可用，但查询始终无法正常运行。

核心方法代码 #

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 = ?";

    // The following line gets printed:
    Debug.WriteLine($"----Executing SQL query: {sql}");

    // Execute the query with parameter sessionID
    var results = connection.Query<ExerciseSetDetail>(sql, sessionID);

    // The following line is never printed:
    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;
}

实体类代码 #

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; }
}

数据库连接代码 #

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);
            
        }
    }
}

排查方向 #

• 捕获异常：在Query<>调用处添加try-catch块，直接输出异常信息，当前代码未处理异常，可能是抛出未捕获异常导致后续代码终止：

  try
  {
      var results = connection.Query<ExerciseSetDetail>(sql, sessionID);
      Debug.WriteLine($"----Query executed successfully. Rows returned: {results.Count()}");
  }
  catch (Exception ex)
  {
      Debug.WriteLine($"----Query failed: {ex.Message}\n{ex.StackTrace}");
      throw;
  }
  

• 验证数据类型映射：确认数据库字段类型与ExerciseSetDetail属性类型完全匹配，比如数据库Weight是INT还是FLOAT？类中double?是否对应正确；Repetitions若为非空，类中int?是否会导致映射问题。
• 检查参数传递：尝试使用命名参数替换?占位符，比如将SQL中的?改为@sessionID，并传递new { sessionID }作为参数。
• 确认数据库一致性：检查嵌入到APP的数据库与测试用的数据库结构是否一致，包括表名、字段名的拼写和大小写（SQLite在iOS/Android下区分大小写）。
• 测试基础查询：先执行简单查询（如SELECT * FROM Sets LIMIT 1），验证Query<>方法本身能否正常运行。
• 核对表名与字段名：确认数据库中Sets、Exercises表名，Repetitions、SessionID等字段名拼写完全正确，无大小写或拼写错误。

内容的提问来源于stack exchange，提问作者User17353