[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});
}

C#

Copy

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");
    }
}

C#

Copy

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