Please follow this walkthrough:
1. Create test database objects
Code: Select all
CREATE TABLE DEPT (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
CREATE TABLE EMP (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT,
HIREDATE DATE,
SAL REAL,
COMM REAL,
DEPTNO INT REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
CREATE OR REPLACE FUNCTION getdeptemp()
RETURNS SETOF refcursor AS
$BODY$
DECLARE
dept_cursor refcursor;
emp_cursor refcursor;
BEGIN
OPEN dept_cursor FOR select * from dept;
RETURN NEXT dept_cursor;
OPEN emp_cursor FOR select * from emp;
RETURN NEXT emp_cursor;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
2. Run Create Model Wizard (*.edml) in your project and select the dept, emp tables and the getdeptemp function. Tables will be added to both SSDL and CSDL, the function will be available only in SSDL part of the model.
3. Open *.edml, navigate to Tools > Entity Developer > Model Explorer > Store > Stored Procedures, select getdeptemp and set its Concealed Function property to True. Now you can drag&drop getdeptemp on the diagram surface, press No in the "Do you wish to obtain metadata of procedure result set?" dialog. As a result, Getdeptemp will be available in CSDL.
4. Double click Getdeptemp in CSDL (the Methods node) to open Method Editor. In the Return Type section, select Value Types and set two lines:
Dept
Emp
Press OK, save the model to generate the code.
5. Now run the code:
Code: Select all
using (var context = new MyEntities()) {
var connection = (context.Connection as System.Data.Entity.Core.EntityClient.EntityConnection).StoreConnection;
connection.Open();
var transaction = connection.BeginTransaction();
var result = context.Getdeptemp();
}
JIC: you can avoid creating the getdeptemp function in the database. Instead of this, right click Stored Procedures in Model Explorer > Add > New Command Text with the following SQL Script:
Code: Select all
select * from dept;
select * from emp;
Now set Procedure1's Concealed Function property to True and use it like getdeptemp.