How to get scalar value from a SQL statement in a .Net core application?

Question

The following code in a .Net core console application (EF core 2.0/Sql server).

var sql = _context.Set<string>()
          .FromSql("select dbo.FunctionReturnVarchar({0});", id);

got the following exception?

Cannot create a DbSet for 'string' because this type is not included in the model for the context.

Is it a way without defining a class with a property of string?

Answer 1

.Set<TEntity>() is for entities backed by the context.

You can try a workaround

DbConnection = connection = _context.Database.GetDbConnection();

using(DbCommand cmd = connection.CreateCommand()) {

    cmd.CommandText = "select dbo.FunctionReturnVarchar(@id)";    

    cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar) { Value = id });

    if (connection.State.Equals(ConnectionState.Closed)) { connection.Open(); }

    var value = (string) await cmd.ExecuteScalarAsync();
}

if (connection.State.Equals(ConnectionState.Open)) { connection.Close(); }

Answer 2

What you need in this scenario is the ExecuteSqlCommand method with bound output parameter:

var resultParameter = new SqlParameter("@result", SqlDbType.VarChar);
resultParameter.Size = 2000; // some meaningfull value
resultParameter.Direction = ParameterDirection.Output;
_context.Database.ExecuteSqlCommand("set @result = FunctionReturnVarchar({0});", id, resultParameter);
var result = resultParameter.Value as string;