Query API

Execute SQL queries with type-safe parameter binding

Query Basics

The [Query] attribute allows you to write custom SQL queries with compile-time type checking and parameter binding.

[Query("SELECT * FROM users WHERE Id = :userId")]
Task<User?> GetByIdAsync(long userId);

[Query("SELECT * FROM users WHERE Email = :email")]
Task<User?> FindByEmailAsync(string email);

Parameter Binding

Use :paramName syntax to bind method parameters to SQL placeholders:

// Single parameter
[Query("SELECT * FROM todos WHERE ListId = :listId")]
Task<List<Todo>> GetTodosByListAsync(long listId);

// Multiple parameters
[Query("SELECT * FROM logs WHERE CreatedAt BETWEEN :start AND :end")]
Task<List<Log>> GetLogsInRangeAsync(DateTime start, DateTime end);

// Boolean parameters
[Query("SELECT * FROM todos WHERE IsCompleted = :completed")]
Task<List<Todo>> GetByStatusAsync(bool completed);

Return Types

Single Entity

// Nullable for queries that might return no results
[Query("SELECT * FROM users WHERE Id = :id")]
Task<User?> GetByIdAsync(long id);

Multiple Entities

[Query("SELECT * FROM users ORDER BY CreatedAt DESC")]
Task<List<User>> GetAllAsync();

[Query("SELECT * FROM users WHERE Name LIKE :pattern")]
Task<List<User>> SearchByNameAsync(string pattern);

Scalar Values

[Query("SELECT COUNT(*) FROM users")]
Task<int> GetCountAsync();

[Query("SELECT MAX(CreatedAt) FROM logs")]
Task<DateTime?> GetLastLogTimeAsync();

Query Operations

SELECT Queries

[Query("SELECT * FROM users ORDER BY Name ASC LIMIT :limit")]
Task<List<User>> GetTopUsersAsync(int limit);

[Query(@"SELECT u.*, p.Bio 
         FROM users u 
         LEFT JOIN profiles p ON u.Id = p.UserId 
         WHERE u.IsActive = :active")]
Task<List<UserWithProfile>> GetActiveUsersWithProfilesAsync(bool active);

UPDATE Queries

// Returns number of rows affected
[Query("UPDATE users SET IsActive = :active WHERE Id = :userId")]
Task<int> SetActiveStatusAsync(long userId, bool active);

DELETE Queries

[Query("DELETE FROM logs WHERE CreatedAt < :cutoffDate")]
Task<int> DeleteOldLogsAsync(DateTime cutoffDate);

Raw Queries

For dynamic queries built at runtime, use [RawQuery]:

[RawQuery]
Task<List<User>> RunCustomQueryAsync(ISupportSQLiteQuery query);

// Usage
var query = SimpleSQLiteQuery.Create(
    "SELECT * FROM users WHERE Name LIKE ? AND Age > ?",
    "%john%",
    18
);
var results = await dao.RunCustomQueryAsync(query);

Best Practices

Use [Query] for static queries (compile-time validation)
Use [RawQuery] only when SQL must be built dynamically
Always return nullable types (T?) for queries that might return no results
Use appropriate return types (int for affected rows, List<T> for multiple results)
Prefer parameterized queries over string concatenation for security

Next Steps

DAO Interfaces - Learn about all DAO operations
Transactions - Combine queries in transactions
Generated Code - See what RoomSharp generates