EF Core N+1 Problem

EF Core nie radzi sobie z przetłumaczeniem czegoś takiego:

_db.Reviews
    .Select(review => new ReviewModel
    {
        Id = review.Id,
        CustomerId = review.CustomerId,
        CustomerFirstName = review.Customer.FirstName,
        Stars = review.Stars,
        Content = review.Content,
        CreatedAt = review.CreatedAt,
        PositiveOpinionCount = review.Opinions.Count(o => o.IsReviewHelpful),
        NegativeOpinionCount = review.Opinions.Count(o => !o.IsReviewHelpful),
        CustomerOpinion = review.Opinions
            .Where(o => o.CustomerId == currentCustomerId)
            .Select(o => new CustomerOpinionModel {IsReviewHelpful = o.IsReviewHelpful})
            .FirstOrDefault()
    })

EF Core 3 rzuca wyjątek, bo nie może tego wysłać w jednym zapytaniu. EF Core 2 co prawda nie rzuca wyjątku, ale wysyła n+1 zapytań (dla n recenzji n razy wysyła zapytanie o opinię klienta). Jak sobie z tym poradzić? Można pobrać opinie osobno, ale wtedy w każdym z 2 zapytań czytamy tabelkę Opinions. Do głowy przychodzi mi jedynie utworzenie widoku z recenzji - wtedy Positive/NegativeOpinionCount będzie od razu na miejscu. Ale co jeśli tabela i widok byłyby duże? Macie jakiś pomysł?

Z czym konkretnie EF3 ma problem? Jeśli z CustomerOpinion to możesz ją chyba pobrać zwykłym joinem. Czy to 3.0 czy 3.1? W 3.1 zostało poprawionych dużo bugów, warto zaktualizować :)

Dopiero zaktualizowałem EF Core, jest 3.1 :) Z tym joinem to też próbowałem, ale EF Core 2 robił cały czas inner joina, a ja potrzebowałemleft joina. Dodanie DefaultIfEmpty nie pomogło. No chyba, że coś się zmieniło.

EDIT: EF Core 3 rzuca wyjątek przy tym takim Joinie

var customerOpinions = _db.ReviewOpinions.Where(o => o.CustomerId == currentCustomerId);

var bestReviews = await _db.Reviews
    .Where(r => r.ProductId == request.ProductId)
    .Join(customerOpinions, r => r.Id, o => o.ReviewId, (review, opinion) => new ReviewModel
    {
        Id = review.Id,
        CustomerId = review.CustomerId,
        CustomerFirstName = review.Customer.FirstName,
        Stars = review.Stars,
        Content = review.Content,
        CreatedAt = review.CreatedAt,
        PositiveOpinionCount = review.Opinions.Count(o => o.IsReviewHelpful),
        NegativeOpinionCount = review.Opinions.Count(o => !o.IsReviewHelpful),
        CustomerOpinion = opinion == null ? null : new CustomerOpinionModel
        {
            IsReviewHelpful = opinion.IsReviewHelpful
        }
    })
    .Take(3)
    .OrderBy(review => review.PositiveOpinionCount - review.NegativeOpinionCount)
    .ToListAsync();

Nie warto walczyć z technologią, zrobiłbym widok, w postgresie można by było użyćj filtrowania na funkcji agregujące, w sql serverze też da się to napisać bez podzapytań skorelowanych. I oddzielnie bym też doczytał opinie pewnie.

Ok, ale co jeśli tabela wyjściowa byłaby duża (np. Products)? Widok zawierający szczegóły zajmie sporo miejsca (i być może potrzeba będzie kilku takich widoków), ale rozumiem, że dla wydajności warto. :P

Widok domyślnie nie zajmuje dodatkowego miejsca, to tylko zapytanie SELECT zapamiętane po stronie serwera.

Dopóki nie założysz indeksu na widok (widok zmaterializowany), to widok zajmuje 0 miejsca na wiersze.

Ale jesli nie zaloze indeksu, to bede musial dwa razy czytac tabele z opiniami przy zalozeniu, ze opinie bede doczytywac osobno. Czy czegos nie zrozumialem?

