Linq generated SQL query selects all columns when any related entity property is selected

Question

Summary

I'm looking into some SQL statements generated by Linq in our .NET Core 3.1.3 app, and found that whenever I use Take in my query, an inner SQL statement which selects all of the columns from the [cars] table is generated, even columns that I'm not at all interested in. There is also an outer select that selects the columns that I actually want. We have several usages which fetch a lot of related entity properties using DTOs, so this is why I'm looking into this behavior.

I'm not very experienced with SQL, but intuitively, it feels wrong that the inner SELECT TOP selects all of the columns when it's not really needed.

Why is this, and can it cause performance issues when the number of related entity properties grows?
Is my query in example 3 actually better from a performance point of view than example 2?

[UPDATE]: So apparently this behavior can be avoided if you put the Take after the Select instead of before:

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName
                               })
                               .Take(10)
                               .ToListAsync();

Which generates the following:

SELECT TOP(@__p_0) [c].[regNr] AS [Licenseplate], [c].[carName] AS [CarName], [c0].[name] AS [CustomerName]
      FROM [cars] AS [c]
      LEFT JOIN [salesInfoes] AS [s] ON [c].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      WHERE [c].[carID] = 72763

Examples

Example 1. The following Linq expression results in every property from car being selected in the inner SELECT TOP statement of the SQL query.

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Take(10)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName
                               })
                               .ToListAsync();

Generated SQL (columns masked with x in the inner SELECT):

SELECT [t].[regNr] AS [Licenseplate], [t].[carName] AS [CarName], [c0].[name] AS [CustomerName]
      FROM (
          SELECT TOP(@__p_0) [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x]
          FROM [cars] AS [c]
          WHERE [c].[carID] = 72763
      ) AS [t]
      LEFT JOIN [salesInfoes] AS [s] ON [t].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]

Without Take, the inner SELECT disappears and the query looks much lighter:

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName
                               })
                               .ToListAsync();

Generated SQL:

 SELECT [c].[regNr] AS [Licenseplate], [c].[carName] AS [CarName], [c0].[name] AS [CustomerName]
      FROM [cars] AS [c]
      LEFT JOIN [salesInfoes] AS [s] ON [c].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      WHERE [c].[carID] = 72763

Example 2. If I add another related entity property, I get another nested SELECT TOP in the query with all of the columns, again:

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Take(10)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName,
                                   FacilityName = c.FacilityNow.Name
                               })
                               .ToListAsync();

Generated SQL

SELECT [t0].[regNr] AS [Licenseplate], [t0].[carName] AS [CarName], [c0].[name] AS [CustomerName], [f].[name] AS [Name]
      FROM (
          SELECT TOP(@__p_0) [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x]
          FROM (
              SELECT TOP(@__p_0) [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x]
              FROM [cars] AS [c]
              WHERE [c].[carID] = 72763
          ) AS [t]
      ) AS [t0]
      LEFT JOIN [salesInfoes] AS [s] ON [t0].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      INNER JOIN [facilities] AS [f] ON [t0].[facilityNowID] = [f].[facilityID]

Example 3. This is what I think the query should look like to not fetch data that I don't need.

SELECT [t0].[regNr] AS [Licenseplate], [t0].[carName] AS [CarName], [c0].[name] AS [CustomerName], [f].[name] AS [Name]
      FROM (
          SELECT TOP 100 [t].[carID], [t].[regNr], [t].[carName], [t].[facilityNowID]
          FROM (
              SELECT TOP 100 [c].[carID], [c].[regNr], [c].[carName], [c].[facilityNowID]
              FROM [cars] AS [c]
              WHERE [c].[carID] = 72763
          ) AS [t]
      ) AS [t0]
      LEFT JOIN [salesInfoes] AS [s] ON [t0].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      INNER JOIN [facilities] AS [f] ON [t0].[facilityNowID] = [f].[facilityID]

Linq generated SQL query selects all columns when any related entity property is selected

0 Answers