Hello. I have a project. This is a site client that stores a list of the series you’ve watched.
Requesting your list from the site takes time, especially if the list is large. The smallest request slowed down the program for 5 (!) Seconds. Without a list, the application has no meaning, which means it cannot start working.

In this regard, I thought to solve the problems as follows. Request a list from the site when you first start the program and log in to the local database. Then display information from it. Updating the database would be carried out only with data from the server by requesting the list at a certain interval or when the user makes changes to the list.

Example. The user started the program for the first time. The program asked for a username and password and remembered the user. Then I sent a request to the server and got the list. The first launch will be long anyway. Further, this data would be stored in the local database. The user would already be presented with a list from the database. In order to keep the local database data up-to-date, once, for example, every 5 minutes, the program would send a new request to the server and update the data. Or when the user initiates changes in the software (adding the viewed series or other). This would have been done in a separate thread.

On subsequent launches of the program, it would start instantly, displaying the list saved locally to the user, while initiating an update request in a separate thread.

Database requirements

• Local. The database with the list of TV shows must be stored on the user’s computer in the program folder.
• Portable. The database should not require the user to install additional libraries on the user’s computer, except perhaps the .Net Framework, which will be required by the program itself. Unless it will be portable libraries that will be inside the folder of the program itself.
• Not bulky. Since the database will be on the user’s computer, I didn’t want a table with 10 fields with 1000 rows to weigh under a gigabyte.
• LINQ support. Natively or by connecting third-party libraries, I would like to execute all queries through LINQ, and not SQLConnect.

Most likely there will be one database and 4 tables in it. No complicated things like stored procedures or triggers are required from it. Storage only. All actions with it will be done directly through the program.

Considered as a variant of sqlite, but not sure if it has good LINQ support. And I decided to consult first. If you know a good way to make sqlite friends with LINQ, or if there is a better solution, I will be grateful for your help.

Answer 1, authority 100%

You can use Access as a local database if you do not plan to store a huge amount of data. (Access supports a database of no more than 2GB).

Office itself is not required, you can install the free Access Engine to work with Access .

Regarding LINQ , you can work with tables in memory by LINQ queries to DataTable , and then, when you are finished, transfer the data to Access .

Also, instead of working through DataTable , you can describe entities in the form of classes and use Dapper to write your own ORM.
Dapper has auto-mapping, which allows you to automatically reflect the data returned by queries to class fields.

If you don’t want to reinvent the wheel, you can look for a ready-made ORM. If I’m not mistaken, NHibernate supports Access out of the box .

Answer 2, authority 75%

Use regular serialization, in any format you like (XML, MS-NRBF, JSON, Protocol Buffers, or in general in “handwritten on the knee in 5 minutes”) – out of the box, this will fulfill all the conditions you set out above, including and about LINQ.

I’m quite serious. I don’t see much sense at this stage of the project, and with these conditions, using a full-fledged database – there will be more minuses than pluses.

Answer 3, authority 75%

Consider the option of using MS SQL Server Compact, this is, in fact, a single-user embedded version of SQL Server, there is full support for the Entity Framework.

Hasn’t been updated for several years, but it’s quite a full-fledged working product.

Connect a couple of libraries to your application and you can even run it from a flash drive.

Related links:

Microsoft SQL Server Compact Edition (Wikipedia)

Download Microsoft SQL Server Compact 4.0 (official)

Microsoft SQL Server Compact Edition (nuget)

EntityFramework.SqlServerCompact (nuget)

CompactView download (simple yet powerful MS SQL Server CE database viewer)