Home

Linqpad 6 Oracle via EF Core

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

Comments

  • 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?
  • edited October 2019
    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
  • 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.)
  • 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
  • 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?)
  • 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.
  • edited November 2019
    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.
Sign In or Register to comment.