What's the best way to archive records in SQLite using EF Core to a new file

Question

I have a .NET 6 application that writes logs out to a SQLite file each time a cycle is completed. I'm using EF Core.

The application sits on a Raspberry Pi with limited resource, so I want to keep the live database small as when it gets large the system slows down. So I want to archive the logs to only keep the last 50 or so logs in the live database.

I am hoping to create a method that will will create a new SQLite database file and then copy the last oldest record over when a new log is created. I'd also want to limit how big the archive file is, maybe split out to create a new one once it reaches a certain size on disk.

I've had a look around and can't find any best practices anything documented. Could someone give me a steer to the best way to achieve this or similar.

Answer 1

I solved my issue by putting EFCore aside and instead using the sqlite-net-pcl nuget package from SQLite-net.

This allowed me to separate the creation of my archive and also apply additional commands not supported in EFCore like vacuum.

I still use EFCore and Linq to query the records to create my archive with and then again to remove those records once the archive is created.