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

[dipti_drd]

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]

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]

Thanks Shalex,this solution worked perfectly.