dotnet / dotnet/efcore

Support expression (AKA functional) indexes

Open
#28,360 18 comments 50 reactions 0 assignees View on GitHub
area-migrations customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

Most databases support including arbitrary expressions in indexes, instead of just columns:

```sql
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
```

This allows filtering by arbitrary expressions to be speeded up at the cost of more expensive updates. This is a migration-only feature, so users can do this relatively easily on their own today, via raw SQL.

Note that a composite index can mix together both regular columns and expressions - this would likely make the modelling API tricky. We could also support only single-expression indexes to start with, this would likely cover most usages.

Cross-database support:

* [PostgreSQL](https://www.postgresql.org/docs/current/indexes-expressional.html)
* [MySQL](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts) (since 8.0.13)
* [SQLite](https://www.sqlite.org/expridx.html)
* [Oracle](https://www.oracletutorial.com/oracle-index/oracle-function-based-index/)
* MariaDB: apparently no support ([kb](https://mariadb.com/kb/en/functional-indexes/)).
* SQL Server: not supported.

Where not supported (SQL Server, MariaDB), it's possible to define an index over a stored generated/computed column to get a similar effect. If we really want to, the provider could automatically set that up under the hood (with a shadow column or property), though that could get a bit tricky.

Previously discussed in #3986, https://github.com/npgsql/efcore.pg/issues/119, #18382.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.