PostgreSQL ADO.NET Developer Guide - Entity Framework ASP.NET App

This article provides a guide for using Entity Framework (EF) Core in an ASP.NET Core Razor Pages app. With the help of the CData ADO.NET Provider for PostgreSQL, this application will create pages to view, add, delete, and update data from PostgreSQL.

NOTE: While this article refers to PostgreSQL data, the principles can be applied to any of the 270+ data sources CData supports.

Prerequisites

This guide requires the following parameters:

Guide

  1. Create the application
    1. Open Visual Studio and create a new “ASP.NET Core Web App (Razor Pages)” project.
    2. Name your project “WebAppPostgreSQL”, so that the namespaces will match if you copy and paste code from this guide, and then select “Next”.
    3. On the next page, select “.NET 8.0 (Long-term support)” and then click “Create”.
  2. Install Entity Framework and add references to the required assemblies
    1. Use the NuGet Package Manager to add the packages listed below to your project:
      1. Microsoft.EntityFrameworkCore
      2. Microsoft.EntityFrameworkCore.Design
      3. Microsoft.EntityFrameworkCore.SqlServer
      4. Microsoft.EntityFrameworkCore.Tools
    2. Add a reference to System.Data.CData.PostgreSQL.dll, located in the lib -> net8.0 subfolder in the installation directory (ex. "C:\Program Files\CData\CData ADO.NET Provider for PostgreSQL 2024\").
    3. Add a reference to CData.EntityFrameworkCore.PostgreSQL.dll, located in the lib -> net8.0 -> EFCORE80 subfolder in the installation directory.
    4. Add the included licensing file (.lic) to your project. You can do this by right-clicking the project in the Solution Explorer and then navigating to Add -> Existing Item -> System.Data.CData.PostgreSQL.lic (located in the lib -> netstandard2.0 subfolder in your installation directory). Finally, right-click the licensing file, and set the Copy to Output Directory property to Copy if newer.
    5. Build the project to complete the setup for using EF Core.
  3. Reverse Engineering (Scaffolding) the data model

    Scaffolding is performed using the Package Manager Console (PMC), so open the PMC and use the following command to scaffold the schema into your Models folder. This command automatically constructs classes for all tables/views available. It also creates a context class that extends DbContext and exposes the DbSet properties that represent the tables in the data source:

    Scaffold-DbContext "Authscheme=Password;Server=YourServer;Port=YourPort;User=YourUser;Password=YourPassword;Database=YourDatabase;Tables=HappinessData;" CData.EntityFrameworkCore.PostgreSQL -OutputDir Models -Context PostgreSQLContext

    Note: We set the Tables property to restrict the number of tables we connect to and model. This will only model the HappinessData table from the PostgreSQL connection instead of every available table and view.

    Read more about the CData PostgreSQL ADO.NET Provider connection properties on the CData Documentation page: CData PostgreSQL ADO.NET Provider Documentation

  4. Add a Razor Page for the HappinessData
    1. Create a new "HappinessData" subfolder in the “Pages” folder of your project.
    2. In the Solution Explorer, right-click the new “HappinessData” folder and select “Add” > “Razor Page…”.
    3. In the Razor Page dialog, select “Razor Page using Entity Framework (CRUD)” and click "Add".
    4. In the “Razor Page using Entity Framework (CRUD)” dialog:
      1. Set “Model class” to the HappinessData entity, "HappinessDatum"
      2. Set “DbContext class” to the database context, “PostgreSQLContext”.
    5. Click “Add” to create the new pages. This will create Razor pages in the Pages/HappinessData folder:
  5. Add Connection String in the appsettings.json file

    In the appsettings.json file, you will need to add the connection string for the PostgreSQL connection. Below is an example of the code:

    { "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "AllowedHosts": "*", "ConnectionStrings": {"PostgreSQLContext": "Authscheme=Password;Server=YourServer;Port=YourPort;User=YourUser;Password=YourPassword;Database=YourDatabase;Tables=HappinessData;"} }
  6. Register the context in Program.cs

    In the Program.cs file, you will need to register the context class with the dependency injection container. Below is an example of the code:

    using WebAppPostgreSQL.Models; using Microsoft.EntityFrameworkCore; var builder = WebApplication.CreateBuilder(args); // Add services to the container. builder.Services.AddRazorPages(); builder.Services.AddDbContext<PostgreSQLContext>(options => options.UsePostgreSQL(builder.Configuration.GetConnectionString("PostgreSQLContext")));
  7. Adjust the layout of the HappinessData list

    For ease of use, we will adjust the layout of the HappinessData page so that the controls to Edit or Delete a country are easily accessible. By default, the column with these controls is listed last, so we will adjust the code in the Index.cshtml file to make this column the first one displayed. In this code, we added an empty header for the column and moved the Edit and Delete controls to the first column listed.

    @page @model WebAppPostgreSQL.Pages.HappinessData.IndexModel @{ ViewData["Title"] = "Index"; }

    Index

    Create New

    … … @foreach (var item in Model.HappinessData) { … … }
    Edit | Delete
  8. Add the Razor page to your layout

    With the Razor page fully set up, we can remove the generic pages that were available when the project was created and replace them with a link to the new page in the _Layout.cshtml file. To do this, delete the current code and paste the following code into the Pages/Shared/_Layout.cshtml file. Below is an example of the adjustments made:

    <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>@ViewData["Title"] - WebAppPostgreSQL</title> <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" /> <link rel="stylesheet" href="~/css/site.css" asp-append-version="true" /> <link rel="stylesheet" href="~/WebAppPostgreSQL.styles.css" asp-append-version="true" /> </head> <body> <header> <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3"> <div class="container"> <a class="navbar-brand" asp-area="" asp-page="/Index">WebAppPostgreSQL</a> <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target=".navbar-collapse" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="navbar-collapse collapse d-sm-inline-flex justify-content-between"> <ul class="navbar-nav flex-grow-1"> <li class="nav-item"> <a class="nav-link text-dark" asp-area="" asp-page="/Index">Home</a> </li> <li class="nav-item"> <a class="nav-link text-dark" asp-area="" asp-page="/HappinessData/Index">Happiness Data</a> </li> </ul> </div> </div> </nav> </header> <div class="container"> <main role="main" class="pb-3"> @RenderBody() </main> </div> <footer class="border-top footer text-muted"> <div class="container"> &copy; 2025 - WebAppPostgreSQL - <a asp-area="" asp-page="/Privacy">Privacy</a> </div> </footer> <script src="~/lib/jquery/dist/jquery.min.js"></script> <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script> <script src="~/js/site.js" asp-append-version="true"></script> @await RenderSectionAsync("Scripts", required: false) </body> </html>
  9. Run the application

    With everything set up, you can now run the completed PostgreSQL-connected application. To do this, first, build the solution then use the green play button to start the application. This should open the home screen for the application, and you can click the Happiness Data tab to view your list of countries, as shown below:

    On this same page, select the Create New button to open the page to create a new entry in your PostgreSQL table:

    Additionally, on the same page, select the Edit or Delete buttons next to an entry to open the pages that allow you to edit or delete the entry selected:

Unlock seamless data connectivity in your .NET applications

Are you looking to unlock the full potential of your PostgreSQL data? CData’s PostgreSQL ADO.NET Provider can seamlessly integrate your .NET applications with your PostgreSQL data. With the CData ADO.NET Provider for PostgreSQL Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building PostgreSQL-connected apps today!