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?

https://github.com/aspnet/EntityFrameworkCore/issues/1862 At present you cannot use FromSql which returns a different shape than one of the entity types in the model — Smit, Sep 11 '17 at 22:58
I have written an updated answer [here](https://stackoverflow.com/a/73333442/3604216) — Radall, Aug 12 '22 at 11:30

Ivan Stoev · Answer 1 · 2017-09-11T22:28:15.580

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;

score 19 · Accepted Answer · edited Mar 24 '23 at 14:47

19

.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();
}

edited Mar 24 '23 at 14:47

Mike W

425
3
12

answered Sep 11 '17 at 20:21

Nkosi

235,767
35
427
472

I _think_ your `connection.Open[Async](); cmd.Execute[Async](); connection.Close[Async]()` should be wrapped in `using (Database.GetService().EnterCriticalSection()) { ... }` becouse `_context.Database.GetDbConnection()` dont give you your own instance of DbConnection (you are not disposing it, arent you). But i am not sure. – Jan 'splite' K. Dec 18 '19 at 20:01

score -2 · Answer 3 · answered Sep 21 '22 at 18:20

 public static class TeeLinqExtensions
    {
        public static long? TeeNextSeq(this DatabaseContext pCtx)
        {
            long? seqNo=0;

            using (var command = pCtx.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = "SELECT NEXT VALUE FOR student_admin_no_seq AS seq";

                pCtx.Database.OpenConnection();

                  seqNo = command.ExecuteScalar() as long?;

                  pCtx.Database.CloseConnection();

            }

            return seqNo;

        }
    }

then use as

 var studentIdentityNo = _db.TeeNextSeq();

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

3 Answers3

Linked