0

I am writing an app using .net Web Api, Entity Framework and PostgreSql and I am having trouble calling a stored procedure. I've tried to go off of this question: using stored procedure in entity framework but it doesn't seem to help so I'll ask for my specific case and hope I can get help.

I have written a stored procedure in postgres and all I want to do is call it from my repository. What is the best way to go about doing this? If I want to call it though a DbContext object do I need to tell the DbContext that the stored procedure exists before I call it? Would it just be better to use ado.Net to use sql directly? This is what I tried and it gives me a syntax error:

var serverName = new PgSqlParameter("server_name", server);
        var dt = new PgSqlParameter("data_tag", dataTag);
        var timestamp = new PgSqlParameter("ts", DateTime.Now);
        var val = new PgSqlParameter("val", value);

        if (context.Database.Connection.State == ConnectionState.Closed)
            context.Database.Connection.Open();

        try
        {
            context.Database.ExecuteSqlCommand(@"EXEC [dbo].[historical_tag_values_insert]", serverName, dt, timestamp, val);
        }
        finally
        {
            if (context.Database.Connection.State == ConnectionState.Open)
                context.Database.Connection.Close();
        }
Community
  • 1
  • 1
GBreen12
  • 1,832
  • 2
  • 20
  • 38
  • Are you following a database-first approach? If so, why not just add the stored procedure to the database model and just call it on the context object? – Arian Motamedi Aug 07 '14 at 16:01
  • Where, exactly, do you get a syntax error? – Michael Edenfield Aug 07 '14 at 16:05
  • It says "syntax error at or near EXEC". And I am building this for a client so I guess technically we are doing a database first approach. How would I add the stored procedure to the model? What exactly do you mean by that, like add it to the context object? – GBreen12 Aug 07 '14 at 16:12

0 Answers0