strzel sobie polyglot persistence i zapisuj cały obiekt do dwóch baz - relacyjnej (normalnie) i nierelacyjnej (już wyliczone rzeczy), a odczyt tylko z nierelacyjnej rób ;)

Próbowałeś użyć ORMa?

Coś w ten deseń:

SELECT
    r.[Id],
    r.[CustomerId],
    c.[FirstName] AS [CustomerFirstName],
    r.[Stars],
    r.[Content],
    r.[CreatedAt],
    rc.[PositiveOpinionCount],
    rc.[NegativeOpinionCount],
    ro.[IsReviewHelpful] AS [CustomerOpinionIsReviewHelpful] 
FROM
    [Reviews] r
	INNER JOIN [Customers] c ON r.[CustomerId] = c.[Id]
	LEFT JOIN [ReviewOpinions] ro on ro.[ReviewId] = r.[Id] and ro.[CustomerID] = c.[CustomerID]
	LEFT JOIN (SELECT
					[ReviewId]
					,sum(case when [IsReviewHelpful] = 1 then 1 else 0 end) [PositiveOpinionCount]
					,sum(case when [IsReviewHelpful] = 0 then 1 else 0 end) [NegativeOpinionCount]
				FROM 
					[ReviewOpinions]
				GROUP BY
					[ReviewId] = r.[Id] 
				) AS rc ON rc.[ReviewId] = r.[Id]

UPDATE:
@nobody01 dlaczego usunoleś swój post? teraz moja odpowiedź nie ma sensu...

Tak sobie myślę, że ten EF Core to niewydajny jest. Zapytanie pobierające recencje z liczbami pozytywnych i negatywnych opinii (kod w pierwszym poście, uproszczony) wygląda tak:

SELECT [t].[Id], [t].[CustomerId], [t].[FirstName] AS [CustomerFirstName], [t].[Stars], [t].[Content], [t].[CreatedAt], [t].[c] AS [PositiveOpinionCount], [t].[c0] AS [NegativeOpinionCount]
FROM (
    SELECT [r1].[Id], [r1].[CustomerId], [c].[FirstName], [r1].[Stars], [r1].[Content], [r1].[CreatedAt], (
        SELECT COUNT(*)
        FROM [ReviewOpinions] AS [r]
        WHERE ([r1].[Id] = [r].[ReviewId]) AND ([r].[IsReviewHelpful] = CAST(1 AS bit))) AS [c], (
        SELECT COUNT(*)
        FROM [ReviewOpinions] AS [r0]
        WHERE ([r1].[Id] = [r0].[ReviewId]) AND ([r0].[IsReviewHelpful] <> CAST(1 AS bit))) AS [c0]
    FROM [Reviews] AS [r1]
    LEFT JOIN [Customers] AS [c] ON [r1].[CustomerId] = [c].[Id]
) AS [t]
ORDER BY [t].[c] - [t].[c0]

Zapytania skorelowane powodują 8 index scanów na Opinions dla 4 recenzji. Podczas gdy własnoręcznie napisane zapytanie wykonuje tylko 1 index scan i do tego pobiera opinię danego klienta. :/

ORMy służą do prostego mapowania między relacjami a obiektami i uprzyjemnienia CRUDów, a nie do optymalizacji skomplikowanych zapytań. Postaw się na miejscu ORM czy mając takie drzewo zapytań z pierwszego posta byłbyś w stanie wygenerować pożądany sql, w generyczny sposób tak by obsłużyć większość tego co może sobie programista wyfantazjować.

ORM jest fajny do nudnych, żmudnych operacji na bazie, jak mamy coś ciekawszego to trzeba się ubrudzić sqlem.

Bez przesady. EF Core jest jeszcze mocno upośledzony co nie znaczy, że każdy ORM tak ma. Nawet EF 6 z dodatkami chyba by sobie z tym poradził.

Wychodzi na to, że jak coś ma w nazwie EF, to nie radzi sobie jako ORM. Dziwny zbieg okoliczności.