Support for expression indices

[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

[roji]

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]

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]

@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]

@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]

@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]

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.