Translate more complex lambda expressions to relational index definitions

[mqudsi]

Many RDBS implementations support custom indices, which can be used to populate the index for any given row with an arbitrary expression (hopefully dependent on the row, but not necessarily so!) rather than the value of one or columns.

e.g. in SQLite:

/* assume the collation handles all inputs correctly */
CREATE INDEX foo_bar_nocase Foo(LOWER(Bar)); 

/* this will use the pre-computed index */
SELECT * FROM Foo WHERE LOWER(Foo.Bar) = LOWER(@bar);

1) I think the EF Core restriction (as of 2.2, at any rate) that the expression provided for Entity<Foo>.HasIndex(...) must be a simple property access should be relaxed; in particular, calls to db-provided scalar functions in the expression tree should, at minimum, be accepted.

2) C# doesn't have syntax for fully anonymous types mapping to the result of a function.

e.g. While the following based off the existing syntax works fine (in theory, assuming number 1 above):

builder.Entity<Person>()
    .HasIndex(p => EF.Functions.Lower(p.FirstName));

this syntax does not:

builder.Entity<Person>()
    .HasIndex(p => new { EF.Functions.Lower(p.FirstName), EF.Functions.Lower(p.LastName) });

Which means that there's no current overload of HasIndex() that would support a composite key where one or more of its components include a [DbFunction] call.

I propose adding a params overload to HasIndex() that takes n expressions each returning a single value, each of which would form a part of the composite key in the order they are provided (like the current params overload for property names works).

Further technical details

EF Core version: 2.2

[ajcvickers]

Triage: Putting this on the backlog to consider translating more than just simple property expressions into index definitions. Also, created #16604, also on the backlog, for specifying the index with an opaque string.

[PejmanNik]

Hi, any news on this?

[ajcvickers]

This issue is in the Backlog milestone. This means that it is not planned for the next release. We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.