You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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\")));");
@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?
@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)?
@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.
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.
@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.)
@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.
@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
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
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.
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 ?
@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:
privatestringTranslateExpressionToSql(Expressionexpression){IRelationalSqlTranslatingExpressionVisitorFactoryvisitorFactory=serviceProvider.GetRequiredService<IRelationalSqlTranslatingExpressionVisitorFactory>();IQueryCompilationContextFactoryqueryCompilationContextFactory=serviceProvider.GetRequiredService<IQueryCompilationContextFactory>();IQueryableMethodTranslatingExpressionVisitorFactoryqueryableMethodVisitorFactory=serviceProvider.GetRequiredService<IQueryableMethodTranslatingExpressionVisitorFactory>();IQuerySqlGeneratorFactoryquerySqlGeneratorFactory=serviceProvider.GetRequiredService<IQuerySqlGeneratorFactory>();// Create the query compilation contextQueryCompilationContextqueryCompilationContext=queryCompilationContextFactory.Create(async:false);QueryableMethodTranslatingExpressionVisitorqueryableVisitor=queryableMethodVisitorFactory.Create(queryCompilationContext);RelationalSqlTranslatingExpressionVisitorscalarVisitor=visitorFactory.Create(queryCompilationContext,queryableVisitor);// Translate the Expression into a structured relational SqlExpressionSqlExpressionsqlExpression=scalarVisitor.Translate(expression)??thrownewInvalidOperationException("Could not translate expression");// Generate the SQLQuerySqlGeneratorsqlGenerator=querySqlGeneratorFactory.Create();IRelationalCommandcommand=sqlGenerator.GetCommand(sqlExpression);returncommand.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.
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.
Activity
[-]Support expression indices[/-][+]Support for expression indices[/+]roji commentedon Nov 21, 2016
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.
crozone commentedon Jun 2, 2017
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\")));");roji commentedon Jun 2, 2017
@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?
smitpatel commentedon Jun 8, 2017
@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)?roji commentedon Jun 8, 2017
@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.
smitpatel commentedon Jun 8, 2017
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.
roji commentedon Jun 9, 2017
@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.)
roji commentedon May 27, 2018
@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.
24 remaining items
roji commentedon Mar 23, 2025
@bazarniy simply Use SQL directly in your migrations (docs).
bazarniy commentedon Mar 25, 2025
@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
roji commentedon Mar 25, 2025
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.
bazarniy commentedon Apr 23, 2025
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
roji commentedon Apr 23, 2025
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.
roji commentedon Jul 14, 2025
tebeco commentedon Apr 12, 2026
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 ?
crozone commentedon Aug 10, 2026
@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
IModelthat theMigrationSqlGeneratorgets 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 aIModelFinalizingConventionconvert the expression into SQL string and call.SetFilter()on theIConventionIndex.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
IRelationalSqlTranslatingExpressionVisitorFactoryfrom the internal service provider, to create anQueryableMethodTranslatingExpressionVisitor. Unfortunately, this requires a finalizedIModelto exist, but that doesn't exist yet because theIModelFinalizingConvention(or even anNpgsqlAnnotationsProvider) is running in the process of building that finalized model.My attempt at the expression -> SQL translator:
The above method cannot work inside a
IModelFinalizingConventionbecauseIQueryableMethodTranslatingExpressionVisitorFactorycannot be resolved until anIModelis 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 theIModelto 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
NpgsqlMigrationsSqlGeneratorcould 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 aQuerySqlGeneratorinstance that works inside a convention or annotation provider.roji commentedon Aug 11, 2026
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.