How to execute stored procedure from Entity Framework 6 (database first) and Npgsql?

Question

Is this a bug?

My components are:

.NET 4.6.1
Entity Framework 6.0
Npgsql 3.0.5
PostgreSQL 9.5

First, I created a table and stored procedure in PostgreSQL 9.5

CREATE TABLE hello
(
  msg text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE hello
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION sayhello()
  RETURNS SETOF hello AS
$BODY$ 

select
 *
 from version()

  $BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION sayhello()
  OWNER TO postgres;

Second, I went to the .edmx file (Entity Framework 6.0), choose "update from database", selected the new table "hello" and the new stored procedure, "sayhello".

The Model Browser now shows the new table entity and the imported function.

Third, add a new procedure to the WCF file:

public string SayHello()
{
            using (var ctx = new chaosEntities())
            {
                var x = ctx.sayhello();
                return "Hello";
            }
}

Set the WCF Service as Startup project and Start Debugging.

The WCF Test Client comes up. Executing SayHello() from the WCF Service leads to:

public virtual ObjectResult<hello> sayhello()
{
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<hello>("sayhello");
}

When this is executed, I get:

An exception of type 'System.Data.Entity.Core.EntityCommandCompilationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: An error occurred while preparing the command definition. See the inner exception for details.

Inner Exception is: {"Value does not fall within the expected range."}

As I have several hundred stored procedures, any help on how to fix this is most appreciated.

TIA

Note: I suspect the problem is with NpgsqlServices.TranslateCommandTree, but I'm only guessing.

Alan Wayne · Accepted Answer · 2016-02-28 21:40:36Z

I could never get it to work the way I hoped (via EntityFramework), so I ended up doing this. I'd sure be open to a better solution!

The below code calls Npgsql directly to avoid the whole EntityFramework thing.

public string SayHello()
        {
            using (var ctx = new chaosEntities())
            {
                var b = ctx.Database.Connection.ConnectionString;

                using (var conn = new Npgsql.NpgsqlConnection(connectionString: b))
                {
                    conn.Open();
                    using (var tran = conn.BeginTransaction())
                    using (var command = conn.CreateCommand())
                    {
                        command.CommandText = "sayhello";
                        command.CommandType = CommandType.StoredProcedure;

                        var g = (string)command.ExecuteScalar();
                        return g;
                    }
                }
            }
        }

Collectives™ on Stack Overflow

How to execute stored procedure from Entity Framework 6 (database first) and Npgsql?

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