Linqpad 6 Oracle via EF Core

dmeeze

Hi all,

I'm trying to get my new Linqpad 6 connected to an oracle database using the new native EF core behaviour, and having some issues.

I *can* connect as SYSDBA and see all the SYS tables. That means it's not plumbing specifically - the port, service, and so on all work and allow me to correctly access the DB. I can also log in and connect using my application credentials.

However if i try to access any tables in my application schema (in either case) I get nothing. The table browser treeview says ...loading... for a few seconds then just ends. I presume it either cannot find any tables, or there is an unsupported or broken table/something in my schema.

I'm wondering how i can best debug this process? how do i see logs, errors, issues etc in the "....loading..." phase of enumerating all the tables in my database?

my scenario: Oracle running in local docker container hence connecting on localhost:15XX. service name of foo.mycompany.com sid of foo. user data schema of FOO_APPDATA

Cheers!
-d

JoeAlbahari

Try running the following query with the language set to SQL:

SELECT con.CONSTRAINT_TYPE, con.OWNER, con.CONSTRAINT_NAME, con.R_OWNER, con.R_CONSTRAINT_NAME, col.TABLE_NAME, col.COLUMN_NAME, col.POSITION
FROM ALL_CONS_COLUMNS col, ALL_CONSTRAINTS con
WHERE con.OWNER in ('HR')
	AND con.CONSTRAINT_TYPE IN ('P', 'U', 'R')
	AND col.OWNER = con.OWNER
	AND col.CONSTRAINT_NAME = con.CONSTRAINT_NAME 
	AND col.POSITION IS NOT NULL

go

SELECT OWNER, VIEW_NAME FROM ALL_VIEWS WHERE OWNER IN ('HR')

go

SELECT
	OWNER as Owner, TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName, COLUMN_ID AS ID, DATA_TYPE AS DataType, DATA_LENGTH AS Length,
	DATA_PRECISION AS Precision, DATA_SCALE AS Scale, NULLABLE AS Nullable, CHAR_USED, CHAR_LENGTH AS LengthInChars,
	DATA_DEFAULT AS DefaultValue, VIRTUAL_COLUMN AS VirtualColumn
FROM ALL_TAB_COLS WHERE OWNER IN ('HR')
ORDER BY TABLE_NAME, ID

(You will need to replace 'HR' with your schema name.)

Does it display your schema?

gb123

I have the same problem-- connecting as a read-only user, I don't see my expected schema if I specify it or leave that field blank.

@JoeAlbahari 's SQL query returns results for the 2nd & 3rd query, but 0 rows for the first.

Edit: I *can* see some tables in the SYS schema, same as @dmeeze

JoeAlbahari

Is there any way that you can provide steps for me to reproduce a database with this problem? (Bear in mind that I don't know much about Oracle.)

jomtois

I'm having a similar issue with Oracle in Linqpad v.6. I reinstalled linqpad 5 with the IQ drivers and all of the expected tables and views display ( count of 42). If I open the same connection in Linqpad 6, it will only show 4 tables and no views. I have tried manually adding the SCHEMA name and everything else I can think of as options to no avail but I think there must be something wrong in how linqpad 6 is not picking things up correctly vs. how it used to work with the IQ drivers. Running the SQL commands above in Linqpad 6 did list the expected tables, so I do not think it is an Oracle config thing (also works correctly on my machine not in linqpad). I don't know much about Oracle myself so I cannot be too helpful beyond that.

In the meantime I'm running 5 and 6 side by side - 5 for Oracle and 6 for net core stuff. I will keep an eye on any developments.

Thanks

JoeAlbahari

Try running the following query:

http://share.linqpad.net/t7hwq2.linq

You'll need to change the query's connection to your Oracle connection.

It should display a list of tables and their columns. Can you tell me what you get? (And if you don't get the tables you expect, can you determine what's causing this in the code?)

jomtois

Thank you for your script. I ran it with the user and connection I am having trouble with.

When run in linqpad 6 under the suspect connection, although linqpad only displays 4 tables in the connection tree, the query returns 13. There are also 13 tables returned in linqpad 5 connection tree using the IQ driver and same oracle connection information. The thing that I notice is that in your query results there is a column "HasKey" which is true only for the 4 tables showing up in the tree, so perhaps that column is influencing what is displayed. There is nothing else in the columns that differentiate those tables from the ones that are not displayed that I can tell.

Additionally, in the linqpad 5 using the IQ drivers, I receive back many Views from this connection as well. I do not get back any views using your script (which may be expected), nor in this tree in linqpad 6.

Maybe that gives you some clues. Let me know if there is anything else I can check and I appreciate you time.

JoeAlbahari

That will be it: With EF Core 2.x, you can't query tables that don't have a primary key. This includes views.

Oracle are planning to support EF Core 3 in the future, which doesn't have that limitation:
https://community.oracle.com/thread/4286326

In the meantime, the workaround is to use LINQPad 5 which uses the IQ driver and DevArt connectors.