How to get scalar value from a SQL statement in a .Net core application?
.net-core c# entity-framework-core
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?
Accepted Answer
.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(); }
Popular Answer
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;
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow