Model configuration: Make filtered indexes play nice with other providers

[roji]

#5817, #6866 add support for filtered indexes. While implementing support for this in PostgreSQL I realized that since the filter is an SQL query, in most (all?) cases it's going to be provider-specific due to SQL dialect differences. That is, in PostgreSQL we surround identifiers with double-quotes but in SQL Server we enclose in brackets, and so on.

This may mean that the feature should be moved out of Relational and made accessible only via ForSqlServerHasFilter(), ForNpgsqlHasFilter(), etc. Even if it's decided to keep this in relational, ForSqlServerHasFilter() is missing.

Cc @laskoviymishka who submitted the PR

[laskoviymishka]

But you can do simply .HasFilter("'Id' % 2 = 0")? This filter just pure string which will be concatenate to WHERE claueses. May be it worth to keep it into relational and add some provider specific stuff around it?

[roji]

@laskoviymishka, in your sample Id is a column, right? In that case, single-quoting it doesn't work in PostgreSQL. The correct way would be to double-quote identifiers, otherwise PostgreSQL automatically lower-cases them. So the PostgreSQL version for your sample would be HasFilter("\"Id\" % 2 = 0").

I'm not sure to what extent quoting/delimiting rules vary that much across providers... It's absolutely certain that we need to have ForSqlServerHasFilter(), ForNpgsqlHasFilter() and so on, the open question is whether a provider-neutral HasFilter() should still exist.

[laskoviymishka]

Sounds reasonable

[roji]

So @AndriySvyryd, @laskoviymishka and anyone else from the EF team, it would be good to have your opinion on whether HasFilter() should continue to exist. Once there's agreement to that I can submit a PR (or if you prefer to do it @laskoviymishka).

[ajcvickers]

@roji @laskoviymishka We discussed this in triage and decided that we do want to keep the relational API. There are two main reasons for this:

• We have relational concepts and APIs where there is good overlap for those concepts even if the exact strings might not match for all relational providers. For example, relational column types can be spcified even though some relational databases have very different types than others--for example, SQLite/SQL Server. Just because PostgreSQL does not match SQL Server in this case it doesn't mean that other pairs relational databases won't have overlap here.
• We expect most people to use a single database with their apps. In this case, we want the relational code to be as clean as possible. It should only be necessary to use provider-specific code when the concept is provider-specific or when multiple providers are being used and there is a specific need to have something different for each provider.

So we want to keep the nice short relational HasFilter(), but it is true that ForSqlServerHasFilter() and ForNpgsqlHasFilter() should be added. Do either of you want to send a PR for this?

[divega]

👍 to everything @ajcvickers said.

I would like to add that I don't discard the possibility of adding support for a more provider agnostic way of specifying index filters in the future, e.g. for this particular case we could leverage Expression<Func<T, bool>> and existing SQL translation capabilities that are already provider-specific (beware, I don't think it is as trivial as it sounds 😄). Even then, this would probably be something that would exist on top of the string-based version of the feature.

To follow the column type analogy, this would be similar to how we added the IsUnicode() API which allows influencing the column types to be used for strings in a more provider agnostic way than setting the store type directly.

[ajcvickers]

@roji @laskoviymishka Are either of you planning to send a PR to add ForSqlServerHasFilter?

[roji]

I'll do this in the next few days.

[amkhitaryan]

Was this removed? I can't find methods ForNpgsqlHasFilter(), ForSqlServerHasFilter(). Is there new alternative solving this?

[AndriySvyryd]

@amkhitaryan These were reverted back to a single method. Use Database.IsSqlServer() to add the corresponding logic. See Upgrading applications from previous versions to EF Core 2.0

[amkhitaryan]

@amkhitaryan These were reverted back to a single method. Use Database.IsSqlServer() to add the corresponding logic. See Upgrading applications from previous versions to EF Core 2.0

Thank you. I was trying to specify it in IEntityTypeConfiguration via EntityTypeBuilder, ended up with similar solution, I did specify provider specific configurations in DbContext via ModelBuilder and methods IsUsingPostgreSql() and IsUsingSqlServer()