Problem with View - Urgent

[tcurtis]

Hi All

Pulling my hair out today. I need to retrieve a sequence from Oracle via Entity Framework. Giving up with stored procs/functions as it seems almost impossible to do this remarkably simple thing.

I'm now trying to do this with a view. I have a view which calls a function which returns the nextval from a sequence.

The view shows up when I do 'Update from database' under Views. I select the view and click finish - and absolutely nothing happens. No errors, nothing, the view simply isn't there in the model.

Can anybody help please? This is getting pretty urgent as I've spent an entire day simply trying to get a sequence nextval. Crazy or what man!

Cheers dudes.

[tcurtis]

CREATE OR REPLACE VIEW get_next_tapetp_seq
AS
SELECT
CAST(get_sequence_nextval('tapetp_seq') AS NUMBER(9)) AS nextval
FROM
dual

[AndreyR]

Entity Framework has a limitation: every entity should have Primary Key defined.
The view you have created does not have Primary Key and can't have it, so there is no possibility to add it to the model.
If you have to use stored procedures, please take a look at this blog post:
http://www.devart.com/blogs/dotconnect/?p=5
But if you simply want to retreive the value of the inserted primary key which is generated from the sequence,
it will be enough to set the StoreGeneratedPattern attribute in the Store part of the Entity Framework model
to "Identity" for this column.

[tcurtis]

Hi AndreyR

Thanks for your reply, much appreciated. Unfortunately, I'm still not much clearer. I can change the view for a stored proc or a function but I'm still not clear how to actually map it. The stored proc would take one input parameter, the name of the sequence and return the next value (int64).

I'm not clear what you mean when you refer to inserted primary key etc. I don't have a primary key, I just want to get a sequence which has nothing to do with any table. Is this possible?

Thanks for your help.

[tcurtis]

Managed to sort it out by bypassing EF. Just not worth the bother of trying to get EF to do such simple things, I'll wait until the next release and then try again.

I'm opening a seperate connection to get my sequence number and wrapping this connection and my EF connection in a transactionscope so they both participate in the same transaction.

Have to say, EF is pretty sucky and MS need to get their act together fast or people will drop it.

[AndreyR]

There is no way to handle the stored procedure returning value that is not an Entity using the Microsoft Entity Framework v1.
I recommend you to create a stored procedure with out parameter like in the following example:

Code: Select all

CREATE OR REPLACE procedure SCOTT.SELECT_AUTOINC_SEQ (t out number)
AS
BEGIN
  SELECT autoinc_seq.NEXTVAL INTO t FROM DUAL;
  END;

Then add it to your Devart Entity Model in Entity Developer and simply call it from code like in the following code snippet:

Code: Select all

decimal? d = null;
db.SelectAutoincSeq(ref d);