[C#] Enable index in SQLite (Code First)

We should specify which column would be indexed in the function 'OnModelCreating'

        protected override void OnModelCreating( ModelBuilder modelBuilder )
        {
            modelBuilder.Entity().HasIndex(b => new { b.Time });
            modelBuilder.Entity().HasIndex(b => new { b.CarAppraisalLicense });
            modelBuilder.Entity().HasIndex(b => new { b._CreateTime});
            modelBuilder.Entity().HasIndex(b => new { b._ModifiedTime });

            modelBuilder.Entity().HasIndex(b => new { b.carPlateModify});
            modelBuilder.Entity().HasIndex(b => new { b.licenseRfid});
            modelBuilder.Entity().HasIndex(b => new { b.carRfid});
            modelBuilder.Entity().HasIndex(b => new { b.updateTime });

            modelBuilder.Entity().HasIndex(b => new { b.licenseRfid });
            modelBuilder.Entity().HasIndex(b => new { b.licenseNo});
            modelBuilder.Entity().HasIndex(b => new { b.updateTime});
        }

Then do EF migration, downgrade/upgrade codes will be generated autometically.

    public partial class CreateIndex : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs_CarAppraisalLicense",
                table: "VerifyLogs",
                column: "CarAppraisalLicense");

            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs_Time",
                table: "VerifyLogs",
                column: "Time");

            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs__CreateTime",
                table: "VerifyLogs",
                column: "_CreateTime");

            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs__ModifiedTime",
                table: "VerifyLogs",
                column: "_ModifiedTime");

            migrationBuilder.CreateIndex(
                name: "IX_DriverLicenses_licenseNo",
                table: "DriverLicenses",
                column: "licenseNo");

            migrationBuilder.CreateIndex(
                name: "IX_DriverLicenses_licenseRfid",
                table: "DriverLicenses",
                column: "licenseRfid");

            migrationBuilder.CreateIndex(
                name: "IX_DriverLicenses_updateTime",
                table: "DriverLicenses",
                column: "updateTime");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_carPlateModify",
                table: "CarLicenses",
                column: "carPlateModify");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_carRfid",
                table: "CarLicenses",
                column: "carRfid");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_licenseRfid",
                table: "CarLicenses",
                column: "licenseRfid");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_updateTime",
                table: "CarLicenses",
                column: "updateTime");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs_CarAppraisalLicense",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs_Time",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs__CreateTime",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs__ModifiedTime",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_DriverLicenses_licenseNo",
                table: "DriverLicenses");

            migrationBuilder.DropIndex(
                name: "IX_DriverLicenses_licenseRfid",
                table: "DriverLicenses");

            migrationBuilder.DropIndex(
                name: "IX_DriverLicenses_updateTime",
                table: "DriverLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_carPlateModify",
                table: "CarLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_carRfid",
                table: "CarLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_licenseRfid",
                table: "CarLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_updateTime",
                table: "CarLicenses");
        }
    }

After creating the index, we can reduce 1/10 DB query time on average.

[C#] DB data model for MySQL (Code First from database)

Create DB data model from MySQL (code first)

1. Install NuGet packages 'MySql.Data' v.8.0.16 and 'MySql.Data.Entity' v. 6.10.8
2. Install MySQL connector Net 6.10.8
3. INstall MySQL for Visual Studio 1.2.8
4. Add Entity Data Model (Code First from database)
5. New Connection (MySQL Data Provider)
6. Modify the file 'App.config' for DB connection string

<connectionStrings>
<add name="MySqlModel" connectionString="server=redmine2.gorilla-technology.com;user id=root;password=linuxyes;persistsecurityinfo=True;database=redmine" providerName="MySql.Data.MySqlClient" />
<add name="Default" connectionString="host=192.168.60.162;port=5432;database=redminedb;user id=redmine;password=MYSQL-TEL#53519427" providerName="Npgsql" />
</connectionStrings>

All needed works are done and then DB operations are ready to go.

using( var context = new MySqlModel() )
using( var transaction = context.Database.BeginTransaction() )
{
...
}