How to create an index in SQLite database using EF6 Code First?

[kiwiSteve]

I'm developing my first ever Visual Studio database application, in Visual Basic 2017 using EF 6.1.3 Code First with dotConnect for SQLite (5.9.912.0), and I'm having trouble getting it to create indexes for columns that are not primary or foreign keys.

I've unsuccessfully tried using data annotation in my entity class, e.g.:

Code: Select all

    <Required>
    <Index(IsUnique:=False)>
    <StringLength(FormulaFieldLength)>
    Public Property Formula As String

I've also tried adding the index as a column annotation using the Fluent API in a subclass of EntityTypeConfiguration:

Code: Select all

        Dim ndx = "IX_" & NameOf(Peptides) & "_" & NameOf(Peptide.Formula)
        With Me.Property(Function(p) p.Formula)
            .HasMaxLength(FormulaFieldLength)
            .IsRequired()
            .HasColumnAnnotation(IndexAnnotation.AnnotationName,
                                 New IndexAnnotation(New IndexAttribute(ndx) With {.IsUnique = False}))
        End With

When EF6 creates my SQLite database, it creates indexes for all the primary and foreign keys defined in my entity classes, but doesn't create any indexes for other columns (like the Formula column, as in the code above).

What an I doing wrong? I presume dotConnect for SQLite suppports Entity Framework's Code First methods for creating indexes, or is that not the case?

[Shalex]

The index must be created with both automatic and code-based Code-First Migrations scenarios (AutomaticMigrationsEnabled = true/false). If you are employing automatic migrations, please use MigrateDatabaseToLatestVersion in your code:

Code: Select all

    Database.SetInitializer<MainEntities>(new MigrateDatabaseToLatestVersion<MainEntities, Configuration>());
    var context = new MainEntities();
    context.Database.Initialize(false);

Be aware that EF6 doesn't support creating indices via the Code-First CreateDatabase() functionality.

Does this information help?

[kiwiSteve]

Thank you for your response. I wasn't aware that EF6 doesn't support creating indices via the Code-First CreateDatabase() functionality, so your reply and sample code give me a starting point to getting my database working correctly.

[kiwiSteve]

I have now enabled automatic migrations, and am trying to register the SQL generator using the SetSqlGenerator method (using the code in http://blog.devart.com/entity-framework ... #Generator as a model):

Code: Select all

Imports System.Data.Entity.Migrations
Imports Devart.Data.SQLite.Entity.Migrations

Namespace Migrations

    Friend NotInheritable Class HerbertDbMigrationConfiguration
        Inherits DbMigrationsConfiguration(Of HerbertDbContext)

        Public Sub New()
            AutomaticMigrationsEnabled = True

            SetSqlGenerator(SQLiteConnectionInfo.InvariantName,
                            New SQLiteEntityMigrationSqlGenerator())

        End Sub

        Protected Overrides Sub Seed(context As HerbertDbContext)
            '  This method will be called after migrating to the latest version.
        End Sub

    End Class

End Namespace

However, I've got an error message saying that SQLiteEntityMigrationSqlGenerator is ambiguous in the Devart.Data.SQLite.Entity.Migrations namespace. Can you suggest why, and how to fix this error?

[Shalex]

1. Are you registering the Devart EF6 provider via code or via *.config?
2. Please set a break point in your code, run it in the debug mode, navigate to Debug > Windows > Modules and specify the values of the following columns for loaded Devart.* assemblies: Name, Path, Version.

[kiwiSteve]

I found I had a reference to Devart.Data.SQLite.Entity.EFCore in my project as well as Devart.Data.SQLite.Entity.EF6, which I must have added when I first created the project thinking it was a "core" assembly needed for SQLite EF support. At that stage (being a complete EF newbie) I didn't realise EF Core is a separate product to EF6!

So, it's not surprising VS complained about ambiguous references to SQLiteEntityMigrationSqlGenerator. Having got rid of the incorrect assembly reference, I now have Code First migrations working and indices being correctly created. Thanks again for pointing me in the right direction!