0

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.

Master DJon
  • 1,899
  • 2
  • 19
  • 30
  • Have you tried instead of `Include` just create needed class via `Select`? Also update question with `MetaMovie` definition. – Svyatoslav Danyliv Apr 05 '22 at 04:51
  • @SvyatoslavDanyliv I probably found my solution and all my requirements are met. Though, to be sure, I'll finish testing it today and give you news. – Master DJon Apr 05 '22 at 09:51
  • @SvyatoslavDanyliv As I said, I found my ideal solution. If you are interested to take a look. BTW, I hope you are doing well due to the "political" context. – Master DJon Apr 05 '22 at 14:21
  • Better post model as I mentioned, I will post my solution. I do not see any reason of those tricks when you can define extension method which returns `IQueryable` without dancing with migrations. – Svyatoslav Danyliv Apr 05 '22 at 14:24
  • @SvyatoslavDanyliv Maybe you can look at those "related questions" about this project: https://stackoverflow.com/questions/71229093/sqlite-not-existing-column-in-table-but-may-be-generated-added-from-some-queri/71753488#71753488 && https://stackoverflow.com/questions/71244785/sqlite-with-spellfix1-extension-used-with-entity-framework-core-crash-on-second. In the latest one, there is a link to a striped version, but now outdated, but still relevant to this question. – Master DJon Apr 05 '22 at 14:28
  • @SvyatoslavDanyliv Though, I am intrigued about your extension method solution. – Master DJon Apr 05 '22 at 14:30
  • Show at least `fuzzyQuery`. Everything too abstract. – Svyatoslav Danyliv Apr 05 '22 at 15:13
  • @SvyatoslavDanyliv Query added in the question. – Master DJon Apr 05 '22 at 15:21
  • @SvyatoslavDanyliv I am still waiting about your extension method solution, if you still want to write one. – Master DJon Apr 11 '22 at 22:12

1 Answers1

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.

Master DJon
  • 1,899
  • 2
  • 19
  • 30