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,
User contributions licensed under CC BY-SA 3.0