.First() on SqlQuery results in exception

[vanillajonathan]

Interestingly enough using SqlQuery with First() does not work. However doing ToList().First() works.

Does not work:

var sql = FormattableStringFactory.Create("SELECT Id FROM Pets");
var query = _context.Database.SqlQuery<Guid>(sql).First();

However using ToList().First() does work:

var sql = FormattableStringFactory.Create("SELECT Id FROM Pets");
var query = _context.Database.SqlQuery<Guid>(sql).ToList().First();

Stack trace

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.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.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)

Include provider and version information

EF Core version: 7.0.10
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 6.0
Operating system: Windows 11 (22621.2134)
IDE: Visual Studio 2022 17.7.3

[ajcvickers]

Duplicate of #30447

[roji]

@vanillajonathan tl;dr just name the column coming out of your SQL query Value, as per the docs: SELECT Id AS Value FROM Pets

[vanillajonathan]

@roji The docs you linked to does not mention "AS Value".

[roji]

@vanillajonathan what am I missing?

[vanillajonathan]

@roji My apologies, I missed it.

[vierlijner]

For me it was also not so clear, because the document used the value for the where statement and @vanillajonathan didn't used that (and me also not). And I want an other result (field) than the where clause is from. So I made this based on the documentation:
var result = await _context.Database .SqlQuery<string>($"SELECT [PARVALUE], [PARNAME] AS Value FROM [Config]") .Where(PARNAME => PARNAME == parName) .AsNoTracking() .FirstOrDefaultAsync(cancellationToken); This also results in an error.
Have to rewrite with some search help to =>
var result = await _context.Database .SqlQuery<string>($"SELECT [PARVALUE] AS Value FROM [Config] AS c WHERE c.[PARNAME] = {parName}") .AsNoTracking() .FirstOrDefaultAsync(cancellationToken);
I also struggled with this and came on the #30447 issue and then to this linked issue. So I share my story, but I read the team discussed this and won't do anything about it, But maybe add some more examples in the documentation?

Update:
I also get the warning: "The query uses the 'First'/'FirstOrDefault' operator without 'OrderBy' and filter operators. This may lead to unpredictable results." This I also don't see in the documentation.

[roji]

@vierlijner I'm not quite sure exactly what it is you tried and why, or what exact examples/docs would have helped here.

I also get the warning: "The query uses the 'First'/'FirstOrDefault' operator without 'OrderBy' and filter operators. This may lead to unpredictable results." This I also don't see in the documentation.

What value would there be in putting something in the docs about this, given that EF itself already says everything there is to know in the warning itself?

[vierlijner]

Put in the docs how to get a single value with .First() or .FistOrDefault() as an example with the OrderBy for example for the SqlQuery() instruction.
Also I don't know why I should order/ filter a single outcome of a query.
I know the outcome of SqlQuery is a IQueryable, but with .FromSql() and the Raw version needs a model, what is not the case here. We only need one single value (in primitive type) from one row/ record.

[roji]

Also I don't know why I should order/ filter a single outcome of a query.

If you're expecting a query to only return a single result, it's recommended to use .Single() or .SingleOrDefault() - no warnings are issued for those (and an exception is generated if there's more than one result).

In general, I believe that where we report an informative, useful warning (such as the above), there's little value in also doing exhaustive documentation; users ideally get the warning "just in time" - when they're trying to problematic thing - and it's unlikely they'd read all of our query docs up-front.

In addition, adding more and more docs bloats them, and makes it hard for users actually find what they're looking for; if we documented every little corner of the query pipeline, you'd end up with a massive, unreadable document. So we prefer to inform users of problems via warnings/errors, and to strike a good balance when explicitly documenting things.