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

Question

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

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

received the following exception:

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

Is this a way not defining a class with string property?

Answer 1

.Set<TEntity>() Designed for objects supported by context.

You can try a workaround

DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

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

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

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

Answer 2

In this scenario, you will need a method ExecuteSqlCommandwith an associated 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;