Connect C# to Oracle with Entity Framework Core
Why dotConnect for Oracle?
dotConnect for Oracle is the ideal data provider for all Oracle-related operations. It offers on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.
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 a .NET project
1. Open Visual Studio and select Create a new project. In the search box, enter ASP.NET Core Project, select the corresponding search result, and click Next.
2. Give your project a name. For this tutorial, let it be Oracle_EF_Core.
3. Select .NET 8 as the target framework and click Create.
4. To install dotConnect for Oracle in your project, go to Tools, point to NuGet Package Manager, and select Manage NuGet Packages for Solution.
5. Click Browse and search for Devart.Data.Oracle.EFCore. Select the matching result, then select your project, and click Install.
6. Run the application by pressing F5 to open the scaffolded webpage.
Create an EF Core model
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 using Scaffold-DbContext
1. Ensure the EF Core Tools are installed in your development environment. To install them globally, run this command in the .NET command-line interface (CLI):
dotnet tool install --global dotnet-ef
2. Install Scaffold-DbContext using the Package Manager Console. For this, in Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console and run this command:
Install-Package Microsoft.EntityFrameworkCore.Tools
3. When the package is installed, you can use Scaffold-DbContext to generate DbContext and entity classes for your Oracle database. Run the following command in the Package Manager Console, 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. You should see them in the Solution Explorer. This means our migration has been applied, and the Actor table has been created.
Create an EF Core model via Entity Developer
1. Before installing Entity Developer, close your Visual Studio instance.
2. Install Entity Developer following the on-screen instructions.
3. Open the Oracle_EF_Core project in Visual Studio again.
4. Right-click the Oracle_EF_Core project in the Solution Explorer and select Add > New Item.
5. Go to Installed > C# > ASP.NET Core > Data, select Devart EF Core Model, and click Add.
6. In the Create Model Wizard, select Database First and click Next.
7. Fill in the details of your Oracle database connection and click Next.
8. Select Generate From Database and click Next.
9. Choose the database objects you want to scaffold. You can select all, but since we've been using the Actor table, let's select only it.
10. Define the naming convention for the property names in the database object and click Next. We suggest keeping the default settings this time.
11. On the Model properties page, change only the Target Framework field. Select .NET 8 (or a different framework your project uses) and click Next.
12. 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.
13. Choose code generation templates for your objects. You can define different parameters you want the object to follow. Let's use the default settings for this tutorial. Click Next.
Your model is ready now.
14. Clear the Download 'Devart.Data.Oracle.EFCore' NuGet package checkbox because we've already added this package to the project; then, click Finish.
Your created model opens.
Now, move on to creating a connection class.
Create a connection class
Using the generated context, let's create the services we'll use with our database.
1. Right-click the Oracle_EF_Core project, select Add > Class, and add a class named DatabaseConnectionClass.
2. Paste this code into the created file:
public class DatabaseConnectionClass
{
private readonly ModelContext _context;
public DatabaseConnectionClass()
{
_context = new ModelContext();
}
public decimal AddActor(Actor actor)
{
actor.LastUpdate = DateTime.Now;
_context.Actors.Add(actor);
_context.SaveChanges();
return actor.ActorId;
}
public bool DeleteActor(int Id)
{
var actor = _context.Actors.FirstOrDefault(a => a.ActorId == Id);
if (actor != null)
{
_context.Actors.Remove(actor);
_context.SaveChanges();
return true;
}
return false;
}
public IEnumerable<Actor> GetActorById(int Id)
{
return _context.Actors.Where(a => a.ActorId == Id).ToList();
}
public List<Actor> GetActors()
{
return _context.Actors.ToList();
}
public Actor UpdateActor(Actor actor)
{
actor.LastUpdate = DateTime.Now;
_context.Actors.Update(actor);
_context.SaveChanges();
return actor;
}
}
Read data from Oracle
Let's try to read data from the database table using the implementation we've just created.
1. In the Controllers folder, add a new class named ActorsController.
2. In the created file, inside namespace Oracle_EF_Core.Controllers, add the following code:
[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
private readonly DatabaseConnectionClass _context;
public ActorsController()
{
_context = new DatabaseConnectionClass();
}
[HttpGet]
public IEnumerable<Actor> GetActors()
{
return _context.GetActors();
}
}
3. Run your application by pressing F5.
4. Go to the GetActors endpoint, click Try it out, and then click Execute.
The query result appears in the response body.
Insert new data into Oracle
Next, let's try to insert a new record into the Actors table.
1. In the ActorsController class, add this piece of code:
[HttpPost]
public decimal AddActor(Actor actor)
{
return _context.AddActor(actor);
}
This uses the helper method in DatabaseConnectionClass to insert a new record.
2. Run the application again. We can see that the new endpoint—AddActor—has appeared.
3. Click the expander arrow and add this JSON object to the request body:
{
"actorId": 201,
"firstName": "John",
"lastName": "Lewis",
"filmActors": []
}
4. Click Execute.
The response should be the ID you assigned to the added data.
Update Oracle data
Imagine that John Lewis has decided to change his surname. Let's update the data.
1. In the ActorController class, add the following method:
[HttpPut]
public Actor UpdateActor(Actor actor)
{
return _context.UpdateActor(actor);
}
This uses the helper method in DatabaseConnectionClass to update a particular record.
2. Run the application, select UpdateActor, and add this JSON object to the request body:
{
"actorId":201,
"firstName": "John",
"lastName": "Mcginnis",
"filmActors": []
}
3. Click Execute.
In the response body, you'll see the record with the updated last name.
Delete data from Oracle
Assume that John has left the acting business. Let's delete his record from the table.
1. Add these two methods to the ActorController class:
[HttpDelete("{Id}")]
public bool DeleteActor(int Id)
{
return _context.DeleteActor(Id);
}
[HttpGet("{Id}")]
public IEnumerable<Actor> GetActorsById(int Id)
{
return _context.GetActorById(Id);
}
The DeleteActor method will delete a row from the table and return true if the request is successful. If no record for that particular ID exists or an error occurs, it'll return false.
The GetActorsById method will verify the actor no longer exists.
2. Run the application, select the DeleteActor endpoint, and enter the ID of the previously created record.
3. Click Execute.
This should return true, and the record gets removed.
4. To verify this, try to get the record with that ID using the GetActorsById endpoint.
The request returns an empty array, which means the record no longer exists.