Connect C# to Oracle With Entity Framework Core
Download and activate dotConnect for Oracle
30-day free trial version
Download and install dotConnect for Oracle directly on your machine, or install the Devart.Data.Oracle NuGet package.
No license key is required, and you can start exploring the product immediately.
Full version
After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.
To activate a connection in your application, add the License Key to your connection string.
Create an EF Core model from Database First
Once you have configured the database, you can move on to the next step—creating an EF Core model. You can do this in two ways: using Scaffold-DbContext or via Entity Developer.
Create an EF Core model via Entity Developer
1. Right-click anywhere inside Solution Explorer and select Add > New Item.
2. Go to Installed > C# Items > Data, select Devart EF Core Model, and click Add.
3. In the Create Model Wizard, select Database First and click Next.
4. Fill in the details of your Oracle database connection and click Next.
5. Select Generate From Database and click Next.
6. Choose the database objects you want to scaffold. You can select all, but since we've been using the Employees table, let's select only it.
7. Define the naming convention for the property names in the database object and click Next. We suggest keeping the default settings this time.
8. On the next page, configure the necessary settings, including file selection for saving the connection, and defining the names of DbContext classes. Select your EF Core version and .NET target framework, then click Next.
9. Choose the model diagram contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, select All Entities and click Next.
10. Choose code generation templates for your objects. You can define parameters according to your preferences or apply default settings. For this tutorial, use the default settings. Click Next.
11. Your model is ready now. Click Finish.
The model you created opens.
The DbContext class has been created.
Create an EF Core model using Scaffold-DbContext
You can use Scaffold-DbContext to generate DbContext and entity classes for your Oracle database. Run the following command, replacing values with your actual credentials:
Scaffold-DbContext "DataSource=127.0.0.1;Port=1521;SID=XE;UserId=TestUser;Password=TestPassword;LicenseKey=**********;" -provider Devart.Data.Oracle.Entity.EFCore -OutputDir Models
After you execute this command, the ModelContext file and the Models folder containing the table entity classes get generated.
Connect to Oracle and retrieve data
This section shows how to connect to an Oracle database and retrieve the first 10 rows from the Employees table using EF Core. We'll query the data and display it in the console for verification.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new DataModel())
{
var employees = context.EMPLOYEEs
.Take(10)
.ToList();
Console.WriteLine("First 10 Employees:");
Console.WriteLine("------------------");
foreach (var employee in employees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, Hire Date: {employee.HIREDATE:yyyy-MM-dd}, Salary: {employee.SALARY:C}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
Run the application. If everything is set up correctly, the console will display the first 10 records from the Employees table.
Insert new records using EF Core
Here, we insert a new employee record into the Employees table. EF Core's Add method is used to stage the record, and SaveChanges persists it to the database. The console displays the inserted record for debugging.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new DataModel())
{
var newEmployee = new EMPLOYEE
{
FIRSTNAME = "John",
LASTNAME = "Doe",
EMAIL = "[email protected]",
HIREDATE = DateTime.Now,
SALARY = 75000.00
};
context.EMPLOYEEs.Add(newEmployee);
context.SaveChanges();
Console.WriteLine($"New employee added with ID: {newEmployee.EMPLOYEEID}");
Console.WriteLine();
var employees = context.EMPLOYEEs
.Take(10)
.ToList();
Console.WriteLine("First 10 Employees:");
Console.WriteLine("------------------");
foreach (var employee in employees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, Hire Date: {employee.HIREDATE:yyyy-MM-dd}, Salary: {employee.SALARY:C}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
Sample output:
Update Oracle data using EF Core
This section shows how to update the record with EMPLOYEEID 41 by changing the employee’s first and last name. We retrieve the record, modify the properties, and call SaveChanges to persist the changes.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new DataModel())
{
var employeeToUpdate = context.EMPLOYEEs.FirstOrDefault(e => e.EMPLOYEEID == 41);
if (employeeToUpdate != null)
{
Console.WriteLine($"Before update - ID: {employeeToUpdate.EMPLOYEEID}, Name: {employeeToUpdate.FIRSTNAME} {employeeToUpdate.LASTNAME}");
employeeToUpdate.FIRSTNAME = "UpdatedFirst";
employeeToUpdate.LASTNAME = "UpdatedLast";
context.SaveChanges();
Console.WriteLine($"After update - ID: {employeeToUpdate.EMPLOYEEID}, Name: {employeeToUpdate.FIRSTNAME} {employeeToUpdate.LASTNAME}");
}
else
{
Console.WriteLine("Employee with ID 41 not found.");
}
Console.WriteLine();
var employees = context.EMPLOYEEs
.ToList();
Console.WriteLine("All Employees:");
Console.WriteLine("--------------");
foreach (var employee in employees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, Hire Date: {employee.HIREDATE:yyyy-MM-dd}, Salary: {employee.SALARY:C}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
The console shows the updated record for verification.
Delete Oracle data using EF Core
Finally, we delete the employee with EMPLOYEEID 41 from the Employees table. The record is removed using the Remove method, and SaveChanges commits the deletion.
class Program
{
static void Main(string[] args)
{
try
{
using (var context = new DataModel())
{
var employeeToDelete = context.EMPLOYEEs.FirstOrDefault(e => e.EMPLOYEEID == 41);
if (employeeToDelete != null)
{
context.EMPLOYEEs.Remove(employeeToDelete);
context.SaveChanges();
}
var employees = context.EMPLOYEEs.ToList();
foreach (var employee in employees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, Hire Date: {employee.HIREDATE:yyyy-MM-dd}, Salary: {employee.SALARY:C}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
The console confirms the deletion.
Video tutorial: Connect to Oracle with EF Core
