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

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
kiwiSteve
Posts: 5
Joined: Sat 10 Jun 2017 23:30

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

Post by kiwiSteve » Sun 11 Jun 2017 00:31

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
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Tue 13 Jun 2017 11:05

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
Posts: 5
Joined: Sat 10 Jun 2017 23:30

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

Post by kiwiSteve » Tue 13 Jun 2017 17:34

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
Posts: 5
Joined: Sat 10 Jun 2017 23:30

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

Post by kiwiSteve » Tue 13 Jun 2017 19:03

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
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Wed 14 Jun 2017 20:02

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
Posts: 5
Joined: Sat 10 Jun 2017 23:30

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

Post by kiwiSteve » Fri 16 Jun 2017 04:32

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!

Post Reply