Execute stored function returning primitive in postgresql using entity framework core

Question

I had a project running on Postgresql. I used Entity framework to execute stored procedure that would return some value (long).

 public override long GetHostSequenceNumber(byte hostId, byte srvId)
 {
    var hId = new Npgsql.NpgsqlParameter("@host_id", hostId);
    var sId = new Npgsql.NpgsqlParameter("@srv_id", srvId);
    var res = this.Database.SqlQuery<long>("select * from  dbo.bs_p_get_host_seqno( @host_id,@srv_id)", hId, sId).SingleOrDefault();
    return res;
  }

Briefly, lets say that this procedure searhes for some specific value in some tables.

And it worked just fine. But now I switched to Core 2.0 and I can't find a way to do the same thing. I've read some articles, where they say to use FromSql, but I can't use it since I don't have any entities that I would add to DbContext. I also tried use ADO and execute SQL commands but I always get -1 in the results.

public override long GetHostSequenceNumber(byte hostId, byte srvId)
    {
        var hId = new Npgsql.NpgsqlParameter("@host_id", hostId);
        var sId = new Npgsql.NpgsqlParameter("@srv_id", srvId);
        var res = this.Database.ExecuteSqlCommand("select * from  bs_p_get_host_seqno(@host_id,@srv_id)", hId,sId);
        return res;
    }

But ExecuteSqlCommand also always returns -1 and it is int type and I don't know how to get the result that I need.

Hakan Fıstık · Accepted Answer · 2020-11-08 14:13:34Z

9

So I get it work. Here is the code.

using (var command = this.Database.GetDbConnection().CreateCommand())
{
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "bs_p_get_host_seqno";
    command.Parameters.Add(new Npgsql.NpgsqlParameter("host_id", NpgsqlTypes.NpgsqlDbType.Smallint)
    { Value = hostId });
    command.Parameters.Add(new Npgsql.NpgsqlParameter("srv_id", NpgsqlTypes.NpgsqlDbType.Smallint)
    { Value = srvId });
    if (command.Connection.State == ConnectionState.Closed)
        command.Connection.Open();
    var res = (long)command.ExecuteScalar();
    return res;
}

My problem was that I put "select * from bs_p_get_host_seqno" to command.CommandText instead of function name.

edited Nov 8, 2020 at 14:13

Hakan Fıstık

19.9k16 gold badges124 silver badges152 bronze badges

answered Dec 15, 2017 at 15:05

daewoosh

3335 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute stored function returning primitive in postgresql using entity framework core

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related