Query: composing projection on top of FromSql doesn't get translated correctly

[maumar]

test:
FromSqlRaw_queryable_simple_projection_composed

context.Set<Product>().FromSqlRaw(
                        NormalizeDelimetersInRawString(
                            @"SELECT *
FROM [Products]
WHERE [Discontinued] <> " + boolMapping.GenerateSqlLiteral(true) + @"
AND (([UnitsInStock] + [UnitsOnOrder]) < [ReorderLevel])"))
                    .Select(p => p.ProductName)
                    .ToArray();

expected sql:

SELECT [p].[ProductName]
FROM (
    SELECT *
    FROM ""Products""
    WHERE ""Discontinued"" <> CAST(1 AS bit)
    AND ((""UnitsInStock"" + ""UnitsOnOrder"") < ""ReorderLevel"")
) AS [p]

actual sql:

SELECT *
FROM ""Products""
WHERE ""Discontinued"" <> CAST(1 AS bit)
AND ((""UnitsInStock"" + ""UnitsOnOrder"") < ""ReorderLevel"")

[smitpatel]

For the record it is correct translation, just gets more data because we lifted subquery.

[PawelGerr]

I'm not sure I understand the answer. Is the current behavior going to be changed?

If not, can you explain why the following queries should generate different queries:

// Generates unexpected SQL: 
//      SELECT * FROM Products

Context.Products.FromSqlRaw("SELECT * FROM Products")
            .Select(p => p.Id)
            .ToList()

// Generates correct SQL: 
//      SELECT [p].[Id] FROM [Products] AS [p]

Context.Products
            .Select(p => p.Id)
            .ToList()

Interesting enough, FromSqlRaw + First/FirstOrDefault work correctly

// Generates correct SQL:
//       SELECT TOP(1) [p].[Id]
//       FROM (
//              SELECT * FROM Products
//       ) AS [p]

Context.Products
            .FromSqlRaw("SELECT * FROM Products")
            .Select(p => p.Id)
            .FirstOrDefault();

Fetching unnecessary columns lowers performance and the current behavior feels like a bug because it occurs "randomly", at least from the point of view of the developer using the EF.

Tested with

• Microsoft.EntityFrameworkCore.SqlServer: 3.0.0 and 3.1.0-preview1

[smitpatel]

I may just do it as tiny project.