Using Include on FromRawSql query

Question

Context

I have two classes: MetaMovie and FuzzyMetaMovie. The second one is a subclass of the first one. The parent class contains a foreign relation to a list of MetaTitles called Titles.

FuzzyMetaMovie exists only to have an extra parameter that can be filled using a raw query.

FuzzyMetaMovie doesn't have a table linked to it because the result is gotten using a raw query on MetaMovies table and spellfix1 SQLite extension.

Problem

db.MetaMovies.First() gives me the first entry and the property Titles is filled.

db.FuzzyMetaMovies.FromSqlRaw<FuzzyMetaMovie>(fuzzyQuery).First() gives me the first entry, but the property Titlesis NOT filled.

Added, for a commenter, the fuzzyQuery variable:

var fuzzyQuery = @"
SELECT '" + nameof(FuzzyMetaMovie) + @"' AS Discriminator, mm.*, fuzzyTitles.distance FROM MetaMovies AS mm,  
(
    SELECT mt.MetaMovieId, MIN(fuzzyWords.distance) AS Distance FROM MetaTitles AS mt, 
        (SELECT DISTINCT word, distance FROM MetaTitlesFuzzy WHERE word MATCH '" + title.Replace("'", "''") + @"' AND distance < 500) AS fuzzyWords
    WHERE mt.Title = fuzzyWords.word
    GROUP BY mt.MetaMovieId
) AS fuzzyTitles
WHERE mm.Id = fuzzyTitles.MetaMovieId";

Attempts

I tried to use Include, but it gives me an exception.

I tried to use Include with casting to MetaMovie, but it gives me an exception.

I tried both above by adding ... INNER JOIN MetaTitles AS mt ON mt.MetaMovieId = fuzzyTitles.MetaMovieId to my raw query.

Not wanted solution if possible

Looping through all the results after calling ToList or else and filling by hand the property Titles.

Answer 1

I found a solution that meets all my requirements:

Having a subclass that...:

• is not creating a new table
• is not included in the migration
• includes the foreign table of its parent

Previously the class FuzzyMetaMovie...

• had the attribute [KeyLess]
• was excluded from migration using modelBuilder.Entity<FuzzyMetaMovie>().ToTable(nameof(FuzzyMetaMovie), t => t.ExcludeFromMigrations()); in the DBContent class

Now...

• I removed the [KeyLess] attribute
• I replaced the line to exclude from migration by modelBuilder.Entity<FuzzyMetaMovie>().ToTable("MetaMovies");
• I added a column in the table MetaMovies called Discriminator and filled it with MetaMovie
• When using the raw SQL, I ensure the first column is 'FuzzyMetaMovie' AS Discrimator so I can still use MetaMovies.* afterward in case I add new columns.

Having all those changes, I see now the property Titles filled either using a "normal" query that transposes directly to IQueryable<MetaMovie> or using a raw query that transposes to IQueryable<MetaMovie> but containing FuzzyMetaMovie entries.