Call Function from Postgres Database in Visual Studio from Devart Entity Model

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
dipti_drd
Posts: 5
Joined: Tue 26 Jul 2016 12:06

Call Function from Postgres Database in Visual Studio from Devart Entity Model

Post by dipti_drd » Tue 26 Jul 2016 12:25

Hello,
I am using Devart Entity Model in my web API/MVC application with Postgres Database.
I have function in Database with the name "fn_getalltaxreturns" whose return type is table(projid int, taxid, intger,name character).

I use Entity Framework with Database-first approach, so my edml file I have imported this function.

I define above function in my class "EdmFunction" as

[EdmFunction("Dd8degrlj74h2fModel.Store", "fn_getalltaxreturns")]
public IEnumerable<taxtype> fn_getalltaxreturns()
{
Dd8degrlj74h2fEntities context = new Dd8degrlj74h2fEntities();
IEnumerable <taxtype> t = context.
CreateQuery<taxtype>("Dd8degrlj74h2fModel.Store.fn_getalltaxreturns()")
.Execute(MergeOption.NoTracking)
.ToList();
return t;
}

And in web api controller I call above function as
edmfunctions cls = new edmfunctions();
IEnumerable<taxtype> taxtype=cls.fn_getalltaxreturns();

Here it throws error
{"The specified cast from a materialized 'System.String' type to the 'TestWebAPI.Models.taxtype' type is not valid."}

How do I call postgres functions returning list of records in Devart Entity Model.

Thanks in Advance.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Call Function from Postgres Database in Visual Studio from Devart Entity Model

Post by Shalex » Thu 28 Jul 2016 11:34

Here is a simple walkthrough:

1. Run the following DDL/DML script in your database

Code: Select all

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE OR REPLACE FUNCTION returntable()
  RETURNS table(p1 int, p2 character varying, p3 character varying) AS
  $BODY$
BEGIN	
	RETURN QUERY SELECT deptno, dname, loc from dept;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
2. Add the Devart Entity Model item (*.edml) to your project and open it. Drag&drop the returntable function from Entity Developer's Database Explorer to the SSDL part of your model in Model Explorer. Then set the Concealed Function to True for this function in Store part in Model Explorer. Drag&drop returntable to Conceptual part, Entity Developer will ask you to allow it to generate the corresponding complex type.

We have just sent a test project to the email specified in your forum profile. Open *.edml, navigate to Tools > Entity Developer > Database Explorer, change the connection string to yours, save the model. Does it work?

dipti_drd
Posts: 5
Joined: Tue 26 Jul 2016 12:06

Re: Call Function from Postgres Database in Visual Studio from Devart Entity Model

Post by dipti_drd » Fri 29 Jul 2016 05:46

Thanks Shalex,this solution worked perfectly.

Post Reply