Using Entity Framework for .NET 6 with SQL Server in a Docker Container

Full source code available here.

As I mentioned in the previous post, I’ve always disliked installing SQL Server on my computer, but I’ve always loved working with Entity Framework. With Docker, I can have SQL Server up and running, but not installed on my OS, and connect my application using EF to it. I love it!

This post will NOT show how to set up Docker, or how to get the SQL Server image (covered in the previous post), there are plenty of guides out there for that.

All I will show is how to use your running SQL Server inside Docker with an application using Entity Framework.

The Application

Once you have the SQL Server up and running in Docker (see this post for the command to start it), you can connect to the application to it with a traditional connection string.

In a real-world scenario, don’t use the SA user to connect to the database from Entity Framework, and don’t put passwords into your appsettings.json file.

My SQL Server in Docker is forwarding the standard port, so I have a simple connection string in appsettings.json -

{
  "ConnectionStrings": {
    "SalesDb": "Server=localhost;Database=SalesDb;User Id=SA;Password=A!VeryComplex123Password;MultipleActiveResultSets=true;TrustServerCertificate=True"
  }
}

In the ConfigureService(..) method add -

services.AddDbContext<SalesContext>(options =>
    options.UseSqlServer(Configuration["ConnectionStrings:SalesDb"])); 

During application startup, the Configure will create the database if it doesn’t exist and seed it using AutoFixture if it is empty. Note that I added the SalesContext as a parameter to the method -

public void Configure(IApplicationBuilder app, IWebHostEnvironment env, SalesContext salesContext)
{
    salesContext.Database.EnsureCreated();
    salesContext.Seed();

The seed method looks like this (DON’T DO THIS in a production application) -

public static void Seed(this SalesContext salesContext)
{
    if (!salesContext.Products.Any())
    {
        Fixture fixture = new Fixture();
        fixture.Customize<Product>(product => product.Without(p => p.ProductId));
        //--- The next two lines add 100 rows to your database
        List<Product> products = fixture.CreateMany<Product>(100).ToList();
        salesContext.AddRange(products);
        salesContext.SaveChanges();
   }
}

Then the GET action method controller will read from the SQL Server running in Docker -

[ApiController]
[Route("[controller]")]
public class ProductsController : ControllerBase
{
    private readonly SalesContext _salesContext;

    public ProductsController(SalesContext salesContext)
    {
        _salesContext = salesContext;
    }

    [HttpGet]
    public ActionResult Get(int take = 10, int skip = 0)
    {
        return Ok(_salesContext.Products.OrderBy(p => p.ProductId).Skip(skip).Take(take));
    }
}