Categories
Uncategorized

Adding Entity Framework/SQL Server to a Function App

When working with function apps, you may want to connect SQL Server to allow for using an API with data persistence.

Setting up Project to Use Entity Framework

Add the following to your .csproj file, in the section with PackageReference entries:

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

And then add the following to allow for copying the project DLL (supports builds in both Windows and Linux):

...
  <Target Name="PostBuild" AfterTargets="PostBuildEvent">
    <Exec Command="copy /Y "$(TargetDir)bin\$(ProjectName).dll" "$(TargetDir)$(ProjectName).dll"" Condition=" '$(OS)' == 'Windows_NT'" />
    <Exec Command="cp "$(TargetDir)bin\$(ProjectName).dll" "$(TargetDir)$(ProjectName).dll"" Condition=" '$(OS)' != 'Windows_NT'" />
  </Target>
</Project>

For setting this up, I’d recommend having at least one Model ready for use. In our case, let’s use a simple example with Entry.cs:

[Key]
public int EntryId { get; set; }

public DateTime Date { get; set; }

public string Content { get; set; }

Now create the Context.cs and ContextFactory.cs files in the Models folder:

    
using Microsoft.EntityFrameworkCore;

namespace YOUR_NAMESPACE
{
  public class Context : DbContext
  {
    public Context(DbContextOptions<UsawStatsContext> options)
      : base(options)
    { }

    public DbSet<Entry> MeetResults { get; set; }
  }
}
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace YOUR_NAMESPACE
{
  public class ContextFactory : IDesignTimeDbContextFactory<Context>
  {
    public Context CreateDbContext(string[] args)
    {
      var optionsBuilder = new DbContextOptionsBuilder<Context>();
      optionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable("SqlConnectionString"));

      return new Context(optionsBuilder.Options);
    }
  }
}

Now create a Startup.cs file in root:

using System;
using YOUR_NAMESPACE.Models;
using Microsoft.Azure.Functions.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

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

namespace YOUR_NAMESPACE
{
  class Startup : FunctionsStartup
  {
    public override void Configure(IFunctionsHostBuilder builder)
    {
      string SqlConnection =
        Environment.GetEnvironmentVariable("SqlConnectionString");

      builder.Services.AddDbContext<Context>(
        options => options.UseSqlServer(SqlConnection)
      );
    }
  }
}

Setting up Database

Finally, you’ll need to set up a database for use. Create the database and use the connection string – let’s use SQL Server locally and set this in the Azure Function project’s local.settings.json:

"Values": {
  ...
  "SqlConnectionString": "Data Source=localhost\\SQLEXPRESS;Initial Catalog=DB_NAME;Integrated Security=SSPI;"
},

Once this is set, setting up migrations is the next step:

$env:SqlConnectionString="CONN_STRING"
dotnet ef migrations add InitialCreate
dotnet ef database update

With this step done, check to see if your database was updated correctly, and that you can see the tables desired.

Leave a Reply

Your email address will not be published. Required fields are marked *