Suggestion - allow HasGeneratedTsVectorColumn to reference navigation properties in its LINQ expression in order to support table splitting

[benstevens48]

This is a request to support the scenario described in this issue - https://github.com/dotnet/efcore/issues/26423

The setup is that the model uses table splitting and we want to perform a text search which covers fields in both the 'core' part of the table and the 'extended' part of the table. So we try something like this.

namespace Test.DbModels {
    [Table("Products")]
    public class Product {
        [Key]
        [Column("Id")]
        public Guid Id { get; set; }
        [Required]
        [Column("DisplayName")]
        public string DisplayName { get; set; }

        public ProductExtraDetail ExtraDetail { get; set; }
    }

    [Table("Products")]
    public class ProductExtraDetail {
        [Key]
        [Column("Id")]
        public Guid Id { get; set; }
        [Column("Description")]
        public string Description { get; set; }
     
        [Column("FullSearchVector")]
        public NpgsqlTsVector FullSearchVector { get; set; } //A generated column for text search

        public Product Parent { get; set; }
    }

    public class ProductEntityTypeConfiguration : IEntityTypeConfiguration<Product> {
        public void Configure(EntityTypeBuilder<Product> builder) {
            builder.HasOne(item => item.ExtraDetail).WithOne(item => item.Parent).HasForeignKey<ProductExtraDetail>(item => item.Id);
        }
    }

    public class ProductExtraDetailEntityTypeConfiguration : IEntityTypeConfiguration<ProductExtraDetail> {
        public void Configure(EntityTypeBuilder<ProductExtraDetail> builder) {
            builder.HasGeneratedTsVectorColumn(item => item.FullSearchVector, "english", item => new { item.Parent.DisplayName, item.Description }).HasIndex(item => item.FullSearchVector).HasMethod("GIN");
        }
    }
}

Note that in the line

builder.HasGeneratedTsVectorColumn(item => item.FullSearchVector, "english", item => new { item.Parent.DisplayName, item.Description }).HasIndex(item => item.FullSearchVector).HasMethod("GIN");

we try to reference the DisplayName property which is actually a property of the core Product model hence we try to use a navigation property. However, it doesn't work. The error is

The expression 'item => new <>f__AnonymousType0`2(DisplayName = item.Parent.DisplayName, Description = item.Description)' is not a valid member access expression. The expression should represent a simple property or field access: 't => t.MyProperty'. When specifying multiple properties or fields, use an anonymous type: 't => new { t.MyProperty, t.MyField }'. (Parameter 'propertyAccessExpression')

It would be nice it this could be made to work. Obviously it only make sense when the navigation property refers to the same table though. Not sure if that's difficult to enforce or whether you would just not validate that.

There is a workaround currently which is just to use HasComputedColumnSql instead of HasGeneratedTsVectorColumn, like this.

public void Configure(EntityTypeBuilder<ProductExtraDetailDbItem> builder) {
            builder.Property(item => item.FullSearchVector).HasComputedColumnSql("to_tsvector('english', coalesce(\"DisplayName\", '') || ' ' || coalesce(\"Description\", ''))", true);
            builder.HasIndex(item => item.FullSearchVector).HasMethod("GIN");
        }

[roji]

Note that as discussed in dotnet/efcore#26423, it's very easy to work around this by specifying HasComputedSql - HasGeneratedtsVectorColumn is just thin sugar above that.

In any case, put this in our backlog for now.