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

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

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 5
  • Comments: 19 (8 by maintainers)

Most upvoted comments

@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.

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.

if it returns primitive and ends by Single or SingleOrDefault, then just get the first row and first column value. i don’t believe it is very hard to do this.

~OK, can you provide the SQL you’d expect EF to generate for that case?~ Well, f one thing, FirstOrDefault()'s job is to make sure only 1 row actually gets fetched from the database. If we allow a query to download the entire contents of some huge table, only to take the first row and discard the rest, we’ll have done our users a very big dis-service by hiding a potentiialy critical performance problem. Unless I’m mistaken, Dapper has a different approach here - it sends your SQL as-is, and any operators are executed in memory on the results, which can cause exactly this problem if users aren’t careful (in other words, EF’s composed operators are IQueryable, while Dapper’s are IEnumerable). So FirstOrDefault() must impact the SQL which EF sends to the database, adding e.g. LIMIT 1 (depending on the database) - that instructs the database to send back just one row. But since the SQL here is a string provided by the user, we can’t manipulate it, since that would require us to be able to parse SQL, which EF cannot do (parsing SQL is a hugely complex task that varies considerably across dialects). This is why when you compose operators, EF takes the user’s SQL string, and integrated it as a subquery within a larger SQL: SELECT … FROM ( – User SQL ) But once there’s a subquery, the SQL outside of the subquery (which EF generates) needs to know the name of the column(s) projected out from that subquery - that’s just how SQL works. So we’re left with no alternative but to require that some specific name be used, i.e. Value. This is just the somewhat unfortunate consequence of trying to mix arbitrary user-provided SQL (which EF cannot understand or manipulate) with additional EF LINQ operator composed on top.

I fully understand the limitations and vagaries from the SQL side, but… does this problem absolutely have to be solved via SQL? Is it not possible to instead implement a solution in C# that optimises SqlQuery<TValue>(<sql string>).First() to use a SqlDataReader and pull out the first row + column of data from the latter?

@roji made his answer a bit more constructive (eventhough the first one had the same message / was quite funny). I guess your mistake is that you only see the issue from one side. Sure, if e.g. First() was the last call EF could wrap something around it to make it work. But don’t forget you’re able to even put this somewhere else in your IQueryable. EF needs to be able to build an SQL for every expression.

And imho the team is totally right if they decide not to start implementing exceptions for special use-cases. Thing would get way more confusing, if a Queryable runs under some circumstances and won’t if called differently.

If the SQL syntax would allow something like this (like SELECT *[0] AS value FROM Data, but there is nothing like this) the team could implement it. But there’s no chance to do it. And that’s what @roji was asking for if he asked to supply an SQL - at least I think so.

if it returns primitive and ends by Single or SingleOrDefault, then just get the first row and first column value. i don’t believe it is very hard to do this.

~OK, can you provide the SQL you’d expect EF to generate for that case?~

Well, f one thing, FirstOrDefault()'s job is to make sure only 1 row actually gets fetched from the database. If we allow a query to download the entire contents of some huge table, only to take the first row and discard the rest, we’ll have done our users a very big dis-service by hiding a potentiialy critical performance problem. Unless I’m mistaken, Dapper has a different approach here - it sends your SQL as-is, and any operators are executed in memory on the results, which can cause exactly this problem if users aren’t careful (in other words, EF’s composed operators are IQueryable, while Dapper’s are IEnumerable).

So FirstOrDefault() must impact the SQL which EF sends to the database, adding e.g. LIMIT 1 (depending on the database) - that instructs the database to send back just one row. But since the SQL here is a string provided by the user, we can’t manipulate it, since that would require us to be able to parse SQL, which EF cannot do (parsing SQL is a hugely complex task that varies considerably across dialects).

This is why when you compose operators, EF takes the user’s SQL string, and integrated it as a subquery within a larger SQL:

SELECT ...
FROM (
    -- User SQL
)

But once there’s a subquery, the SQL outside of the subquery (which EF generates) needs to know the name of the column(s) projected out from that subquery - that’s just how SQL works. So we’re left with no alternative but to require that some specific name be used, i.e. Value.

This is just the somewhat unfortunate consequence of trying to mix arbitrary user-provided SQL (which EF cannot understand or manipulate) with additional EF LINQ operator composed on top.

@IanKemp that’s always easy to say, but what do you suggest?

if it returns primitive and ends by Single or SingleOrDefault, then just get the first row and first column value. i don’t believe it is very hard to do this.

This is bad design because it violates the principle of least astonishment.

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.

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