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)
@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 Valueon the output column, so that EF can reference it outside the subquery.@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 yourIQueryable. 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.~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:
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.
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.
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..."?