c# - Using EF Core HasQueryFilter in entity configuration to map nullable db columns to non-nullable properties?

Question

The Setup:

In an MS SQL Server database (note: managed by an external provider, we cannot change the schema) we have a table column that is nullable. At the same time, in our entity model, we deliberately specified that the property mapped to this column is not nullable (because we want to completely ignore all null cases for this property and also do not want to do any property conversion in our code).

Naturally, at runtime when EF Core tries to map a nullable data to a non nullable property this would cause an exception at the time of query execution.

Therefore we thought, we could use EF Core's HasQueryFilter, which "specifies a LINQ predicate expression that will automatically be applied to any queries targeting this entity type". The idea is that by using HasQueryFilter, we can ignore all cases where the property mapped to this column is null. This way, at the time of query execution, it is guaranteed that all rows with null values for this column are stripped from the result set before EF Core does the mapping.

Here is the example entity configuration:

namespace Foo.Bar.Baz
{
  public class MyEntity : IEntityTypeConfiguration<MyEntity>
  {
    public int Id { get; set; }
    public int Foo { get; set; }

    public void Configure(EntityTypeBuilder<MyEntity> builder)
    {
      builder.ToTable("myTable");
      builder.HasKey(e => e.Id);
      builder.HasQueryFilter(e =>
        e.Foo != null // <-- ignoring any datasets with Foo being null
      );
    }
  }
}

The problem:

When executing a query for this entity via e.g. ...

DbContext.Set<MyEntity>().ToList();

...the .HasQueryFilter is executed but the e.Foo != null part seems to be be removed from the WHERE part of the SQL query as EF Core's optimization mechanism thinks it is an expression "that is always true".

The Question:

Any ideas how to force EF Core to execute the filter part of the .HasQueryFilter or how to otherwise configure the entity to achieve what we described above?

P.S. Please note that we know that we could add another service layer that does another mapping or we could introduce another property to correctly map the column. But we want to avoid this and transparently handle this case via the entity type builder configuration.

Answer 1

Looks like EF will optimize the query filter to remove any not null check for required properties. If you remove the [Required] attribute from the property and use just the query filter it should work. Or leave the [Required] attribute so you can use it at runtime, but override it in the model configuration.

builder.Property(e => e.Foo).IsRequired(false);