How To: Reference and Set a Database Connection String Using Entity Framework Core for Deployed Azure Functions.

I’ve been doing some work with Timer and Http Trigger Azure Functions recently when working on the free journal and mood tracking app  https://dailytracker.co.

Each function calls a service class that encapsulate business logic responsible for text analytics, key phrases and entity extraction using Azure Cognitive Services.

Text analytics insights are then written to Azure SQL.

I was passing the database connection string using dependency injection into the Azure Function but didn’t like the ceremony around it and the extra code I had to write.

I wanted to load the connection string directly from the DbContext class when it was instantiated.  The DBContext is called within each service class.

Appsettings.json

For reference, here is an example appsettings.json with the connection string:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "TextAnalyticsEndpoint": "{some endpoint}",
    "TextAnalyticsAPIKey": "{api-key}"
  },
  "ConnectionStrings": {
    "DefaultConnection": "{some-connection-string}"
  }
}

Database Context

The following DBContext class contains the barebones of what is required to make it work:

public class AppDbContext : DbContext
    {
        private string _connectionString = string.Empty;
     

        public DbSet<ApplicationUser> ApplicationUsers { get; set; }

        public AppDbContext()
        {
            string fullPath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "..");
       
            var builder = new Microsoft.Extensions.Configuration.ConfigurationBuilder();

            builder.SetBasePath(fullPath);

          
            builder.AddJsonFile("appsettings.json", optional: false)
                   .AddJsonFile("local.settings.json", optional: true, reloadOnChange: true);

            var configuration = builder.Build();

            _connectionString = configuration.GetConnectionString("DefaultConnection").ToString();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(_connectionString);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ApplicationUser>(entity =>
            {
                entity.ToTable("AspNetUsers");
                entity.HasKey(a => a.Id);
            });

            base.OnModelCreating(modelBuilder);
        }
    }

In the above code, some main things to notice follow.

~

Constructor and Handling Deployment Issues in Azure

Whilst your function will work locally when it reads the appsettings.json from the file system, it will be deployed into a different location in Azure.

I found this out quickly after deploying the function.  Inspecting the debugger showed the DBContext had a null connection string.

You can verify the location in Azure for the function by using the App Service Editor for the deployed function:

The following code is needed in the DBContext to get this to work when deployed in Azure:

string fullPath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "..");

The code above constructs the correct path for the location of the appsettings.json file which can then be used to fetch the connection string.

~

OnConfiguring

This code sets the connection string (fetched in the constructor) for the DbContextOptionsBuilder class, thereby making it possible to connect to the database:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  if (!optionsBuilder.IsConfigured)
  {
    optionsBuilder.UseSqlServer(_connectionString);
  }
}

The DB Context is now setup and any DBSet<YourEntities> can now be used.

Summary

Sometimes it’s easy to forget these things as you do them at the start of a project.  A short blog post can help you remember.