Skip to content

Support for expression indices #119

Open

Description

@roji

Could go along well with partial index support (#116). See open issue on possible support in EFCore itself: dotnet/efcore#3986.

Note previous issue when scaffolding a database that contained a partial index: #12

Activity

  1. added this to the milestone on Oct 27, 2016
  2. self-assigned this
    on Oct 27, 2016
  3. changed the title [-]Support expression indices[/-] [+]Support for expression indices[/+] on Oct 27, 2016
  4. roji commented on Nov 21, 2016

    @roji
    MemberAuthor

    It turns out PostgreSQL supports indices with multiple expressions, and even the mixing of columns and expressions on the same index...

    This makes it pretty impractical to model expression indices in EF Core: a built-in Index contains properties, and while we could use annotations to augment it, this becomes very messy with the mixing of both columns and annotations.

    Doesn't seem to be worth the effort, when it's easy to add raw SQL in migrations for creating these indices.

  5. removed this from the milestone on Nov 21, 2016
  6. crozone commented on Jun 2, 2017

    @crozone

    I'm trying to add an index on the lowercase of a text column, so that if I need to do case insensitive lookup on the column (which is not always), I can use .ToLower() == .ToLower() and have the query use the index.

    My first attempt was to do something like:
    builder.Entity<Something>(o => { o.HasIndex(s => s.SomeStringColumn.ToLower()); }

    But obviously this doesn't work. Is this use case totally off the table?

    I'm currently using migrationBuilder.Sql("CREATE UNIQUE INDEX \"IX_Something_SomeStringColumn_lower\" ON \"Something\" ((lower(\"SomeStringColumn\")));");

  7. roji commented on Jun 2, 2017

    @roji
    MemberAuthor

    @crozone you definitely can't do anything like that with HasIndex() - this is what this issue was originally about but I ended up dropping the idea because it didn't make much sense.

    Your second (current) approach is the right one - using raw SQL to define the index - is it not working for you?

  8. smitpatel commented on Jun 8, 2017

    @smitpatel

    @roji - If I am understanding correctly, at present there is no way to create index on arbitrary expressions other than using migrationBuilder.Sql() (in PostgreSQL)?

  9. roji commented on Jun 8, 2017

    @roji
    MemberAuthor

    @smitpatel that's right, I looked at this a while back but as I wrote above, PostgreSQL support for expression indices is very powerful/flexible, and so the effort of modeling everything seemed a bit too much considering you can just use raw sql.

    Are you guys considering some support for expression indices or something? I can revisit this if relevant.

  10. smitpatel commented on Jun 8, 2017

    @smitpatel

    We have tracking issue on EF side without a note from 2015 (I need to find out what we are going to do with that). I was looking at the issue from RevEng side mainly. Since EFCore can translate annotations while scaffolding now, PostgreSQL can map such indices to their fluent API if any through annotations.

  11. roji commented on Jun 9, 2017

    @roji
    MemberAuthor

    @smitpatel OK, thanks for the update. Scaffolding wasn't really the issue here, but rather the complexity of the fluent API that would be needed to express indices over any number of both columns and arbitrary expressions (and mixing them together). Not that it's not possible - it's just that the value of providing such an API didn't seem clear when the same can be done with raw SQL.

    Would definitely be interested in your input on this (I also have no idea regarding expression index capabilities in other databases etc.)

  12. roji commented on May 27, 2018

    @roji
    MemberAuthor

    @smitpatel, I'm revisiting this after a while, and considering adding support for a limited scenario, but which probably is the 90% case (index with a single expression and no columns). You mentioned an issue in EF Core about supporting expression index, can you fill me in on what you decided to do? If you guys have plans for some sort of expression index support, it's better for me to do it in a way that aligns with yours etc.

  13. 24 remaining items

  14. roji commented on Mar 23, 2025

    @roji
    MemberAuthor

    @bazarniy simply Use SQL directly in your migrations (docs).

  15. bazarniy commented on Mar 25, 2025

    @bazarniy

    @roji Example: I have computed column and index above on it. If I try to override simple efcore index with raw sql, it will be silently recreated to default in next migration when I change computed column generation expression (NET8). If I create index with raw sql and ef don't know about it, I can get errors in migrations later (when everybody forget that there are this index). So I try to find a way to create index above and make ef core not rollback it to default value

  16. roji commented on Mar 25, 2025

    @roji
    MemberAuthor

    If I create index with raw sql and ef don't know about it, I can get errors in migrations later (when everybody forget that there are this index)

    What kind of errors are you referring to? If the index is only managed via raw SQL, EF is completely unaware of it an no errors should occur.

  17. marked How to use Index Expressions #1486 as a duplicate of this issue on Apr 1, 2025
  18. bazarniy commented on Apr 23, 2025

    @bazarniy

    What kind of errors are you referring to? If the index is only managed via raw SQL, EF is completely unaware of it an no errors should occur.

    I have stored computed column and lower index on it. When I change computed column formula postgres remove and create new column with same name as I see. So this action removes my index too. If ef core don't know about this index it will be removed silently and not created again. If ef core know about index it will be reacreated. So with manual creation I can lose indexes and don't know about it until perfomance problems

  19. roji commented on Apr 23, 2025

    @roji
    MemberAuthor

    Yes, if you modify your index you have to manually manage that in migrations, just like when you originally create it (I'm still not sure what "errors in migrations later" you were referring to before).

    I'm not saying this is an ideal situation, but it is what it is at the moment.

  20. added theissue type on Jun 14, 2025
  21. roji commented on Jul 14, 2025

    @roji
    Author
  22. tebeco commented on Apr 12, 2026

    @tebeco

    I'm not super knowledgeable regarding EFcore and Specific driver implementation.
    We just stumbled on a similar need at work, for now done manually via migration at work

    Does this needs to be first implemented per DB or at EFCore level first ?

  23. crozone commented on Aug 10, 2026

    @crozone

    @tebeco Based on my own experiments attempting to implement this I think we need a version of TranslatingExpressionVisitor that is able to generate SQL using the pre-finalized relational model that is available at design time, instead of the final IModel that the MigrationSqlGenerator gets access to.

    I naively attempted to implement a homemade .HasFilter() extension method, which adds the index filter expression to the index as an annotation, and then have a IModelFinalizingConvention convert the expression into SQL string and call .SetFilter() on the IConventionIndex.

    Unfortunately it immediately became obvious why this has not yet been implemented at the EF level. As a matter of fact, none of the current built in EF extensions that are based on annotations can take an expression argument, beyond simply converting it into a property list, and for good reason. Only query filters do, but they exist purely at runtime.

    In order to generate the SQL from an expression using the existing infrastructure, you need an injected instance of IRelationalSqlTranslatingExpressionVisitorFactory from the internal service provider, to create an QueryableMethodTranslatingExpressionVisitor. Unfortunately, this requires a finalized IModel to exist, but that doesn't exist yet because the IModelFinalizingConvention (or even an NpgsqlAnnotationsProvider) is running in the process of building that finalized model.

    My attempt at the expression -> SQL translator:

    private string TranslateExpressionToSql(Expression expression)
    {
        IRelationalSqlTranslatingExpressionVisitorFactory visitorFactory = serviceProvider.GetRequiredService<IRelationalSqlTranslatingExpressionVisitorFactory>();
        IQueryCompilationContextFactory queryCompilationContextFactory = serviceProvider.GetRequiredService<IQueryCompilationContextFactory>();
        IQueryableMethodTranslatingExpressionVisitorFactory queryableMethodVisitorFactory = serviceProvider.GetRequiredService<IQueryableMethodTranslatingExpressionVisitorFactory>();
        IQuerySqlGeneratorFactory querySqlGeneratorFactory = serviceProvider.GetRequiredService<IQuerySqlGeneratorFactory>();
    
        // Create the query compilation context
        QueryCompilationContext queryCompilationContext = queryCompilationContextFactory.Create(async: false);
        QueryableMethodTranslatingExpressionVisitor queryableVisitor = queryableMethodVisitorFactory.Create(queryCompilationContext);
        RelationalSqlTranslatingExpressionVisitor scalarVisitor = visitorFactory.Create(
            queryCompilationContext,
            queryableVisitor
        );
    
        // Translate the Expression into a structured relational SqlExpression
        SqlExpression sqlExpression = scalarVisitor.Translate(expression) ?? throw new InvalidOperationException("Could not translate expression");
    
        // Generate the SQL
        QuerySqlGenerator sqlGenerator = querySqlGeneratorFactory.Create();
        IRelationalCommand command = sqlGenerator.GetCommand(sqlExpression);
    
        return command.CommandText;
    }

    The above method cannot work inside a IModelFinalizingConvention because IQueryableMethodTranslatingExpressionVisitorFactory cannot be resolved until an IModel is available.

    The translation also cannot happen later on within a derived custom NpgsqlMigrationsSqlGenerator, because in order for the expression to reach the SQL generator in an annotation value, it must be serialized. However, only primitive values can be serialized as annotation values, and expressions are certainly not serializable. In order to build correct SQL from an expression with the existing infrastructure, we need the IModel to be built, and we need access to the expression. Nowhere in the current model building -> migration pipeline allows for both of these preconditions to be met.

    It would potentially be possible to serialize the expression into an intermediate form that the NpgsqlMigrationsSqlGenerator could reason about and do the final generation, but as previously discussed this potentially hides certain changes from the model differ (like changing column names). IMHO it would be ideal to somehow generate the SQL before the model is finalized such that the annotations work the same way as all of the other annotations. To do that, we need to somehow get a QuerySqlGenerator instance that works inside a convention or annotation provider.

  24. roji commented on Aug 11, 2026

    @roji
    MemberAuthor

    Thanks for looking into this. One note...

    My hunch is telling me that some form of expression indexes exist across most databases; for example, SQL Server may not have a direct expression index feature, but IIRC one can create a virtual column and index that, and that speeds up even queries which don't reference that virtual column but instead use the expression directly (so in effect the same thing is achieved).

    Assuming this hunch is true, it would most likely be better to look at this as a general EF feature and implement the infrastructure support in EF, rather than treating it as a PostgreSQL-only feature.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Projects

No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions