Entity Framework & pgsql fuctions (stored procedures)

Entity Framework & pgsql fuctions (stored procedures)

Postby kulibin » Thu 18 Sep 2008 07:07

hi!
I try to use database functions for insert/update/delete entity. Insert function with result binding doesn't work (I use it to get database generated serial id). I try it with several types of functions (with some variation return types) - and I can't make it work . It seems like pgsqldirect.net provider problem - because everything works well with MS SQL Server.

Details:
I add result binding in EDM-designer, and I have compilation error message below:
-----------------------------------------------------------------------------------
Error 1 Error 2046: A mapping function binding specifies a function sp_testModel.Store.test_ins that is not supported. Only functions that cannot be composed are supported.
-----------------------------------------------------------------------------------

if I manualy edit xml edmx file: "IsComposable="false"" then I see next compilation error message:
-----------------------------------------------------------------------------------
Error 1 Error 137: Functions that cannot be composed must not declare a return type.
-----------------------------------------------------------------------------------
How can I get serial id from insert function?
kulibin
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby Shalex » Thu 18 Sep 2008 12:34

These error messages are generated by the EDM designer (not by PostgreSQLDirect .NET provider). If you set the IsComposable="true" attribute, a function will return value. However, there are Microsoft's limitations on usage of such functions, because complete function support was not implemented in Entity Framework v1 release.
If you set the IsComposable="false", you have to remove the ReturnType attribute. In general, you have to return nothing from the insert function.
Have you set the StoreGeneratedPattern="Identity" attribute for the colunm you are interested in? If the problem persists, please post here:
1. a script that creates the database objects and fills them with data;
2. an *.edmx file content;
3. the code example where the error arises.
Alternatively, we will appreciate if you send us (alexsh at devart.com) the test project and the script.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby kulibin » Fri 19 Sep 2008 07:39

I made it work. It works only with function, which return record (I use table type). Generator doesn't correct - it generates returning record parameter (mode IN ). And to make it work I must delete row with this parameter from edmx xml manualy. When updating edm, designer recreates this garbage - and I must delete it again and again . Does more efficient way exist to return serial id from db function?
kulibin
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby Shalex » Fri 19 Sep 2008 09:37

Please provide us with the examples mentioned above. It will help us to find a solution for this problem.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby svinfo » Sun 26 Oct 2008 11:53

I have encoutered the same problems with functions.
I will try your solution.

For information, I have also the same kind of problem when I'm trying to insert PostGresql's Views in the EDMX :

(it says that my view has no primary key).
svinfo
 
Posts: 3
Joined: Thu 23 Oct 2008 23:42

Postby Shalex » Mon 27 Oct 2008 08:04

Only PostgreSQL's Views with at least one column specified as "NOT NULL" can be inserted in the EDMX. There is no easy opportunity to obtain correct PK from View.

If the problem with functions persists, please give us a detailed description of it and send us a test project and a script (DDL and DML) that creates database objects and fills them with data.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL