Entity Framework Code First, FullTextIndex & Inheritance

Question

I'm currently working, with my team, on a project where we have a large SQL Server 2014 database with a lot of data. To increase performances, we chose to use FullTextIndex with EntityFramework by following this tutorial. We now have a problem for the table Customer because of inheritance (we think so, it may be something else). Here is the model (simplified) :

public abstract class Person // In db : dbo.People
{
    public int Id { get; set }
    public string LastName { get; set; }
    // Other properties
}

public class Customer : Person // In db : dbo.People_Customer
{
    // Inherited properties from Person
}

public class Mission // In db : dbo.Missions
{
    public int CustomerIdPerson { get; set; }
    public virtual Customer Customer { get; set; }
}

When I try to search missions through customer via the following query :

context.Missions.Where(m => m.Customer.LastName.Contains("foo")).ToList();

I have this error (raised by SQL Server) : Cannot use a CONTAINS or FREETEXT predicate on column 'LastName' because it is not full-text indexed.

Here is the SQL generated by EF (I just replaced the variable by the actual value of the SQL parameter).

SELECT 
[Limit3].[IdJourney] AS [IdJourney]
FROM ( SELECT TOP (2000) 
    [Project4].[IdJourney] AS [IdJourney]
    FROM    (SELECT 
        [Project2].[IdJourney] AS [IdJourney], 
        [Project2].[SuppressionDate] AS [SuppressionDate], 
        [Project2].[State] AS [State], 
        [Project2].[TripIdTrip] AS [TripIdTrip], 
        [Project2].[IdTrip] AS [IdTrip], 
        [Project2].[SuppressionDate1] AS [SuppressionDate1], 
        [Project2].[TripSetIdTripSet] AS [TripSetIdTripSet], 
        [Project2].[C1] AS [C1], 
        (SELECT TOP (1) 
            [Extent6].[PlannedDate] AS [PlannedDate]
            FROM  [dbo].[PlannedElements_PlannedBusinessFleetElement] AS [Extent5]
            INNER JOIN [dbo].[PlannedElements] AS [Extent6] ON [Extent5].[IdPlannedElement] = [Extent6].[IdPlannedElement]
            WHERE (0 = [Extent5].[TypeOfBusinessFleetElement]) AND ([Project2].[IdJourney] = [Extent5].[JourneyIdJourney])) AS [C2]
        FROM ( SELECT 
            [Extent1].[IdJourney] AS [IdJourney], 
            [Extent1].[SuppressionDate] AS [SuppressionDate], 
            [Extent1].[State] AS [State], 
            [Extent1].[TripIdTrip] AS [TripIdTrip], 
            [Extent2].[IdTrip] AS [IdTrip], 
            [Extent2].[SuppressionDate] AS [SuppressionDate1], 
            [Extent2].[TripSetIdTripSet] AS [TripSetIdTripSet], 
            (SELECT TOP (1) 
                [Extent4].[PlannedDate] AS [PlannedDate]
                FROM  [dbo].[PlannedElements_PlannedBusinessFleetElement] AS [Extent3]
                INNER JOIN [dbo].[PlannedElements] AS [Extent4] ON [Extent3].[IdPlannedElement] = [Extent4].[IdPlannedElement]
                WHERE (0 = [Extent3].[TypeOfBusinessFleetElement]) AND ([Extent1].[IdJourney] = [Extent3].[JourneyIdJourney])) AS [C1]
            FROM  [dbo].[Journeys] AS [Extent1]
            INNER JOIN [dbo].[Trips] AS [Extent2] ON [Extent1].[TripIdTrip] = [Extent2].[IdTrip]
        )  AS [Project2] ) AS [Project4]
    LEFT OUTER JOIN [dbo].[TripSets] AS [Extent7] ON [Project4].[TripSetIdTripSet] = [Extent7].[IdTripSet]
    LEFT OUTER JOIN  (SELECT [Extent8].[IdPerson] AS [IdPerson1], [Extent8].[LastName] AS [LastName]
        FROM  [dbo].[Persons] AS [Extent8]
        INNER JOIN [dbo].[Persons_Customer] AS [Extent9] ON [Extent8].[IdPerson] = [Extent9].[IdPerson] ) AS [Join5] ON [Extent7].[CustomerIdPerson] = [Join5].[IdPerson1]
    WHERE (CONTAINS([Join5].[LastName], '"foo*"'))
)  AS [Limit3]

I'm sure the column is full-text indexed. I've rebuild multiple times the calalog and the index. For other tables that are not inherited, the FullTextSearch works fine...

I'm out of ideas... Thanks for your help. :)

Answer 1

Probably the problem is related to the fact that CONTAINS access to a Join and not to a table so SQL Server does not like it. I usually avoid to inherit on classes written on the DB (scared about EF behaviour :) ).

Anyway you could avoid to add the [myAlias] in the piece of
CONTAINS([myAlias].[LastName], '"foo*"')
query. Probably sometimes you need to insert [myAlias] so you could change the code in the FtsInterceptor class. You could add 1 more "tag"
private const string FullTextPrefixWithoutAlias = "-FTSPREFIXNOALIAS-";
and then call a different RewriteFullTextQuery if you specify the FTSPREFIXNOALIAS (or better add a parameter to RewriteFullTextQuery to specify the behaviour). The only difference in RewriteFullTextQuery is that in the case you don't want the alias you need to use
string.Format(@"contains([$2], @{0})",parameter.ParameterName));
instead of
string.Format(@"contains([$1].[$2], @{0})",parameter.ParameterName));

In the same way you can also add more control to the FtsInterceptor adding more "tags"...