EF Core - translating to SQL simple select

.net-core c# entity-framework-core linq-to-sql sql

Question

I have very simple models

.NET Core 2.1 / EF Core 2.1 / MSSQL

public class ImageZ
{
    [Key]
    public Guid Id { get; set; }
    public string Base64 { get; set; }
    public string Title { get; set; }
}

public class Gallery
{
    [Key]
    public Guid Id { get; set; }
    public ImageZ MainImage { get; set; }
    public List<ImageZ> Images { get; set; } = new List<ImageZ>();
}

and I'm using this LINQ to load it from db

return _context
       .Gallery
       .Include(x => x.Images)
       .Include(x => x.MainImage)
       .OrderBy(x => Guid.NewGuid())
       .FirstOrDefault();

But it sends two queries to db

SELECT TOP(1) [x].[Id], [x].[MainImageId], [x.MainImage].[Id], [x.MainImage].[Base64], [x.MainImage].[GalleryId], [x.MainImage].[Title]
FROM [Gallery] AS [x]
LEFT JOIN [ImageZ] AS [x.MainImage] ON [x].[MainImageId] = [x.MainImage].[Id]
ORDER BY NEWID(), [x].[Id]

SELECT [x.Images].[Id], [x.Images].[Base64], [x.Images].[GalleryId], [x.Images].[Title]
FROM [ImageZ] AS [x.Images]
INNER JOIN (
    SELECT DISTINCT [t].*
    FROM (
        SELECT TOP(1) [x0].[Id], NEWID() AS [c]
        FROM [Gallery] AS [x0]
        LEFT JOIN [ImageZ] AS [x.MainImage0] ON [x0].[MainImageId] = [x.MainImage0].[Id]
        ORDER BY [c], [x0].[Id]
    ) AS [t]
) AS [t0] ON [x.Images].[GalleryId] = [t0].[Id]
ORDER BY [t0].[c], [t0].[Id]

Is it correct behaviour? shouldn't it be done just with one?

1
0
1/6/2019 6:41:36 PM

Popular Answer

Do you really need to select * from MainImage and Images? The blanket include could have an impact on your index selection. With 2.2, you can now use a projection with .ToList() to only select the columns you need. It will still use separate queries for each child collection, but will be limited to the columns you project into.

Alternatively, since you are only selecting one row (at random due to the order by on New Guid), you might be able to issue the separate requests explicitly and not need the order by in the secondary query (over Images). Indeed since each query will be using a different newid(), I suspect your images results in this case won't be properly aligned and you may need to do it explicitly.

0
1/7/2019 5:59:18 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow