Support expression (AKA functional) indexes
- 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
Assessment
This issue has not been assessed yet.