Join na wielu tabelach czy osobne zapytania

Mam takie tabele: Products, Reviews, ReviewComments, Customers, ProductRates, ProductPhotos. Chcę pobrać produkt o danym ID z recenzjami (wraz z nazwiskami autorów), komentarzami do tych recenzji (również z danymi autorów), ocenami (aby sprawdzić, czy użytkownik nie ocenił już produktu, a jeśli tak, to jakoś to zasygnalizować w UI), zdjęciami (właściwie to metadanymi) i pewnie jeszcze z kilkoma rzeczami, o których na razie nie wiem.

I tak: zapytanie pobierające wszystkie te dane składałoby się z 6 joinów, z których 4 byłyby typu one-to-many. Mam wrażenie, że wykonywanie pojedynczych zapytań zamiast jednego dużego byłoby prostsze, ale nie wiem, co się stanie z wydajnością. Tu ktoś pisze, że w przypadku MySQL:
For inner joins, a single query makes sense, since you only get matching rows. For left joins, multiple queries is much better... look at the following benchmark I did

Jak to wygląda dla SQL Server i EF Core?

Napisz to tak, aby było czytelnie w kodzie - jeśli w takiej wersji będzie działać wolno (wtedy i tylko wtedy), pomyśl o optymalizacji.
Będziesz miał również punkt zaczepienia oraz możliwość porównania jednej wersji z drugą - aktualnie możemy tylko gdybać.

Nie powinno byc problemu z połączeniem 6 tabel. Ja mam w jednym miejscu ponad 15 tabel. Jak czasem patrzę w kod procedur sql w xl to tam tez sie nie przejmują :)
No chyba, że Mysql albo ef core sobie nie poradzi. Mysql bywa kapryśny. EF Core to nie wiem bo mało używam.

Robiłem dużo takich zapytań w EF Core i dla SQL Server. Aby zapytanie było wydajne, ważne, żeby nie pobierać wszystkich kolumn z łączonych tabel, tylko, te które Ciebie interesują. Czyli w EF Core należy wykorzystać metodę Select() i wskazać konkretne kolumny.

Nie wiem czy podglądałeś jakie zapytania robi EF Core. Ale na prawdę w najnowszych wersjach to już jest magia. Czasami to co wydawałoby się, że pójdzie jednym Selectem idzie dwoma. I jest szybciej niż jakby szło jednym. I nie musieliśmy o tym myśleć, EF Core robi to za nas.

@nobody01:

Możesz zamienić Queryable na czysty SQL i porównać plan twojego i EF Corowego zapytania.

Dla takich modeli:

public class ProductModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public decimal Price { get; set; }
        public decimal? Discount { get; set; }
        public int ReviewCount { get; set; }
        public int RateCount { get; set; }
        public decimal AverageRate { get; set; }
        public int? UserRate { get; set; }

        public IList<ReviewModel> Reviews { get; set; }
        public IList<PhotoModel> Photos { get; set; }
    }

    public class ReviewModel
    {
        public int Id { get; set; }
        public string CustomerId { get; set; }
        public string CustomerFirstName { get; set; }
        public int Stars { get; set; }
        public string Content { get; set; }
        public int ApprovementCount { get; set; }
        public bool WasApprovedByUser { get; set; }
    }

    public class PhotoModel
    {
        public int Id { get; set; }
        public string Description { get; set; }
        public bool IsMain { get; set; }
    }

przy takiej konfiguracji

string currentCustomerId = null;

CreateMap<Product, ProductModel>()
        .ForMember(dest => dest.ReviewCount, opt => opt.MapFrom(src => src.Reviews.Count()))
        .ForMember(dest => dest.UserRate, opt => opt.MapFrom(src => src.Rates.Where(r => r.CustomerId == currentCustomerId).Select(r => r.Stars).FirstOrDefault()));

CreateMap<ProductPhoto, PhotoModel>();

CreateMap<Review, ReviewModel>()
        .ForMember(dest => dest.ApprovementCount, opt => opt.MapFrom(src => src.Approvements.Count()))
        .ForMember(dest => dest.WasApprovedByUser, opt => opt.MapFrom(src => src.Approvements.Any(a => a.CustomerId == currentCustomerId)));

