How to built a cloud native app with Azure Functions to create, design and connect to a Azure Database for PostgreSQL using .Net Core and EntityFramework Core

Philipp Bauknecht

Follow

Nov 29, 2020 · 5 min read

Why?

Azure Functions are a lightweight, inexpensive and scalable alternative to write backend functionality. If you’ve written your backends so far in ASP.NET (Core) and connected to your relational database using EntityFramework (Core) you might wonder if this also works in a C# Function app? Turns out yes with some quirks!

In this sample I’m going to walk through the steps necessary to design a model first database using EntityFramework Core, connect to an Azure PostgreSQL database and provide simple CRUD (create, read, update, delete) functionality as a REST-style http endpoint.

Prerequisites

• Azure Subscription, create your free account with $200 credit here: https://azure.microsoft.com/en-us/free
• Visual Studio 2019, IDE for C# and .NET Core, download the free community edition here: https://visualstudio.microsoft.com/vs/community/
• Azure Functions Tools as part of the Visual Studio installation
• Azure Data Studio, a cross platform database tool, free download here: https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio

Create a new PostgreSQL database server in the Azure Portal

Search for Azure PostgreSQL in the Azure Marketplace to create a new database server. Make sure to pick the offer by Microsoft with the single server option:

For our demo we can choose the cheapest option with the Basic SKU, 1 core and 5 GB storage:

Once the database server has been successfully deployed go to Connection strings and copy the value of ADO.NET:

Next add your client IP address to the firewall rules so you are able to later connect to the database server from your local machine:

Create a new database with Azure Data Studio

Start Azure Data Studio, search and install the PostgreSQL extension by Microsoft:

Go back to connections, click on Add Connection, enter the details from the connections string and click on Connect:

When connected click on New Query, enter

CREATE DATABASE ServerlessDemo

and click on Run. On first access the server takes some seconds to finally create the new database:

Let’s write some code!

With the database ready in the cloud we can finally start with the app by creating a new empty C# Azure Functions app in Visual Studio.

To use EntityFramework Core in a Function app we need some packages from Nuget. So let’s add these:

• Microsoft.Azure.Functions.Extensions, to we can use Dependency Injection
• Microsoft.EntityFrameworkCore (3.1.4)
• Microsoft.EntityFrameworkCore.Design (3.1.4)
• Microsoft.EntityFrameworkCore.Tools (3.1.4)
• Npgsql.EntityFrameworkCore.PostgreSQL (3.1.4), PostgreSQL provider for EntityFramework Core

Important: At the time of writing this only works with version 3.1.4 and NOT with 5.x of the EntityFramework and Npgsql packages!

Step 1: Code first model

Let’s create a simple model of a customer in a new customer namespace:

Step 2: Database Context

Now we need to add this model to a context deriving from DbContext to add this model as a table to our database:

Step 3: Service

To serve the data to the view (in our case a http function) we want to have a service that loads the model data from the DbContext and returns the data as a view model. So first the view model:

To make our code testable let’s introduce an interface for our service with the CRUD methods:

The implementation of the service gets the DbContext injected:

Implement the CreateAsync method:

Implement the GetAsync method:

Implement the UpdateAsync method:

Implement the DeleteAsync method:

Step 4: Configure dependency injection

Dependency Injection helps us to wire everything together and also to provide settings like the connection string. The connection is best placed in local.settings.json (Azure Key Vault for production):

For dependency injection we need a new class Startup.cs in the root namespace. Here we can retrieve the connection string and register both the context and the service:

Step 5: Enable design-time context creation and adding a migration

So this is some wisdom from a great blog post by Jeff Hollan on this matter: https://dev.to/azure/using-entity-framework-with-azure-functions-50aa

Implement a IDesignTimeDbContextFactory to help entity framework discover our context:

Next we need to make sure our project’s dll is in the right spot for entity framework. So add this copy task to the project’s csproj:

Now we can finally run our first migration and update the database in the Package Manager Console. Also note that I added the connection string as an environment variable as EntityFramework doesn’t now it from our local.settings.json:

$env:SqlConnectionString="Server=tcp:mySqlServerStuffxxx"
Add-Migration InitialCreate
Update-Database

Step 6: Http Functions

Now that the database is up to date, let’s also add 4 functions with http triggers to expose the customer CRUD operations as REST-style endpoints. Each function class and Run method needs to be modified from static to non static and we need to inject the ICustomerService into each function class.

Function to create a new customer

Function to get a list of customers

Function to update a customer

Function to delete a customer

Step 7: Test with Postman

With all functions in place our code is ready for some testing. So let’s spin up Postman to test our CRUD API:

Summary

So it’s absolutely possible to connect an Azure Function app written with C#/.NET Core to a relational database like Azure PostgreSQL or Azure SQL using EntityFramework Core with a code first approach. There are still some things that feel a bit rough like having to stick with Version 3.1.4 of EF Core (at least at the moment), having to implement IDesignTimeDbContextFactory and copying the dll after every build. But once this is set up it works really well!

Find the full source code on GitHub: GrillPhil/ServerlessSQLDemo (github.com)