DEV Community

loading...
Cover image for Introducing Index Attribute in Entity Framework Core

Introducing Index Attribute in Entity Framework Core

Foyzul Karim
・Updated on ・3 min read

The story of migration

I have developed some medium scale applications (medium scale = 20+ model/tables in a database) which is running smoothly on ASP.NET Web API and Entity Framework tech stack from 2016. I have been maintaining those projects perfectly fine.

But as the whole world is now crazy on MicroServices architecture, and the list of requirements of my clients are growing, so I thought, "Ok, let me migrate all of the old things to new and shiny boxes."

The Extension

As my repository architecture was awesome, I thought it would be very smooth and minimum code change needed to migrate on EF Core. But the first hit I had was, there was no Index attribute in EF Core. Ok, it wasn't here in 2018, I knew, but in 2020? It should be introduced already.
Since Index support is not here, and I am super lazy to write all of the plumbing code (ie. HasIndex, HasName for each property), I wrote a simple extension.

GitHub Snippet

public static ModelBuilder BuildIndex<T>(this ModelBuilder builder) where T : class
        {
            var propertyInfos = typeof(T).GetProperties().ToList().Where(x => x.GetCustomAttributes(typeof(IsIndex), false).Length > 0);
            foreach (var propertyInfo in propertyInfos)
            {
                //var propertyInfo = typeof(T).GetType().GetProperty("PhoneNumber");
                var parameterExpression = Expression.Parameter(typeof(T), "x");
                var memberExpression = Expression.PropertyOrField(parameterExpression, propertyInfo.Name);
                var lambda = Expression.Lambda<Func<T, object>>(memberExpression, parameterExpression);
                string indexName = $"IX_{propertyInfo.Name}";
                builder.Entity<T>().HasIndex(lambda).HasName(indexName);
            }

            return builder;
        }
Enter fullscreen mode Exit fullscreen mode

How do I use it?

In DbContext

In my actual model project, in the DbContext's OnModelCreating method, I just call the model like below.
GitHub Link

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.BuildIndex<ProductGroup>();
            modelBuilder.BuildIndex<ProductCategory>();
            modelBuilder.BuildIndex<ProductDetail>();
            base.OnModelCreating(modelBuilder);
        }
Enter fullscreen mode Exit fullscreen mode

In POCO

And in the model class, I decorate the the properties with the IsIndex attribute, like below.
GitHub Snippet

        [IsIndex]
        [Required]
        [Column(TypeName = "varchar(128)")]
        public string Name { get; set; }
Enter fullscreen mode Exit fullscreen mode

If we add the migration, we should see Entity Framework Core actually understands the attribute now and translate it to the migration file.

GitHub Snippet

 migrationBuilder.CreateIndex(
                name: "IX_Name",
                table: "ProductGroups",
                column: "Name");
Enter fullscreen mode Exit fullscreen mode

So, when I run update-database to apply the migration, I see the indexes are created into the table.

Alt Text

Real case scenario

Just imagine, my current monolithic system is having these tables. And each of the table is having at least 10/15 indexes.

Alt Text

If I were not to write this index attribute, I would have gone crazy just to type the HasIndex and HasName codes. πŸ˜‚πŸ€£

Future posts

I am converting the big monolith system into microservices architecture. The basic version of monolith system can be found in GitHub. Below are some repositories you might find interesting.

BizBook Server
BizBook AngularJS 1.5 Client
BizBook Server Core

So, in the journey of splitting the monolith to MicroServices, I extracted all of the common codes into BizBook.Common.Library repository.

You can also install the package through
https://www.nuget.org/packages/BizBook.Common.Library.EntityFrameworkCore

I will be very glad if you can give some feedback. πŸ™‚
If you find these helpful, you can buy me a coffee. I love coffee.

Buy Me A Coffee

HappyCoding

Discussion (5)

Collapse
j_sakamoto profile image
jsakamoto

Is this NuGet package is also helpful for some people like you?

Collapse
foyzulkarim profile image
Foyzul Karim Author

Hi @jsakmoto, your package is super awesome. I can't remember where I saw it, but you gave a humble warning to use it carefully, so I got scared. πŸ˜‚
Since my requirement was pretty simple, I thought I would write it by myself to get the full control.
But I am keeping a close eye in your project. Kudos πŸ™‚
And thanks for the comment, I am honored.

Collapse
j_sakamoto profile image
jsakamoto • Edited

I thought I would write it by myself to get the full control.

Yeah, I strongly agree with this decision.
I will sometimes choose the way to get full control, too. :)

Anyway, thank you for your reply. And, I think, you did the great work!

Collapse
moinulmithu_97 profile image
Moinul Islam

Just read the post. You are always awesomeπŸ‘

Collapse
j_sakamoto profile image
jsakamoto

[FYI]

PR #21012 was merged...! 😲

"Fix for 4050. IndexAttribute."