Index created on views do not get scaffolded

[shvmgpt116]

Bug description

I am trying to scaffold below schema using Oracle EFCore provider.

CREATE TABLE "TST"."TABLE_DEMO_1"
( "STRING_COL_1" VARCHAR2(12 BYTE),
"STRING_COL_2" VARCHAR2(1 BYTE)
);

CREATE MATERIALIZED VIEW "TST"."MAT_VIEW_DEMO_1" ("STRING_COL_1", "STRING_COL_2")
AS SELECT
STRING_COL_1, STRING_COL_2
FROM
TST.TABLE_DEMO_1;

CREATE INDEX "TST"."MAT_VIEW_DEMO_1_INDEX1" ON "TST"."MAT_VIEW_DEMO_1" ("STRING_COL_1");

CREATE INDEX "TST"."TABLE_DEMO_1_INDEX1" ON "TST"."TABLE_DEMO_1" ("STRING_COL_2");

The generated model has fluent API HasIndex created for the table but for materialized view, there is null instead of the index name 'MAT_VIEW_DEMO_1_INDEX1' which was created for the materialized view.

Below is the generated OnModelCreating method-

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .HasDefaultSchema("TST")
        .UseCollation("USING_NLS_COMP");

    modelBuilder.Entity<MatViewDemo1>(entity =>
    {
        entity
            .HasNoKey()
            .ToView("MAT_VIEW_DEMO_1");

        entity.HasIndex(e => e.StringCol1, null);

        entity.Property(e => e.StringCol1)
            .HasMaxLength(12)
            .IsUnicode(false)
            .HasColumnName("STRING_COL_1");
        entity.Property(e => e.StringCol2)
            .HasMaxLength(1)
            .IsUnicode(false)
            .HasColumnName("STRING_COL_2");
    });

    modelBuilder.Entity<TableDemo1>(entity =>
    {
        entity
            .HasNoKey()
            .ToTable("TABLE_DEMO_1");

        entity.HasIndex(e => e.StringCol2, "TABLE_DEMO_1_INDEX1");

        entity.Property(e => e.StringCol1)
            .HasMaxLength(12)
            .IsUnicode(false)
            .HasColumnName("STRING_COL_1");
        entity.Property(e => e.StringCol2)
            .HasMaxLength(1)
            .IsUnicode(false)
            .HasColumnName("STRING_COL_2");
    });

    OnModelCreatingPartial(modelBuilder);
}

The DatabaseModel object returned to the relational layer has the index name in the DatabaseView object (See SS below).

I am wandering why is index name info is missing in the generated model, even though Oracle provider returns that info to the relational layer/code.

======================================

A similar issue is noticed in EFCore sql server provider as well.
I used following schema to scaffold.

-- Step 1: Create a simple table
CREATE TABLE dbo.ProductSales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(100),
    SaleAmount DECIMAL(10, 2)
);

-- Insert some dummy data
INSERT INTO dbo.ProductSales (ProductName, SaleAmount) VALUES ('Laptop', 1000.00);
INSERT INTO dbo.ProductSales (ProductName, SaleAmount) VALUES ('Mouse', 25.00);
INSERT INTO dbo.ProductSales (ProductName, SaleAmount) VALUES ('Laptop', 1200.00);
INSERT INTO dbo.ProductSales (ProductName, SaleAmount) VALUES ('Keyboard', 45.00);
GO

-- Step 2: Create the View with SCHEMABINDING
-- Note: We must use two-part names (dbo.ProductSales), not just ProductSales
CREATE VIEW dbo.v_SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    ProductName,
    COUNT_BIG(*) AS RecordCount -- Required by SQL Server when grouping in indexed views
FROM 
    dbo.ProductSales
GROUP BY 
    ProductName;
GO

-- Step 3: Create the Unique Clustered Index on the View
-- This step physically creates the data structure for the view
CREATE UNIQUE CLUSTERED INDEX IX_v_SalesSummary_Product 
ON dbo.v_SalesSummary(ProductName);
GO

CREATE INDEX IX_ProductSales 
ON dbo.ProductSales(ProductName);
GO

The db has index created for table and view both.
But after the scaffolding, the generated model doesn't have index generated for the view. Below is the generated OnModelCreating for SqlServer EFCore provider.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductSale>(entity =>
    {
        entity.HasKey(e => e.SaleId).HasName("PK__ProductS__1EE3C41FB9813AE0");

        entity.HasIndex(e => e.ProductName, "IX_ProductSales");

        entity.Property(e => e.SaleId).HasColumnName("SaleID");
        entity.Property(e => e.ProductName)
            .HasMaxLength(100)
            .IsUnicode(false);
        entity.Property(e => e.SaleAmount).HasColumnType("decimal(10, 2)");
    });

    modelBuilder.Entity<VSalesSummary>(entity =>
    {
        entity
            .HasNoKey()
            .ToView("v_SalesSummary");

        entity.Property(e => e.ProductName)
            .HasMaxLength(100)
            .IsUnicode(false);
    });

    OnModelCreatingPartial(modelBuilder);
}

Your code

Please check the info above for the DB schema

Stack traces

Verbose output

EF Core version

10.0.1

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 10

Operating system

Windows 11

IDE

VS 2022

[shvmgpt116]

@ajcvickers @roji

[ErikEJ]

@shvmgpt116 The indexe definition are not used by EF Core duing runtime anyway - do you have errors or are blocked in any way?

[roji]

@shvmgpt116 the above seems to be signaling two unrelated issues:

1. On Oracle, scaffolded indexes on views don't have the database name scaffolded on them.
2. On SQL Server, indexes on views don't seem to be scaffolded at all.

In general, please open separate issues for separate problems.

I'll keep this issue open in the backlog to track scaffolding indexes on views for SQL Server.