EF Core - translating to SQL simple select

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?

No, actually that's a great improvement. In EF6, multiple Incudes easily [blow up the SQL result set both in length and in width](https://stackoverflow.com/a/34732579/861716). Executing multiple relatively slim queries prevents that. The key to performance is that the queries are executed in one batch. — Gert Arnold, Jan 06 '19 at 18:53
@GertArnold So, I shouldnt try to get something better from this LINQ? — Tezzy, Jan 06 '19 at 19:29
No, let EF do it's own tricks. If you manipulate it you're screwed when EF makes it even better later. — Gert Arnold, Jan 07 '19 at 15:02

Jim Wooley · Answer 1 · 2019-01-07T17:59:18.543

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.

Hmm, so any example of how can I do it in a different way? Or maybe "MainImage" should be just an Id of an ``Image`` and instead of having it in this property, ill just move it to the Images list? — Tezzy, Jan 07 '19 at 21:02

EF Core - translating to SQL simple select

1 Answers1