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.

9
  • Have you tried instead of Include just create needed class via Select? Also update question with MetaMovie definition. Commented Apr 5, 2022 at 4: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. Commented Apr 5, 2022 at 9: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. Commented Apr 5, 2022 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<FuzzyMetaMovie> without dancing with migrations. Commented Apr 5, 2022 at 14:24
  • @SvyatoslavDanyliv Maybe you can look at those "related questions" about this project: stackoverflow.com/questions/71229093/… && stackoverflow.com/questions/71244785/…. In the latest one, there is a link to a striped version, but now outdated, but still relevant to this question. Commented Apr 5, 2022 at 14:28

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.