Efcore: Support for ExecuteScalar

[danobri]

AFAIK the only way to call a stored procedure that returns a scalar value is to use the underlying ADO.Net API like this:

```c#
using (var connection = context.Database.GetDbConnection())
{
connection.Open();

using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SomeStoredProc";

return command.ExecuteScalar();

}
}
```

Alternatively, the stored procedure can be written to use an output parameter, and can then be called using the context.Database.ExecuteSqlCommand function, but that feels unnecessarily complicated, and requires existing procedures to be re-written.

Is there any reason the EF Core team has chosen not to include ExecuteScalar / ExecuteScalarAsync methods on the context.Database object?

[ajcvickers]

@danobri We have discussed this and decided that we will look into doing this. The main reason we haven't done this in the past is because ExecuteScalar is quite a confusing method. Consider this stored proc:

CREATE PROCEDURE [dbo].[GetFoo]
AS
BEGIN

SELECT 76

RETURN 77
END

There is often an expectation that calling ExecuteScalar for this would return 77, since this is the "return value" of the procedure. But it will actually return 76. This also illustrates that ExecuteScalar is really a form of sugar over ExecuteReader. For example, in your code above you could have done this instead:
```C#
using (var reader = command.ExecuteReader())
{
reader.Read();
return reader.GetInt32(0);
}

This means that once #1862 is implemented, then it would be possible to write:
```C#
var value = context.Database.FromSql<int>("SomeStoredProc").First();

and have it return the same thing as an ExecuteScalar. This is possibly less ambiguous than ExecuteScalar and doesn't involve new API surface.

All that being said, ExecuteScalar may perform better (not tested) and may help with discovery for people who know how ExecuteScalar works and are looking for it. So putting this on the backlog to consider in the future.

[danobri]

Great - thank you for adding it to the backlog. #1862 would also address my concerns. Executing SQL to get a single primitive value feels like the kind of thing that should only require a line of code like in your example above. I will subscribe to #1862 and vote it up!

[andriysavin]

This means that once #1862 is implemented, then it would be possible to write:
var value = context.Database.FromSql("SomeStoredProc").First();
and have it return the same thing as an ExecuteScalar. This is possibly less ambiguous than ExecuteScalar and doesn't involve new API surface.

So #1862 is implemented, how do I do ExecuteScalar now?

[divega]

Is this a duplicate of https://github.com/aspnet/EntityFrameworkCore/issues/11624?

[ajcvickers]

Triage: This is a sub-scenario of #11624; tracking there.