Using Entity Framework Core with SQLite for offline desktop apps

Field teams across Australia regularly work beyond reliable mobile coverage. Surveyors in the Pilbara, farm advisers across regional Victoria and community health workers travelling through the Red Centre can spend hours or days without a dependable connection. An offline desktop application must keep working with local records instead of failing whenever a VPN or cloud service is unreachable.

Using Entity Framework Core with SQLite for offline desktop apps creates a local-first architecture: queries and writes target a database file on the device, while synchronisation remains a separate concern. This suits Australian organisations that need Windows applications for small teams, field operations and sites with intermittent links. It also gives developers familiar LINQ, change tracking and migrations without requiring a database server beside every workstation.

Why SQLite suits offline desktop applications

SQLite is an embedded relational engine that stores data in a file managed by the operating system. It supports transactions, foreign keys, indexes and concurrent reads, yet needs no separate service to remain available. A desktop build can therefore continue capturing inspections, orders or case notes while disconnected, then reconnect later without changing the primary user workflow.

Entity Framework Core sits between the application and SQLite, mapping database rows to C# objects and handling queries, writes and schema changes. The local file remains the source of truth for the running app; synchronisation must be designed explicitly rather than treated as an automatic feature of the provider.

Setting up the Entity Framework Core data layer

Create a dedicated data project and install the provider plus design package, keeping all Entity Framework Core packages on the same major version. The CLI commands are:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

Define one DbContext for the application and resolve the database path under Environment.SpecialFolder.LocalApplicationData, creating its directory at startup. Avoid the executable folder because it may be read-only after installation. Register the context through dependency injection for testability, with the essential configuration resembling:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite($"Data Source={databasePath};Foreign Keys=True");
}

Manage the schema with migrations. Run dotnet ef migrations add InitialCreate during development, include the generated migration files in the desktop build, and apply them through Database.Migrate() during a controlled startup. Do not combine EnsureCreated() with migrations, because that can leave an existing database without the migration history needed for later upgrades.

Modelling data for local-first work

Give entities stable identifiers, such as Guid values generated by the application, so records can be created offline without relying on a server sequence. Add local metadata such as CreatedAtUtc, LastModifiedUtc, a server version and a synchronisation state when records will be shared. Keep these values on the entity or in a focused synchronisation entity, and test them against the round-trip behaviour of your chosen API.

Choose SQLite mappings deliberately. The provider stores decimal values as text by default, so monetary or measurement fields may be better represented as scaled integers or documented as strings. SQLite also has limited ordering support for some decimal and offset-date comparisons; store DateTime values in UTC when ordering matters, retain an offset separately, and map DateTimeOffset only after testing the required queries. Explicit indexes should reflect real filters, while large related graphs can be loaded selectively to keep memory use low.

Practical safeguards for dependable operation

A controlled startup routine prevents version, locking and storage problems from reaching the main workflow.

• Resolve the database location and ensure its directory exists.
• Enable foreign keys and configure a sensible busy timeout for competing access.
• Verify the schema against included migrations before allowing edits.
• Run a small read-and-write check, then report a locked or corrupt file clearly.

During normal operation, preserve work even when the application closes unexpectedly.

• Group related changes in a transaction so one failure cannot leave partial records.
• Use Entity Framework’s async APIs, while remembering that SQLite performs much of its I/O synchronously.
• Log operation, entity and error details without recording sensitive field values.

Synchronising local changes with a server

A local database does not remove the need to reconcile records created in different places. For teams working around Alice Springs, Broken Hill or isolated properties in western Queensland, the sync service should resume after connectivity returns, report its progress and remain independent of the desktop user interface. An ASP.NET Core API backed by Azure services is one option, but the transport and hosting choices should follow the organisation’s security and support needs.

Capture outbound changes in an outbox entity written during the same local transaction as the business change. Upload batches with an idempotency key so a timeout cannot create duplicate server records, and retain failed items locally for retry. Include tombstones for deletions, because a missing local row cannot otherwise tell the server that a record was intentionally removed.

Conflicts need domain-specific handling. Comparing the original client version with the current server version can detect competing edits; users may resolve material differences, while an agreed rule can merge low-risk fields. Blind last-write-wins is convenient but can discard a clinically significant note or a changed order, so each record type should have an explicit conflict policy and an audit trail.

Protecting performance and sensitive records

SQLite can handle a substantial desktop workload when queries are intentional. Add indexes for common filters and sort columns, use AsNoTracking() for read-only views, paginate long lists and avoid loading entire graphs for simple edits. Write-ahead logging can let readers work during a write, although the application should close connections cleanly and back up the database with SQLite-aware tooling rather than copying only the main file while write-ahead logging is active.

The local file is not automatically encrypted. Use operating-system controls such as BitLocker on Windows or encrypted Linux volumes, restrict file permissions, and keep credentials outside the database. If regulatory requirements demand encryption inside the file, evaluate SQLCipher or another supported encrypted format and test its provider and packaging path. Australian Privacy Principles, health-service requirements and organisational governance rules may all affect what can be synchronised, logged or retained; an Aboriginal community-controlled health service should also apply its own information-governance decisions.

Backups should preserve recoverable work without exposing another copy unnecessarily. Use a SQLite backup mechanism or a consistent snapshot, include the relevant journal files, encrypt the destination and test restoration on a separate machine. Monitor free disk space because a failed write can strand users on a remote site until an administrator supplies more capacity.

Testing, packaging and Australian field support

Automate tests against a temporary SQLite file so they exercise real transactions, migrations and locking behaviour. Include cases for interrupted synchronisation, duplicate delivery, a process closing during a write, a read-only location and a full disk. Seed representative Australian dates and time zones, and verify that a failed upload can be retried without creating a second record or losing its local audit history.

Package a signed Windows installer or MSIX where appropriate, then test installation, first launch, upgrades and uninstall on machines managed by Australian small and mid-sized organisations. These buyers often value a self-contained product, predictable licensing and access to a local support vendor more than an elaborate backend they must operate themselves. Keep the database in user data rather than the program folder so upgrades do not overwrite field records.

Support material should use plain Australian English and match how teams speak without becoming cryptic. A local run book might call an afternoon connectivity test an “arvo check”, while the application itself should still identify errors clearly. Store timestamps in UTC and convert them for the user’s location because Perth, Adelaide, Brisbane and Sydney operate on different offsets and daylight-saving rules. A well-tested local-first release then remains dependable from a city office to an isolated worksite, with recovery and synchronisation handled as part of normal operation.