Posted in:

Last year I created a demo Azure Functions project that implemented a simple CRUD REST API for managing TODO list items.

The project showed off various Azure Functions bindings by implementing the REST API against four different backing stores:

  • In-memory
  • Azure Table Storage
  • Azure Blob Storage (a JSON file per TODO item)
  • Cosmos DB

You can read about how I implemented each of these in my original post here.

But the one backing store I never got round to implementing was Entity Framework Core. And that was because Azure Functions doesn't really offer any binding support for working with SQL databases.

However, with the recent release of dependency injection support for Azure Functions, working with EF Core in Azure Functions feels a bit nicer, so in this post, I'll explain how I updated my sample app to support an Azure SQL Database as a backing store.

Project references

The first step was to reference the EF Core SQL Server NuGet package. As Jeff Hollan explains in this very helpful article, it's important to select the correct version number that's compatible with the version of .NET you are running on.

I've also referenced the Microsoft.Azure.Functions.Extensions NuGet package, which gives us access to the dependency injection feature.

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.3" />
<PackageReference Include="Microsoft.Azure.Functions.Extensions" Version="1.0.0" />

Database schema and model

My database schema is very simple. Here's the SQL to create the table I used for testing. I didn't create an EF Core migration, but Jeff's article shows how to do that if you want.

CREATE TABLE [dbo].[Todos]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [TaskDescription] NVARCHAR(50) NOT NULL, 
    [IsCompleted] BIT NOT NULL, 
    [CreatedTime] DATETIME NOT NULL
)

I also updated my local.settings.json to include a connection string I could use for local testing purposes:

"SqlConnectionString": "Data Source=(LocalDB)\\MSSQLLocalDB;Integrated Security=true;Database=Todos"

And I created an EF Core model for my TODO item entity and a custom DbContext :

public class TodoEf
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public DateTime CreatedTime { get; set; } = DateTime.UtcNow;
    public string TaskDescription { get; set; }
    public bool IsCompleted { get; set; }
}

public class TodoContext : DbContext
{
    public TodoContext(DbContextOptions<TodoContext> options)
        : base(options)
    { }

    public DbSet<TodoEf> Todos { get; set; }
}

Initialize dependency injection

To set up dependency injection for our function app we use the FunctionsStartup attribute on the assembly to indicate a startup class that will run when the function app starts. In that class, which inherits from FunctionsStartup we override the Configure method. This allows us to retrieve the SQL connection string from configuration, and register a DbContext in the services, which will allow us to inject the TodoContext into our function.

[assembly: FunctionsStartup(typeof(AzureFunctionsTodo.Startup))]

namespace AzureFunctionsTodo
{
    class Startup : FunctionsStartup
    {
        public override void Configure(IFunctionsHostBuilder builder)
        {
            string connectionString = Environment.GetEnvironmentVariable("SqlConnectionString");
            builder.Services.AddDbContext<TodoContext>(
                options => SqlServerDbContextOptionsExtensions.UseSqlServer(options, connectionString));
        }
    }
}

Injecting DbContext into a function

What dependency injection now offers to us is the ability for us to define our functions in classes which have their dependencies injected into their constructor. So here I have defined a TodoApiEntityFramework class whose constructor takes a TodoContext, that can be used by the functions we'll define shortly.

public class TodoApiEntityFramework
{
    private const string Route = "eftodo";
    private readonly TodoContext todoContext;

    public TodoApiEntityFramework(TodoContext todoContext)
    {
        this.todoContext = todoContext;
    }

    // ... functions defined here
}

Get all Todo items

Now we're ready to implement each of the five methods on our Todo api. The first gets all Todo items. As you can see it's very similar to a regular Azure Function definition, with the exception that it's not a static method. It uses a HttpTrigger, and simply uses the injected TodoContext to retrieve all the Todo items.

[FunctionName("EntityFramework_GetTodos")]
public async Task<IActionResult> GetTodos(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = Route)]
    HttpRequest req, ILogger log)
{
    log.LogInformation("Getting todo list items");
    var todos = await todoContext.Todos.ToListAsync();
    return new OkObjectResult(todos);
}

Get Todo item by id

Getting a Todo item by id again is also very straightforward to implement by calling FindAsync on our Todos DbSet:

