How can I learn the credentials for the Connection String in optionsBuilder.UseOracle(@"User Id=<>;Password=<>;Data Source=<>")?

Question

I want to connect from my codebase which is console C# project to an Oracle database.
I am using Entity Framework Core 6.0:

    using Microsoft.EntityFrameworkCore;
    Console.WriteLine("Hello, World!");
    BloggingContext oracleContext = new BloggingContext();
    Console.WriteLine(oracleContext.Database.CanConnect());
    public class BloggingContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle(@"User Id=userid;Password=password;Data Source=localhost:1521/ORACLE19");
    }

The code compiled successfully but no connection is established,
I am getting False in the next output line when checking oracleContext.Database.CanConnect(). What credetials should I put into UseOracle("...") connection string, instead of those shown? I tried everything!
My Oracle database is on my local computer and working well, because I can connect with it through such connection tools as SQL*plus, RaizorSQL, PL/SQL Developer and see its contents.
How can I learn the right connection string for UseOracle("..."), using these tools or somehow otherwise? What are these User Id, Password, Data Source?

Answer 1

After two weeks of trials and errors I have eventually got the successful connection while acquiring knowledge about Oracle database system. The trick for the right connection string is as follows: the string for SERVICE_NAME should be one could see as the sqlplus answer to the command show parameter service_name
as EdStevens kindly pointed out or looked up in the file tnsnames.ora. On my system it is oracle19.errdonald.net. But in this case the name of the user for User Id should start with prefix C## because it is supposed to be a common user in the Oracle system container CDB. Otherwise, if the user is local and created in a pluggable database pdb, suppose its name is pdb1, then the string for SERVICE_NAME should start with pdb1 prefix. And the most crucial thing for me was to discover that the Password for the user has to contain no less than two capital letters to be valid! So the working code looks like this on my system:

using Microsoft.EntityFrameworkCore;
Console.WriteLine("Hello, World!");
BloggingContext oracleContext = new BloggingContext();
Console.WriteLine(oracleContext.Database.CanConnect());
public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseOracle(@"User Id=C##blog;Password=*********SS;Data Source=localhost:1521/oracle19.errdonald.net");
}

Or,the connection string might be:

optionsBuilder.UseOracle(@"User Id=blog;Password=*********SS;Data Source=localhost:1521/pdb1.errdonald.net");

if the user blog is in the pluggable database pdb1.
*******SS means that the password may be whatsoever but contain no less than 2 capital letters, SS, for example and be no shorter than 9 characters.

Answer 2

"What's SERVICE_NAME?"

You said you can connect with sqlplus, and the database is on your local machine. So . . .

[oracle@vbol83-01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 1 09:04:52 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl
SQL>

Or, checking to see what service names the listener is supporting:

[

oracle@vbol83-01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-APR-2022 09:06:37

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                14-MAR-2022 18:08:17
Uptime                    17 days 14 hr. 58 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vbol83-01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbol83-01.localdomain)(PORT=1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "d21995589d8b1045e0556f99782067a1" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb01" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

So, on my sysetem, the service name is 'orcl'. It may be different on yours.