SqlQuery doesn't support scalar values but works with unmapped types

[Misiu]

Preview 1 of EF8 supports Raw SQL Queries, but we must create class to be able to get a single scalar value.

so this works:

var id = "5A83F3C3-A88F-4A56-934C-FFB8D0E682C1";
var product = context.Database.SqlQuery<Product>($"SELECT name FROM dbo.test_table WHERE entity_id={id}").FirstOrDefault();
Console.WriteLine(product?.Name);

and this throws error:

var name = context.Database.SqlQuery<string>($"SELECT name FROM dbo.test_table WHERE entity_id={id}").FirstOrDefault();
Console.WriteLine(name);

full example:

using Microsoft.EntityFrameworkCore;

class Program
{
    static void Main(string[] args)
    {
        // specify the connection string to your SQL Server database
        var connectionString = "Server=192.168.0.20;Database=TEST;User Id=test;Password=secret;TrustServerCertificate=True;Application Name=TEST;";

        var options = new DbContextOptionsBuilder<MyDbContext>()
            .UseSqlServer(connectionString)
            .Options;

        using (var context = new MyDbContext(options))
        {
            //this works
            var id = "5A83F3C3-A88F-4A56-934C-FFB8D0E682C1";
            var product = context.Database.SqlQuery<Product>($"SELECT name FROM dbo.test_table WHERE entity_id={id}").FirstOrDefault();
            Console.WriteLine(product?.Name);

            /*
            exec sp_executesql N'SELECT TOP(1) [p].[Name]
            FROM (
                SELECT name FROM dbo.test_table WHERE entity_id=@p0
            ) AS [p]',N'@p0 nvarchar(4000)',@p0=N'5A83F3C3-A88F-4A56-934C-FFB8D0E682C1'
             */

            //this throws error with message "Invalid column name 'Value'."

            var name = context.Database.SqlQuery<string>($"SELECT name FROM dbo.test_table WHERE entity_id={id}").FirstOrDefault();
            Console.WriteLine(name);


            /*
            exec sp_executesql N'SELECT TOP(1) [t].[Value]
            FROM (
                SELECT name FROM dbo.test_table WHERE entity_id=@p0
            ) AS [t]',N'@p0 nvarchar(4000)',@p0=N'5A83F3C3-A88F-4A56-934C-FFB8D0E682C1'
            */

        }

        Console.ReadLine();
    }
}

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }
}

public class Product
{
    public string Name { get; set; }
}

Error code

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Invalid column name 'Value'.
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) in /_/src/EFCore.Relational/Storage/RelationalCommand.cs:line 533
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator) in /_/src/EFCore.Relational/Query/Internal/SingleQueryingEnumerable.cs:line 240
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator) in /_/src/EFCore.Relational/Query/Internal/SingleQueryingEnumerable.cs:line 168
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded) in /_/src/EFCore.SqlServer/Storage/Internal/SqlServerExecutionStrategy.cs:line 57
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext() in /_/src/EFCore.Relational/Query/Internal/SingleQueryingEnumerable.cs:line 171
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found) in /_/src/libraries/System.Linq/src/System/Linq/Single.cs:line 88
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in /_/src/EFCore/Query/Internal/QueryCompiler.cs:line 68
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in /_/src/EFCore/Query/Internal/EntityQueryProvider.cs:line 63
   at Program.Main(String[] args) in D:\GitHub\EF8Tests\EF8Tests\Program.cs:line 30

Include provider and version information

EF Core version: 8.0.0-preview.1.23111.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.5.1

[andriysavin]

Have you tried as a workaround to add column name EF is expecting in your query, e.g. "SELECT name AS Value FROM..."?

[Misiu]

Have you tried as a workaround to add column name EF is expecting in your query, e.g. "SELECT name AS Value FROM..."?

That works, but as you wrote, this is a workaround. It should work without as value.

[roji]

This behavior is by design. As the docs explain, when you compose LINQ operators over your SQL query, EF must know what the column name is that's coming out of it - your SQL query is integrated as a subquery in the larger query. In your code above, you're composing FirstOrDefault on top of the SqlQuery, so it's your responsibility to add the AS Value on the output column, so that EF can reference it outside the subquery.

[goenning]

Why is "as value" not needed in this example?

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

[roji]

@goenning because no operators are being composed on top of SqlQuery. ToList simply loads all of the query results, whereas FirstOrDefault composes a SELECT TOP(1) [t].[Value] ... on top of it.

[angelaki]

@goenning I guess the DbReader just uses the first column no matter what name it has.

While theSQL builder needs to know the columns name. So for scalars just assumes value.

@roji why not just use * for scalars? Would it mean a too expensive internal implementation / changes for this single usecase? I'm excited in the design decision. Not parsing the actual name from the query I totally agree - not worth the effort.

[roji]

Star (*) might work in this very particular case, but depending what exactly you compose, it might not. Star in SQL can mean multiple columns, which wouldn't work in various other SQL contexts - in this scenario there's only ever a single column coming out etc.

We generally avoid star because when reading results back, we'd have to read them by name (since ordering isn't known/clear); and that's slightly less efficient than reading by index.

[angelaki]

@roji Yeah sure, totally agree! Bin since you can read (the not sub selected) scalar value query with every column name, I guess in this case you say: just the first / single column. And so the SQL generator could say: scalar? Star (*) is just fine, since there can only be a single value. But yeah, following sub querys might fail. So I guess you just decided: call it value and the reader just takes the first column?

[Misiu]

Hmm...
so the correct query (which works) is:
$"SELECT name as value FROM dbo.test_table WHERE entity_id={id}"
but this also works:
$"SELECT name as value, 1 as id FROM dbo.test_table WHERE entity_id={id}"
$"SELECT name as not_value, 'fake data' as value FROM dbo.test_table WHERE entity_id={id}"

I think that we need a proper scalar function that will return the first column of the first row in the result set returned by the query, just like https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=dotnet-plat-ext-8.0

I use this workaround:

public static T? SqlQueryScalar<T>(this DatabaseFacade facade, FormattableString sql)
{
    using var cmd = facade.GetDbConnection().CreateCommand();
    cmd.CommandText = sql.ToString();

    if (cmd.Connection != null && cmd.Connection.State != ConnectionState.Open)
    {
        cmd.Connection.Open();
    }

    var result = cmd.ExecuteScalar();

    if (result is null)
    {
        return default;
    }

    return (T)result;
}

but as I wrote this is a workaround and with this, I must change my query from to:
$"SELECT name as value FROM dbo.test_table WHERE entity_id='{id}'" <- extra apostrophes around the parameter

[goenning]

And it seems like Value might not be the best name for it? Postgres doesn't like it:

SELECT t."Value"
      FROM (
          SELECT id as Value FROM app_environments WHERE app_key = @p0
      ) AS t

Npgsql.PostgresException (0x80004005): 42703: column t.Value does not exist

And I'm forced to wrap it on double quotes :(

[roji]

Yep, you indeed need the double-quotes since PG folds unquoted identifiers to lowercase.

[ajcvickers]

We discussed this and we're happy with the current design. While we could have special cases to handle some queries that are composed over by LINQ, the value seems small.