Full Text Search on ValueObject in same Table generates incorrect query

0

I have an existing table using a valueobject placed into the same table. A full text index has been placed on the fields in the table from the valueobject, but when using EF.Function.Contains EF generates a query that fails with Sql Exception.

ValueObject

public class Address
{
        [Required]
        [MaxLength(50)]
        public string BuildingNumber { get; private set; }

        [Required]
        [MaxLength(100)]
        public string Street { get; private set; }

        [MaxLength(100)] public string? Locality { get; private set; }

        [MaxLength(50)] public string? City { get; private set; }

        [MaxLength(50)] public string? County { get; private set; }

        [Required] [MaxLength(8)] public string Postcode { get; private set; }
}

Main Entity

public class Job
{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; protected set; }

        [Required] 
        public Address Address { get; private set; }
}

DbContext Configuration

 modelBuilder.Entity<Job>().OwnsOne(j => j.Address);
            modelBuilder.Entity<Job>().OwnsOne(j => j.Address).Property(b => b.BuildingNumber).IsRequired()
                .HasColumnName("BuildingName");
            modelBuilder.Entity<Job>().OwnsOne(j => j.Address).Property(b => b.Street).IsRequired()
                .HasColumnName("Street");
            modelBuilder.Entity<Job>().OwnsOne(j => j.Address).Property(b => b.Locality).HasColumnName("Locality");
            modelBuilder.Entity<Job>().OwnsOne(j => j.Address).Property(b => b.City).HasColumnName("City");
            modelBuilder.Entity<Job>().OwnsOne(j => j.Address).Property(b => b.County).HasColumnName("County");
            modelBuilder.Entity<Job>().OwnsOne(j => j.Address).Property(p => p.Postcode).IsRequired()
                .HasColumnName("Postcode");

Query in Code

_dbContext.Set<Job>().Where(
   EF.Functions.Contains(j.Address.Street, "valley")).Count();

Generated SQL

SELECT Count(*)
FROM [Job].[Jobs] AS [j]
LEFT JOIN (
    SELECT [j0].[Id], [j0].[BuildingName], [j0].[City], [j0].[County], [j0].[Locality], [j0].[Postcode], [j0].[Street], [j1].[Id] AS [Id0]
    FROM [Job].[Jobs] AS [j0]
    INNER JOIN [Job].[Jobs] AS [j1] ON [j0].[Id] = [j1].[Id]
    WHERE [j0].[Street] IS NOT NULL AND ([j0].[Postcode] IS NOT NULL AND [j0].[BuildingName] IS NOT NULL)
) AS [t] ON [j].[Id] = [t].[Id]
WHERE CONTAINS([t].[Street], 'valley')

Error generated

An exception occurred while iterating over the results of a query for context type '"JobsOnTrack.Data.Jobs.JobsContext"'."
""Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot use a CONTAINS or FREETEXT predicate on column 'Street' because it is not full-text indexed.

Clearly SQL cannot determine the use of table from the inner query [t], if I simply change to the following the query runs correctly and the full text index is working.

SELECT Count(*)
FROM [Job].[Jobs] AS [j]
LEFT JOIN (
    SELECT [j0].[Id], [j0].[BuildingName], [j0].[City], [j0].[County], [j0].[Locality], [j0].[Postcode], [j0].[Street], [j1].[Id] AS [Id0]
    FROM [Job].[Jobs] AS [j0]
    INNER JOIN [Job].[Jobs] AS [j1] ON [j0].[Id] = [j1].[Id]
    WHERE [j0].[Street] IS NOT NULL AND ([j0].[Postcode] IS NOT NULL AND [j0].[BuildingName] IS NOT NULL)
) AS [t] ON [j].[Id] = [t].[Id]
WHERE CONTAINS([j].[Street], 'valley')

I am not sure why EF generates the left join to the same table for the fields of the valueobject.

Hopefully someone can shed some light on how to implement this correctly.

thanks,

c#
entity-framework-core
asked on Stack Overflow Mar 11, 2020 by matto56 • edited Mar 11, 2020 by matto56

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0