[FunctionName("EntityFramework_GetTodoById")]
public async Task<IActionResult> GetTodoById(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = Route + "/{id}")]
    HttpRequest req, ILogger log, string id)
{
    log.LogInformation("Getting todo item by id");
    var todo = await todoContext.Todos.FindAsync(Guid.Parse(id));
    if (todo == null)
    {
        log.LogInformation($"Item {id} not found");
        return new NotFoundResult();
    }
    return new OkObjectResult(todo);
}

Create a Todo item

Here's my function to create a new Todo item. We just need to add the new item to our DbSet and save changes.

[FunctionName("EntityFramework_CreateTodo")]
public async Task<IActionResult>CreateTodo(
    [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = Route)]
    HttpRequest req, ILogger log)
{
    log.LogInformation("Creating a new todo list item");
    var requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var input = JsonConvert.DeserializeObject<TodoCreateModel>(requestBody);
    var todo = new TodoEf { TaskDescription = input.TaskDescription };
    await todoContext.Todos.AddAsync(todo);
    await todoContext.SaveChangesAsync();
    return new OkObjectResult(todo);
}

Update Todo item

Our update function is a little more involved as you can optionally update the task description:

[FunctionName("EntityFramework_UpdateTodo")]
public async Task<IActionResult> UpdateTodo(
    [HttpTrigger(AuthorizationLevel.Anonymous, "put", Route = Route + "/{id}")]
    HttpRequest req, ILogger log, string id)
{
    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var updated = JsonConvert.DeserializeObject<TodoUpdateModel>(requestBody);
    var todo = await todoContext.Todos.FindAsync(Guid.Parse(id));
    if (todo == null)
    {
        log.LogWarning($"Item {id} not found");
        return new NotFoundResult();
    }

    todo.IsCompleted = updated.IsCompleted;
    if (!string.IsNullOrEmpty(updated.TaskDescription))
    {
        todo.TaskDescription = updated.TaskDescription;
    }

    await todoContext.SaveChangesAsync();

    return new OkObjectResult(todo);
}

Delete Todo item

And here's how we can delete a Todo item.

[FunctionName("EntityFramework_DeleteTodo")]
public async Task<IActionResult> DeleteTodo(
    [HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = Route + "/{id}")]
    HttpRequest req, ILogger log, string id)
{
    var todo = await todoContext.Todos.FindAsync(Guid.Parse(id));
    if (todo == null)
    {
        log.LogWarning($"Item {id} not found");
        return new NotFoundResult();
    }

    todoContext.Todos.Remove(todo);
    await todoContext.SaveChangesAsync();
    return new OkResult();
}

Summary

The new dependency injection feature of Azure Functions makes it very simple to work with Entity Framework Core database contexts within an Azure Functions app, even though there is no explicit EF Core binding for Azure Functions. I'm also hopeful that when EF 6.3 becomes available, it will make it much easier to port legacy EF 6 code to run in Azure Functions.

Want to learn more about how easy it is to get up and running with Azure Functions? Be sure to check out my Pluralsight courses Azure Functions Fundamentals and Microsoft Azure Developer: Create Serverless Functions

Comments

Comment by Gintaras Bubelevičius

Assuming 2 delete actions for same id trigger at once, how do you handle concurrency here?

Gintaras Bubelevičius
Comment by Mark Heath

There's a few options, but I'd probably just catch the specific EF Core exception and put in custom handling (maybe a retry, or if it was obvious that the row had already been deleted, just suppressing the error).

Mark Heath
Comment by Masaab Aljailani

Hi Mark, I am using Azure TimerTrigger with V3 with EF core. I cannot use any of DB migration command. I keep getting error.
An assembly specified in the application dependencies manifest (FunctionApp1.deps.json) was not found:
package: 'FunctionApp1', version: '1.0.0'
path: 'FunctionApp1.dll'. It works fine if I use a Console App.

Masaab Aljailani
Comment by Mark Heath

it's not a problem I've run into myself (I don't generally have AZ migrations in my function apps), but possibly due to the fact that functions builds the dll into a different place. Maybe having a separate project for EF entities would help

Mark Heath
Comment by Chris Bertrand

For anyone looking, if you get this error, add the following to the .csproj file
<publishwithaspnetcoretargetmanifest>false</publishwithaspnetcoretargetmanifest>

Chris Bertrand