przy użyciu ProjectTo EF Core generuje taki SQL:

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (22ms) [Parameters=[@__request_ProductId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [dtoProduct].[AverageRate], [dtoProduct].[Description], [dtoProduct].[Discount], [dtoProduct].[Id], [dtoProduct].[Name], [dtoProduct].[Price], [dtoProduct].[RateCount], (
    SELECT COUNT(*)
    FROM [Reviews] AS [r]
    WHERE [dtoProduct].[Id] = [r].[ProductId]
) AS [ReviewCount], COALESCE((
    SELECT TOP(1) [r0].[Stars]
    FROM [Rates] AS [r0]
    WHERE [r0].[CustomerId] IS NULL AND ([dtoProduct].[Id] = [r0].[ProductId])
), 0) AS [UserRate]
FROM [Products] AS [dtoProduct]
WHERE [dtoProduct].[Id] = @__request_ProductId_1
ORDER BY [dtoProduct].[Id]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (3ms) [Parameters=[@__request_ProductId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [dtoProduct.Photos].[Description], [dtoProduct.Photos].[Id] AS [Id0], [dtoProduct.Photos].[IsMain], [dtoProduct.Photos].[ProductId]
FROM [ProductPhotos] AS [dtoProduct.Photos]
INNER JOIN (
    SELECT TOP(1) [dtoProduct0].[Id]
    FROM [Products] AS [dtoProduct0]
    WHERE [dtoProduct0].[Id] = @__request_ProductId_1
    ORDER BY [dtoProduct0].[Id]
) AS [t] ON [dtoProduct.Photos].[ProductId] = [t].[Id]
ORDER BY [t].[Id]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (6ms) [Parameters=[@__request_ProductId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [t0].[Id], (
    SELECT COUNT(*)
    FROM [ReviewApprovements] AS [r1]
    WHERE [dtoProduct.Reviews].[Id] = [r1].[ReviewId]
) AS [ApprovementCount], [dtoProduct.Reviews].[Content], [dtoReview.Customer].[FirstName] AS [CustomerFirstName], [dtoProduct.Reviews].[CustomerId], [dtoProduct.Reviews].[Id] AS [Id0], [dtoProduct.Reviews].[Stars], (
    SELECT CASE
        WHEN EXISTS (
            SELECT 1
            FROM [ReviewApprovements] AS [a]
            WHERE [a].[CustomerId] IS NULL AND ([dtoProduct.Reviews].[Id] = [a].[ReviewId]))
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END
) AS [WasApprovedByUser], [dtoProduct.Reviews].[ProductId]
FROM [Reviews] AS [dtoProduct.Reviews]
LEFT JOIN [Customers] AS [dtoReview.Customer] ON [dtoProduct.Reviews].[CustomerId] = [dtoReview.Customer].[Id]
INNER JOIN (
    SELECT TOP(1) [dtoProduct1].[Id]
    FROM [Products] AS [dtoProduct1]
    WHERE [dtoProduct1].[Id] = @__request_ProductId_1
    ORDER BY [dtoProduct1].[Id]
) AS [t0] ON [dtoProduct.Reviews].[ProductId] = [t0].[Id]
ORDER BY [t0].[Id]

Skoro i tak EF Core wysyła kilka zapytań, to chyba temat można zamknąć.

A czemu nie masz jednego viewmodelu?

@somekind: Nie wiem, co masz na myśli. Ten ProductModel zawiera kolekcje, więc nie mogę zrobić go płaskim. ;)

metgens.pl napisał(a):

Robiłem dużo takich zapytań w EF Core i dla SQL Server. Aby zapytanie było wydajne, ważne, żeby nie pobierać wszystkich kolumn z łączonych tabel, tylko, te które Ciebie interesują. Czyli w EF Core należy wykorzystać metodę Select() i wskazać konkretne kolumny.

Nie wiem czy podglądałeś jakie zapytania robi EF Core. Ale na prawdę w najnowszych wersjach to już jest magia. Czasami to co wydawałoby się, że pójdzie jednym Selectem idzie dwoma. I jest szybciej niż jakby szło jednym. I nie musieliśmy o tym myśleć, EF Core robi to za nas.

W temacie jak mierzyłem, że zapytanie EF Core są szybsze.

Keidyś mieliśmy problem z wydajnością EF Core i myśleliśmy, że to wina samego EF Core, więc zdowngrade'owaliśmy do EF 6 i tam jedno z tych zapytąń co szły 2 selectami tam szło jednym i różnica była ok. 20% na korzyść EF Core. Potem okazało się, że problem w ogóle nie był w EF tylko w naszej głupocie :D

@nobody01 czyli ty nie piszesz sam zapytania do EF Core tylko poniekąd ono u Ciebie powstaje przez mapowanie Automapperem? Ciekawe podejście ;) Tu te 3 zapytania wynikają z tego, że odpalasz 3 mapowania. Nie ma to nic wspólnego z EF Core.

@metgens.pl: Te 3 zapytania wynikają raczej z tego, że mam zagnieżdzony model. Dla takiego zapytania:

var product = await _db.Products
                .Select(p => new ProductModel
                {
                    Id = p.Id,
                    Name = p.Name,
                    Description = p.Description,
                    Price = p.Price,
                    Discount = p.Discount,
                    RateCount = p.RateCount,
                    AverageRate = p.AverageRate,
                    Photos = p.Photos.Select(photo => new PhotoModel
                    {
                        Id = photo.Id,
                        Description = photo.Description,
                        IsMain = photo.IsMain
                    }).ToList()
                }).FirstOrDefaultAsync(p => p.Id == request.ProductId);

dostaję

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (1ms) [Parameters=[@__request_ProductId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [p].[Id], [p].[Name], [p].[Description], [p].[Price], [p].[Discount], [p].[RateCount], [p].[AverageRate]
FROM [Products] AS [p]
WHERE [p].[Id] = @__request_ProductId_0
ORDER BY [p].[Id]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (2ms) [Parameters=[@__request_ProductId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [p.Photos].[Id] AS [Id0], [p.Photos].[Description], [p.Photos].[IsMain], [p.Photos].[ProductId]
FROM [ProductPhotos] AS [p.Photos]
INNER JOIN (
    SELECT TOP(1) [p0].[Id]
    FROM [Products] AS [p0]
    WHERE [p0].[Id] = @__request_ProductId_0
    ORDER BY [p0].[Id]
) AS [t] ON [p.Photos].[ProductId] = [t].[Id]
ORDER BY [t].[Id]

Poza tym, jeśli wykonuje się joina typu one-to-many, to zawsze chyba trzeba wykonać tych kilka mapowań, więc zawsze będzie kilka zapytań szło do bazy zamiast jednego.