EF Core/NorthWind for Oracle
Tags: csharp, dotnetcore, database, codenewbie
Published: 2024-03-02 23:33:35

## Introduction


Learn how to work with Oracle database with Microsoft [Entity Framework Core](https://learn.microsoft.com/en-us/ef/core/) (at this time version 8) database first using [EF Power Tools](https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools) to reverse engineer tables in the database. The database has table names and column names uppercased which will need to be addressed to conform to current coding standards. Rather than data annotations [FluentValidation](https://docs.fluentvalidation.net/en/latest/) NuGet package is used for validation.

**Note** that not all operations are provided in source code but have been stubbed out. This is because the database is hosted on a server that I can not do backups and restore operations. Also, anything with could use dropdowns are simply inputs. For learning how to code dropdowns see [Add Select option to a Dropdown](https://dev.to/karenpayneoregon/razor-pages-add-select-option-to-a-dropdown-48m9) article with source code.

---
> **Note**
> Make sure to read the section Connection Strings which explains setting up the database connection string.

---

> **Note**
> [northwind_export.sql](https://github.com/karenpayneoregon/oracle-northwind2024/blob/master/NorthwindOracle_DDL-master/northwind_export.sql) in the root of the Visual Studio solution contains the database structure and data for each table.

---

## What was used to create code

At the time of this article was written, Microsoft Visual Studio 2022, EF Core 8 were used. For newer releases of EF Core unless there are breaking changes this code should still work.

---

## Logging

One aspect that is critical to learning how EF Core works is to examine the queries generated. There are two ways, the first, view the queries in Visual Studio output window which go away while the second is from queries written to the log file under the debug folder. For the file logs, one file per day rather than one massive log file. See the project file OracleNorthWind2024 for how folders are created.

```xml
<PropertyGroup>
   <CurrentDate>$([System.DateTime]::Now.ToString(yyyy-MM-dd))</CurrentDate>
</PropertyGroup>
<Target Name="MakeLogDir" AfterTargets="Build">
   <MakeDir Directories="$(OutDir)LogFiles\$(CurrentDate)" />
</Target>
```

{% cta https://github.com/karenpayneoregon/oracle-northwind2024/tree/master %} Source code {% endcta %}

---

## Database setup

The Oracle NorthWind database is the original version which is not fully normalize but will be fine for learning how to work with EF Core and FluentValidation.

For the category table, no images were provided. The current images came from exporting images from the SQL-Server version of the NorthWind database. The only place category images are used is in the Windows Forms project, they could also be used in the ASP.NET Core project using the same technique used in the Windows Form project with adjustment to were the images are stored.

1. Create the NorthWind database on your Oracle server
1. Run the provided script found under solution items in the provided source code.

## Reverse engineering the database

EF Power Tools was used to create POCO classes. Once EF Power tools is installed, right click on a project, select new database, fill in the required fields to connect then select the tables. If EF Core NuGet package is not installed EF Power Tools will install the package. Note, there are many options that should be explored for EF Power Tools but for now the above is all that is needed.

---

## Oracle table and column names

The table and column names are all uppercased which are fine for some developer while not for others. For this article using Table and Column attributes are used to case the table and column names as shown below. If using ReSharper, ReSharper will assist with the naming process.

```csharp
[Table("CATEGORIES")]
public partial class Categories
{
    [Column("CATEGORY_ID")]
    public int CategoryId { get; set; }

    [Column("CATEGORY_NAME")]
    public string CategoryName { get; set; }

    [Column("DESCRIPTION")]
    public string Description { get; set; }

    [Column("PICTURE")]
    public string Photo { get; set; }

    /// <summary>
    /// Used in OracleWinFormsApp2024 project to
    /// read images from a local folder
    /// </summary>
    [NotMapped]
    public Image Picture { get; set; }

    public override string ToString() => CategoryName;

    public virtual ICollection<Products> Products { get; set; } 
        = new List<Products>();
}
```

---

## Projects

NorthWindOracleLibrary class project contains the models, validators, interfaces and language extensions for use in the ASP.NET Core project OracleNorthWind2024 and can be used in other projects such as projects the reader creates to learn from.

---

## Contracts

The following interface is used to ensure continuity for all the different models. Only a handful are used, feel free once comfortable fill in the others and if there are issues post them in the GitHub repository and they will be addressed.

```csharp
public interface IOperations<T> where T : class
{
    IEnumerable<T> GetAll();
    Task<List<T>> GetAllAsync();
    T GetById(int id);
    T GetByIdWithIncludes(int id);
    Task<T> GetByIdAsync(int id);
    Task<T> GetByIdWithIncludesAsync(int id);
    bool Remove(int id);
    void Add(in T sender);
    void Update(in T sender);
    int Save();
    Task<int> SaveAsync();
}
```

---

## Dependency Injection

Rather than place all registration in Program.cs, registration is placed in a language extension method as follows. This keeps Program.cs clean and will note that some developer do not care for this and if a reader is one who does not care for this feel free to move the following to Program.cs.

```csharp
public static class DIRegistrations
{
    public static IServiceCollection RegisterServices(this IServiceCollection services)
    {

        services.AddScoped(typeof(IOperations<Products>), typeof(ProductsService));
        services.AddScoped(typeof(IOperations<Customers>), typeof(CustomersService));
        services.AddScoped<IValidator<Customers>, CustomersValidator>();
        services.AddScoped<IValidator<Products>, ProductsValidator>();
        services.AddScoped<IValidator<Shippers>, ShippersValidator>();

        services.AddFluentValidationAutoValidation();

        return services;

    }
}
```
**Program.cs**

```csharp
public class Program
{
    public static void Main(string[] args)
    {
        var builder = WebApplication.CreateBuilder(args);

        builder.Services.AddRazorPages();

        builder.LoggingSetup();

        builder.DatabaseSetup();

        builder.Services.RegisterServices();

        builder.Services.AddExceptionHandler<DefaultExceptionHandle>();
        builder.Services.AddProblemDetails();

        var app = builder.Build();

        if (!app.Environment.IsDevelopment())
        {
            app.UseHsts();
        }

        app.UseExceptionHandler();
        app.UseHttpsRedirection();
        app.UseStaticFiles();

        app.UseRouting();
        app.UseAuthorization();
        app.MapRazorPages();
        app.Run();
    }
}
```

- builder.LoggingSetup() sets up SeriLog to write to a file and console for development and for other environments just the log file.
- builder.DatabaseSetup() sets up EF Core to work with Oracle and to log to a file. For development sensitive information is logged but not for other environments. Note that the connection string is stored in appsettings.json which the reader needs to adjust.
- builder.Services.AddExceptionHandler&lt;DefaultExceptionHandle>() overrides the default error handler.

## Reading data from a table

The following read data from the employees table.


![Shows employee data in a HTML table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/skaho73jn73y801z9n5t.png)

```csharp
public class EmployeesPageModel : PageModel
{
    private readonly Context _context;
    public IEmployeesService EmployeesService { get; }

    [BindProperty]
    public IList<Employees> Employees { get; set; } = default!;

    public EmployeesPageModel(Context context)
    {
        _context = context;
        EmployeesService = new EmployeesService(_context);
        Employees = EmployeesService.GetEmployees();
    }
    public void OnGet()
    {
    }
}
```

### Breaking down the code

The following class **EmployeesService** has methods for interacting with the NorthWind database. In the constructor, our DbContext is passed in which is initialized in the call page then in the above page GetEmployees method retrieves all employees.

> **Note**
> There are more methods that are in the GitHub source

```csharp
public class EmployeesService : IEmployeesService
{
    private readonly Context _context;

    public EmployeesService(Context context)
    {
        _context = context;
    }

    public List<Employees> GetEmployees() 
        => _context.Employees.ToList();

	...
}
```
### Frontend

There is nothing special here, the list from above is used to dynamically create a simple HTML table.

```html
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Employees[0].FirstName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Employees[0].LastName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Employees[0].Country)
            </th>

        </tr>
    </thead>
    <tbody>

        @for (int index = 0; index < Model.Employees.Count; index++)
        {
            <tr>
                <input type="hidden" asp-for="Employees[index].EmployeeId" />
                <td>
                    <input type="hidden" asp-for="Employees[index].FirstName" />
                    @Model.Employees[index].FirstName
                </td>
                <td>
                    <input type="hidden" asp-for="Employees[index].LastName" />
                    @Model.Employees[index].LastName
                </td>
                <td>
                    <input type="hidden" asp-for="Employees[index].Country" />
                    @Model.Employees[index].Country
                </td>
            </tr>
        }

    </tbody>
</table>
```

---

## Editing an employee

For this, an employee is read from the employee table using the same service used above, in the same page.

```csharp
Employees employee = EmployeesService.GetById(1)!;
```
Then the following logic update the first name property.

```csharp
if (employee is not null)
{
    if (employee.FirstName.EndsWith("1"))
    {
        employee.FirstName = "Nancy";
    }
    else
    {
        employee.FirstName = "Nancy1";
    }

    var (result, exception) = EmployeesService.Update(employee);
    if (result)
    {
        Log.Information("Update successful");
    }
    else
    {
        Log.Error(exception, "Update failed");
    }
}
```

Although the update works, check out what was logged. All columns are updated.

```plsql
DECLARE
v_RowCount INTEGER;

BEGIN
UPDATE "NORTHWIND"."EMPLOYEES" SET "ADDRESS" = :p0, "BIRTHDATE" = :p1, "CITY" = :p2, "COUNTRY" = :p3, "TITLE_OF_COURTESY" = :p4, "EXTENSION" = :p5, "FIRSTNAME" = :p6, "HIREDATE" = :p7, "HOME_PHONE" = :p8, "LASTNAME" = :p9, "NOTES" = :p10, "PHOTO" = :p11, "POSTAL_CODE" = :p12, "REGION" = :p13, "REPORTS_TO" = :p14, "TITLE" = :p15
WHERE "EMPLOYEE_ID" = :p16;
v_RowCount := SQL%ROWCOUNT;
OPEN :cur0 FOR SELECT v_RowCount FROM DUAL;

END;
```

Although the likelihood we would just update only first name but instead first and last name let's keep it simple and indicate that EF Core should only update FirstName with the following method.

```csharp
public (bool, Exception exception) UpdateFirstNameOnly(Employees employees)
{
    try
    {
        _context.Employees.Attach(employees);
        UpdateSpecificField(employees, x => x.FirstName);
        var affected = _context.SaveChanges();
        return (affected == 1, null)!;
    }
    catch (Exception localException)
    {
        return (false, localException);
    }
}

/// <summary>
/// Used to set individual propertie(s) of an entity
/// </summary>
/// <param name="entity">Valid Employee</param>
/// <param name="updatedProperties">List of properties</param>
public void UpdateSpecificField(Employees entity, params Expression<Func<Employees, object>>[] updatedProperties)
{
    foreach (var property in updatedProperties)
    {
        _context.Entry(entity).Property(property).IsModified = true;
    }
}
```

UpdateSpecificField method is called to tell EF Core to only update FirstName. Here is the query used to update FirstName.

```plsql
DECLARE

v_RowCount INTEGER;

BEGIN

UPDATE "NORTHWIND"."EMPLOYEES" SET "FIRSTNAME" = :p0
WHERE "EMPLOYEE_ID" = :p1;
v_RowCount := SQL%ROWCOUNT;
OPEN :cur0 FOR SELECT v_RowCount FROM DUAL;

END;
```

Suppose we want to update FirstName and LastName. Change the following.

```csharp
UpdateSpecificField(employees, x => x.FirstName);
```

To

```csharp
UpdateSpecificField(employees, x => x.FirstName, x => x.LastName);
```

The resulting query.

```plsql
DECLARE

v_RowCount INTEGER;

BEGIN

UPDATE "NORTHWIND"."EMPLOYEES" SET "FIRSTNAME" = :p0, "LASTNAME" = :p1
WHERE "EMPLOYEE_ID" = :p2;
v_RowCount := SQL%ROWCOUNT;
OPEN :cur0 FOR SELECT v_RowCount FROM DUAL;

END;
```

> **Note**
> There is a log file generated for all EF Core operations. Check the log out and note that since the environment is development values are shown e.g.

```
Executed DbCommand (46ms) 
	[Parameters=
		[:p2='1', :p0='Nancy1' (Nullable = false) (Size = 10) (DbType = AnsiString), 
		 :p1='Davolio' (Nullable = false) (Size = 20) (DbType = AnsiString), 
		 :cur0=NULL (Nullable = false) (Direction = Output) (DbType = Object)], CommandType='Text', CommandTimeout='0'
	]

```

---

## Validation
 
Using [FluentValidation](https://docs.fluentvalidation.net/en/latest/) rather than data annotations its easier to [create](https://docs.fluentvalidation.net/en/latest/custom-validators.html) or use [built-in](https://docs.fluentvalidation.net/en/latest/built-in-validators.html) rules.

For instance, going back to Employees model. We want to ensure there is a first, last name, title and birth date.

```csharp
public class EmployeesValidator : AbstractValidator<Employees>
{
    public EmployeesValidator()
    {

        RuleFor(customer => customer.FirstName)
            .IsNotNullOrWhitespace()
            .WithMessage("Enter a first name");

        RuleFor(customer => customer.LastName)
            .IsNotNullOrWhitespace()
            .WithMessage("Enter a last name");

        RuleFor(customer => customer.Title)
            .IsNotNullOrWhitespace()
            .WithMessage("Enter a title name");

        RuleFor(customer => customer.BirthDate)
            .GreaterThan(x => new DateTime(1900, 1, 1))
            .WithMessage("Birth date must be greater than 1/1/1900");
    }
}
```

**IsNotNullOrWhitespace** is an extension method included in provided source code.

```csharp
public static class RuleBuilderExtensions
{

     public static IRuleBuilderOptions<T, string> IsNotNullOrWhitespace<T>(this IRuleBuilder<T, string> builder)
        => builder
            .Must(item => item != null && !string.IsNullOrWhiteSpace(item))
            .WithMessage("'{PropertyName}' should not be empty");
}
```

Another example for BirthDate, what is presented works but suppose the rule is needed in other classes, we can create an extension method.

```csharp
public static class RuleBuilderExtensions
{
    public static IRuleBuilderOptions<T, DateTime> BirthDateGreaterThan1900<T>(this IRuleBuilder<T, DateTime> builder)
        => builder
            .Must(item => item > new DateTime(1900,1,1))
            .WithMessage("'{PropertyName}' should not be empty");
}
```
Here is an example of reusability, **IsNotNullOrWhitespace** is used above and below.

```csharp
public class CustomersValidator : AbstractValidator<Customers>
{
    public CustomersValidator()
    {

        RuleFor(customer => customer.CompanyName)
            .IsNotNullOrWhitespace()
            .WithMessage("Enter a company name");

        /*
         * This is needed because of not properly normalizing Customer model
         */
        var contactTitles = ReferenceLists
            .CustomerTitles()
            .Select(x => x.ContactTitle)
            .ToList();

        RuleFor(customer => customer.ContactTitle)
            .IsNotNullOrWhitespace()
            .Must((customer, _) => contactTitles.Contains(customer.ContactTitle))
            .WithMessage("Invalid contact title");

        RuleFor(customer => customer.ContactName)
            .IsNotNullOrWhitespace()
            .WithMessage("Enter a contact name");

        RuleFor(customer => customer.Phone)
            .GeneralPhoneNumber();
    }
}
```
Let's try this out with a mocked customer. Below shows properties that are invalid.

![Shows inputs for customer data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/endufjuirn9nvyzuv5cq.png)

**Backend code**

- Create a valid Customer
- Validate in the post method

```csharp
public class IndexModel : PageModel
{
    [BindProperty]
    public Customers Customers { get; set; } = default!;

    public void OnGet()
    {
        // start with a valid record
        Customers = new()
        {
            ContactTitle = "Owner",
            CompanyName = "ABC",
            ContactName = "Karen Payne",
            Phone = "999-1234"
        };

    }

    public PageResult OnPost()
    {
        if (!ModelState.IsValid)
        {
            return Page();
        }
        else
        {
            // insert new record
            return Page();
        }
    }
}
```

**Frontend code**

```html
<div class="row">
    <div class="col-md-4">
        <form method="post">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <input type="hidden" asp-for="Customers.CustomerId" />
            <div>
                <label asp-for="Customers.CompanyName"></label>
                <input asp-for="Customers.CompanyName" class="form-control" />
                <span asp-validation-for="Customers.CompanyName" class="text-danger"></span>
            </div>
            <div>
                <label asp-for="Customers.ContactTitle"></label>
                <input asp-for="Customers.ContactTitle" class="form-control" />
                <span asp-validation-for="Customers.ContactTitle" class="text-danger"></span>
            </div>
            <div>
                <label asp-for="Customers.ContactName"></label>
                <input asp-for="Customers.ContactName" class="form-control" />
                <span asp-validation-for="Customers.ContactName" class="text-danger"></span>
            </div>
            <div>
                <label asp-for="Customers.Phone"></label>
                <input asp-for="Customers.Phone" class="form-control" />
                <span asp-validation-for="Customers.Phone" class="text-danger"></span>
            </div>
            <div class="mt-2">
                <input type="submit" value="Save" class="btn btn-primary" />
            </div>
        </form>
    </div>
</div>
```

---

## Category images

Not sure how many developer need these images but to ensure they work the easy way to test is in a Windows Forms project.


![Form with DataGridView showing images](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jvkvahaibpi8x1inp6yc.png)

Image names are read from a local folder.

```csharp
public class FileOperations
{
    /// <summary>
    /// Get names of all images for <see cref="Categories"/>
    /// </summary>
    /// <returns>List of file names with full path</returns>
    public static List<string> GetImages()
        => Directory.GetFiles(
                Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NorthImage"))
            .ToList();
}
```

Which are passed to the following method to create actual images using EF Core.

```csharp
internal class CategoriesOperations
{
    public static List<Categories> GetAll()
    {
        var files = FileOperations.GetImages();
        using var context = new Context();
        var categories = context.Categories.ToList();

        for (int index = 0; index < categories.Count; index++)
        {
            byte[] bytes = File.ReadAllBytes(files[index]);
            categories[index].Picture = bytes.BytesToImage();
        }

        return categories;
    }
}
```
That is called in a form to display in a DataGridView where the columns are predefined.

```csharp
public partial class CategoriesForm : Form
{
    public CategoriesForm()
    {
        InitializeComponent();
        dataGridView1.AutoGenerateColumns = false;
        Shown += MainForm_Shown;
    }

    private void MainForm_Shown(object? sender, EventArgs e)
    {
        dataGridView1.DataSource = CategoriesOperations.GetAll();
        dataGridView1.ExpandColumns();
    }
}
```

---

## Displaying data in a table

This is what we want.

![All products in a sticky table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/se7duwise4cdm18f3av5.png)

**Backend code**

- Create an instance of the product service
- Using **GetAll** get all products

```csharp
public class ProductsPageModel : PageModel
{
    private readonly IOperations<Products> _productsService;

    [BindProperty]
    public List<Products> Products { get; set; }
    public ProductsPageModel(IOperations<Products> customersService)
    {
        _productsService = customersService;
        Products = _productsService.GetAll();
    }

    public void OnGet()
    {
    }
}
```

**Frontend code**

- Setup styles for the table to have a sticky header
- Create the table and populate with all products.

```html
@page
@model OracleNorthWind2024.Pages.ProductsPageModel
@{
}

<style>
    .header {
        position: sticky;
        top: 0;
    }

    .tbodyDiv {
        max-height: clamp(5em,40vh,450px);
        overflow: auto;
    }

    .table tbody tr.highlight td {
        background-color: yellow;
    }
</style>

<div class="container">
    <div class="alert alert-primary" role="alert">
        <h1 class="fs-5">See console</h1>
    </div>
</div>

<form method="post">
    <div class="tbodyDiv">
        <table id="infoTable" class="table table-bordered text-start table-striped" data-toggle="table">
            <thead class="sticky-top bg-white">
                <tr>
                    <th>
                        @Html.DisplayNameFor(model => model.Products[0].ProductId)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Products[0].ProductName)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Products[0].Categories.CategoryName)
                    </th>

                </tr>
            </thead>
            <tbody>

                @for (int index = 0; index < Model.Products.Count; index++)
                {
                    <tr>

                        <td>
                            <input type="hidden" asp-for="Products[index].ProductId" />
                            @Model.Products[index].ProductId
                        </td>

                        <td>
                            <input type="hidden" asp-for="Products[index].ProductName" />
                            @Model.Products[index].ProductName
                        </td>

                        <td>
                            <input type="hidden" asp-for="Products[index].Categories.CategoryName" />
                            @Model.Products[index].Categories.CategoryName
                        </td>

                    </tr>
                }

            </tbody>
        </table>
    </div>

    <button type="submit" class="btn btn-primary mt-3">Submit</button>

</form>
```

---

## Simple service

In the following service there are no navigation tables. If a method is not needed a throw is used to indicate it is not available. In several methods there is a save changes which may or may not fit everyone's requirements so if not simply change the code.

```csharp
using Microsoft.EntityFrameworkCore;
using NorthWindOracleLibrary.Data;
using NorthWindOracleLibrary.Interfaces;
using NorthWindOracleLibrary.Models;

namespace NorthWindOracleLibrary.Services;

public class ContactTypeService : IOperations<ContactType>
{
    private readonly Context _context;

    public ContactTypeService(Context context)
    {
        _context = context;
    }

    public List<ContactType> GetAll()
    {
        return _context.ContactType.ToList();
    }

    public Task<List<ContactType>> GetAllAsync()
    {
        return _context.ContactType.ToListAsync();
    }

    public ContactType GetById(int id)
    {
        return _context.ContactType.FirstOrDefault(x => x.ContactTypeIdentifier == id)!;
    }

    public ContactType GetByIdWithIncludes(int id)
    {
        throw new NotImplementedException();
    }

    public async Task<ContactType> GetByIdAsync(int id)
    {
        return (await _context.ContactType.FirstOrDefaultAsync(x => x.ContactTypeIdentifier == id))!;
    }

    public Task<ContactType> GetByIdWithIncludesAsync(int id)
    {
        throw new NotImplementedException();
    }

    public bool Remove(int id)
    {
        ContactType contactType = _context.ContactType.FirstOrDefault(x => x.ContactTypeIdentifier == id)!;
        if (contactType is null)
        {
            return false;
        }
        else
        {
            _context.ContactType.Remove(contactType);
            var affected = _context.SaveChanges();
            return affected == 1;
        }
    }

    public void Add(in ContactType sender)
    {
        _context.Add(sender);
        _context.SaveChanges();
    }

    public void Update(in ContactType sender)
    {
        _context.Attach(sender).State = EntityState.Modified;
        _context.SaveChanges();
    }

    public int Save()
    {
        return _context.SaveChanges();
    }

    public async Task<int> SaveAsync()
    {
        return await _context.SaveChangesAsync();
    }
}
```

The validator is simple as there is only one property to validate.

```csharp
public class ContactTypeValidator : AbstractValidator<ContactType>
{
    public ContactTypeValidator()
    {
        RuleFor(product => product.ContactTitle)
            .NotEmpty()
            .MinimumLength(3)
            .WithMessage("Enter a contact title");
    }
}
```


![Advice](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5k5cb1n7nz6jlf4k043k.png)

To really get in and learn the ins and outs of working with EF Core is to first start with a console or windows forms project, toss away the services and validators and write code as for the following example done (may be a console project, may be a windows forms project. This way there are no distractions such as getting inputs and post operations perfect.

```csharp
ContactType contactType = new ContactType() {ContactTitle = "Manager"};
using var context = new Context();
context.Add(contactType);
var affected = context.SaveChanges();
if (affected == 1)
{
    // set breakpoint to see the id value
}
else
{
    // well no exception, what the heck
}
```

---

## Practice code

There is a Windows Forms project, PracticeApp which is partly done and this is on purpose to allow the reader to work through operations such as adding records which is provided so the reader might want to try adding then deleting or find and replace operations.

One important fact, for this database, Employees table the next identifier must be read via.

```sql
SELECT SEQ_NW_EMPLOYEES.NEXTVAL FROM dual
```
Oracle Developer and Toad can let you see sequences. Or use the following query.

```sql
SELECT * FROM user_sequences;
```

**Results**

![Displays all sequences for NorthWind database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/89e2qv6edbs8w0ohrf91.png)

Adding a new record

- Uses Dapper to get the next id, could use EF Core raw query too.
- Insert new record

> **Note**
> Next step is to bring this code into the EmployeesService in the project NorthWindOracleLibrary.

```csharp
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void AddEmployeeButton_Click(object sender, EventArgs e)
    {
        using var context = new Context();

        Employees employees = new Employees
        {
            FirstName = "Karen",
            LastName = "Adams",
            Title = "Manager",
            TitleOfCourtesy = "Mr",
            BirthDate = new DateTime(1957, 1, 12, 13, 1, 1),
            HireDate = new DateTime(2023, 1, 12, 13, 1, 1),
            Address = "15 Apple Street",
            City = "Portland",
            Region = "PX",
            PostalCode = "97643",
            Photo = "karen.png",
            Notes = "Just added",
            ReportsTo = 2,
        };
        context.Add(employees);
        context.SaveChanges();
    }
}
```

Next, create an override for the DbContext which on add with an entity using the interface IHasSequencer to get the value for the primary key.

---

```csharp
public interface IHasSequencer
{
    int Id { get; set; }
    FormattableString GetSequenceStatement();
}
```

---

```csharp
public override int SaveChanges()
{
    AddNewRecordLogic();

    return base.SaveChanges();
}

public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = new())
{
    AddNewRecordLogic();

    return base.SaveChangesAsync(cancellationToken);
}

/// <summary>
/// Get primary key for adding new record
/// </summary>
private void AddNewRecordLogic()
{
    var addedEntries = ChangeTracker
        .Entries()
        .Where(e => e.State == EntityState.Added);

    foreach (var entry in addedEntries)
    {
        if (entry.Entity is IHasSequencer sequencerEntity)
        {
            sequencerEntity.Id = Database.GetDbConnection()
                .QuerySingle<int>(sequencerEntity.GetSequenceStatement().ToString());
        }
    }
}
```

---

In Employees, _GetSequenceStatement_ has a SELECT to get the next key and assigns the key in the property _Id_ to _EmployeeId_. Note since _Id _ is not part of the model we must tell EF Core this.

```csharp
[NotMapped]
public int Id
{
    get => _id;
    set
    {
        _id = value;
        EmployeeId = value;
    }
}

public FormattableString GetSequenceStatement() 
    => $"SELECT SEQ_NW_EMPLOYEES.NEXTVAL FROM dual";
}
```

## Reference tables

In this version of NorthWind there should be a country and contact type tables unlike my modified version for SQL-Server.

---

**SQL-Server**

![SQL-Server schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vqwehrvzx7sf5l8napmc.png)

---

A quick fix is to create strong typed list which are accessed through a singleton class so that a list is only read once.

```csharp
public sealed class ReferenceData
{
    private static readonly Lazy<ReferenceData> Lazy = new(() => new ReferenceData());
    public static ReferenceData Instance => Lazy.Value;
    public List<Countries> CountriesList { get; private set; }
    public List<ContactType> ContactTypesList { get; private set; }

    private ReferenceData()
    {
        CountriesList = ReferenceLists.CountriesList();
        ContactTypesList = ReferenceLists.ContactTypes;
    }
}
```

Which for example to get a list of countries.

```csharp
ReferenceData.Instance.CountriesList
```

---

## Summary

Information has been provided to get started working with Oracle and Microsoft Entity Framework Core, dependency injection and model validation.

There are gaps in code provided so the reader can practice.

The best way to learn is first if new to dependency injection and model validation to read up on these topics.

For those familiar with dependency injection and model validation, review the code then set breakpoints tracing through the code while inspecting values.

Next steps, fill in the gaps in code and if up to it add in controllers.

Hopefully the information provided is of assistance.


## Connection strings

Are stored in appsettings.json. Once creating the database, add your connection string to `Development` property below.

```json
﻿{
  "ConnectionsConfiguration": {
    "ActiveEnvironment": "Development",
    "Development": "Your connection string",
    "Stage": "Stage connection string goes here",
    "Production": "Prod connection string goes here"
  }
}
```

{% cta https://github.com/karenpayneoregon/oracle-northwind2024/tree/master %} Source code {% endcta %}

{% cta https://github.com/karenpayneoregon %} 🚀 Visit My GitHub Profile {% endcta %}