Select ROWID

[asaake]

In our 4GL we read the Oracle ROWID of a row with a long combined key and use this ROWID later for different selects.

I wonder if there is any way to access the ROWID from LinqToSQL without using a pure SQL string statement?

[AndreyR]

You can work with the ROWID column as with any other column in LINQ to SQL.
I just have made a simple test, ROWID column can be used in the Where clause successfully.

[asaake]

Ok, but where do I get the ROWID from? It isn't in my entity class.

So - I haven't tried it - but I think something like the following will not work:

var rowid1 = (from c in ctx.xyz
where c.col1 == a
&& c.col2 == b
select c.ROWID).FirstOrDefault();

Would you please post your test query for me?

Thanks!

[asaake]

Ok, perhaps my question was unclear.

In your test, did you add a column named ROWID or so to the entity?

If yes, how did you define it?

Or did you test with a string based query like

ctx.ExecuteQuery("select rowid from a where a.key = {0}", b);


Currently I have no idea where to begin with introducing the rowid.

[AndreyR]

I had a table

Code: Select all

CREATE TABLE ROWID_TYPES (
  ID NUMBER(6) PRIMARY KEY,
  F_ROWID ROWID,
  F_UROWID UROWID(4000));

and queried the model using the following code:

Code: Select all

var q = db.RowidTypes.Where(rt => rt.FRowid == "AAAAECAABAAAAeiAAA");
DataContext1.RowidType rtype = q.Single();

You can query in the following way, this works also:

Code: Select all

DataContext1.RowidType rtype = db.ExecuteQuery("select * from Rowid_Types where f_rowid = {0}", "AAAAECAABAAAAeiAAA").Single();

[asaake]

Thanks for your example!

It didn't solve my problem, because it replaces the pseudo column ROWID with a real column.

My question was directed to the following case:

(To mention: we are on Oracle!)

CREATE TABLE ITEM (
ID NUMBER(6) PRIMARY KEY,
DESC VARCHAR2(40),
PRICE NUMBER(10,2));

Now:

SELECT a.DESC, a.ROWID FROM item a
WHERE a.ID = 4711;

How can I get that ROWID?

Do I have to create an entity with a ROWID column it (which doesn't exist in the data dictionary)

or

do I have to query with

var q = ctx.ExecuteQuery("SELECT a.DESC, a.ROWID FROM item a
WHERE a.ID = 4711");

Thanks in advance!

[AndreyR]

Actually, each approach is acceptable.
But I'd recommend you the second one. Select a.DESC and a.ROWID into an anonymous type or an instance of a new custom class and the use the ROWID property in future queries.