SQLite查询报错排查：员工姓名模糊查询异步接口异常

Hey there! Let's walk through potential issues with your search functionality and how to fix them. Based on the code you shared, here are the most likely culprits and solutions:

1. Null Reference Risks #

Right now, if searchField is null or empty, calling searchField.ToLower() will throw a NullReferenceException. Additionally, if any Employee has a null EmpName, i.EmpName.Contains(...) will also cause an error. Let's add safety checks:

public async Task<List<Employee>> GetEmployeesAsyncBy(string searchField)
{
    // Handle empty or null search input gracefully
    if (string.IsNullOrWhiteSpace(searchField))
    {
        // Return empty list or all employees based on your needs
        return new List<Employee>();
    }

    var normalizedSearch = searchField.ToLower();
    return await database.Table<Employee>()
        // Skip entries with null EmpName to avoid exceptions
        .Where(i => !string.IsNullOrEmpty(i.EmpName) && i.EmpName.ToLower().Contains(normalizedSearch))
        .ToListAsync();
}

2. SQLite Collation & Case Sensitivity #

SQLite's default collation might behave differently depending on your setup. If case-insensitive search isn't working as expected, you can explicitly use SQLite's LIKE operator (which is case-insensitive by default in most configurations) by rewriting the query:

return await database.Table<Employee>()
    .Where(i => !string.IsNullOrEmpty(i.EmpName) && SQLiteNetExtensions.Extensions.TextFunctions.Like(i.EmpName, $"%{normalizedSearch}%"))
    .ToListAsync();

Note: If you're using SQLite-Net PCL, you might need to enable text functions or use raw SQL for more control.

3. Verify Async/Await Usage #

Make sure you're awaiting the method when calling it—if you just call GetEmployeesAsyncBy(searchTerm) without await, you'll get an uncompleted Task instead of the list of employees, which can lead to unexpected behavior or errors:

// Correct usage
var results = await GetEmployeesAsyncBy("John");

// Incorrect (will not return the actual list)
var results = GetEmployeesAsyncBy("John");

4. Database & Entity Configuration Checks #

• Double-check that your database instance is properly initialized and connected to a valid SQLite file.
• Confirm your Employee entity is mapped correctly: Ensure EmpName is annotated with [Column("EmpName")] if your database column uses that exact name, and that the property is of type string.
• If you're getting a SQLite-specific error, check the exception message—it will often tell you if a column is missing, the table doesn't exist, or there's a syntax issue.

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