How to create a compound index in Sqlite via Microsoft.EntityFrameworkCore.Sqlite?

Question

I’m trying to create a unique composite index, but I don’t know how to do it on Microsoft.EntityFrameworkCore.SQLite, I’m used to working only with the Doctrine and Hibernate and I’m totally lost.

This is my model class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;

namespace Test.Entity
{
    public class Project
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        private int id;
        [Required]
        private bool shared;
        [Required, MaxLength(64)]
        private String name;

        private Project parent;

        [ForeignKey("id")]
        private ICollection<Project> searchable;

        public bool Shared
        {
            get { return shared; }
            set { shared = value; }
        }

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        public Project Parent
        {
            get { return parent; }
            set { parent = value; }
        }

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        public ICollection<Project> Searchable
        {
            get { return searchable; }
            set { searchable = value; }
        }
    }
}

That’s my method in my context class

protected override void OnModelCreating(ModelBuilder modelBuilder) {
   modelBuilder.Entity<Project>().HasIndex(r => new { r.Name, r.Parent}).IsUnique();
}

Answer 1

I use a procedure little different from yours but the result should be the same:

 entityBuilder.HasIndex(c => new { c.AutorNome, c.Nome})
                .HasName("idx_Curso_Autor_Nome")
                .IsUnique();

Result in manager:

I see two possibilities for not being able to generate your index:

1. You are not running Migration Add-Migration IndiceComposto
2. You are using data annotation along with fluent api, would be better to do everything with fluent api. Note that in the documentation informs that it is not possible composite keys with data annotations. I know you are not generating the compound Indice this way but it is good to standardize.

Answering more specifically the question:

I think there is no possibility to inform a class as Indian.

Like the EF will know what exactly which field is wanting to index?

The Property 'xxx' cannot be Added to the Entity type 'xxx'