Entity Framework Core In Docker Container - Part Two - SQLite

This article offers a good step-by-step tutorial on using SQLite and Entity Framework Core with Docker.

As I promised, in this article, I shall explain the Entity Framework Core with SQLite in Docker. The architecture which I have used in Part I is based on Microservices architecture with docker or containerized apps. The power of Microservices architecture is the scalability, as you see in our Part I example. You can very quickly scale out your services by creating more product containers. In this part (SQLite), we will put the database inside the container. The first question coming in my mind is how can you scale out your services or application in a Docker container? So, you have to think about that before using SQLite inside the container.
 
Entity Framework Core in Docker Container
Image -1- shows ProductsWebAPI for easy scaling in/out with SQL Server

If you have not installed Docker, then please go back to Part I and install Docker for Windows desktop.

Source Code in GitHub: Source Code
 
For more information about Entity Framework Core and C# 8, please visit: bassam or Bassam on C#Corner
 

OrdersWebApi: Let us do it together (step by step)

 
I have opened the solution file from Part I, and then I have selected from the menu item - "Add-> New Project…" and I have chosen ASP.NET Core Web Application, as you can see in the image below.
 
Entity Framework Core in Docker Container 

Here, I have set the project name to "OrdersSQLite”.

Entity Framework Core in Docker Container
 
 The next window is new. It came with the latest Visual Studio 2019 Update 3. I have selected “API” and then pressed “Create”.
 
Entity Framework Core in Docker Container
 
A dummy OrdersSQLite project is generated and if you press F5, then you shall see the dummy value1 and value2 in your web browser.
 
Entity Framework Core in Docker Container
 

Domain Model and Data Access Layer

 
In this section, I have -
  1. added the entity “Order” to the project.
  2. added OrdersDBContext to the project and set up the connection string.
  3. added OrderssController to the project with some logic to handle the requests.
Order Entity
  1. public class Order  
  2.   {  
  3.     /// <summary>  
  4.     /// Gets or sets the order identifier.  
  5.     /// </summary>  
  6.     /// <value>The order identifier.</value>  
  7.     public int OrderId { getset; }  
  8.   
  9.     /// <summary>  
  10.     /// Gets or sets the name.  
  11.     /// </summary>  
  12.     /// <value>The name.</value>  
  13.     public string Name { getset; }  
  14.   
  15.     /// <summary>  
  16.     /// Gets or sets the product Ids.  
  17.     /// </summary>  
  18.     /// <value>The product Ids.</value>  
  19.     Collection<int> ProductIds { getset; } = new Collection<int>();  
  20.   }  
Here is the Order's database context.
  1. public class OrderDbContext : DbContext    
  2. {    
  3.     /// <summary>    
  4.     /// Gets or sets the orders.    
  5.     /// </summary>    
  6.     /// <value>The orders.</value>    
  7.     public DbSet<Order> Orders { getset; }    
  8.     
  9.     /// <summary>    
  10.     /// Initializes a new instance of the <see cref="OrderDbContext"/> class.    
  11.     /// </summary>    
  12.     /// <param name="options">The options.</param>    
  13.     public OrderDbContext(DbContextOptions<OrderDbContext> options)    
  14.             : base(options)    
  15.     {    
  16.     }    
  17.     
  18.     protected override void OnModelCreating(ModelBuilder modelBuilder)    
  19.     {    
  20.       modelBuilder.Entity<Order>().HasData    
  21.           (    
  22.           new Order { OrderId = 1, Name = "MSDN Order" },    
  23.           new Order { OrderId = 2, Name = "Docker Order" },    
  24.           new Order { OrderId = 3, Name = "EFCore Order" }    
  25.           );    
  26.     }    
  27. }    

Also, I have added the connection string to the appsettings.json file.

  1. {  
  2.   "Logging": {  
  3.     "LogLevel": {  
  4.       "Default""Warning"  
  5.     }  
  6.   },  
  7.   "AllowedHosts""*",  
  8.   "ConnectionStrings": {  
  9.     "OrdersConnectionSqlite""Filename=Orders.db;"  
  10.   }  
  11. }  
Here is the Order's controller with some logic to store, load, and delete the orders.
  1. [ApiController, Route("api/[controller]")]  
  2. public class OrdersController : ControllerBase  
  3. {  
  4.     /// <summary>  
  5.     /// The orders database context  
  6.     /// </summary>  
  7.     private readonly OrderDbContext _ordersDbContext;  
  8.   
  9.     /// <summary>  
  10.     /// Initializes a new instance of the <see cref="OrdersController"/> class.  
  11.     /// </summary>  
  12.     /// <param name="ordersDbContext">The orders database context.</param>  
  13.     public OrdersController(OrderDbContext ordersDbContext)  
  14.     {  
  15.       this._ordersDbContext = ordersDbContext;  
  16.     }  
  17.   
  18.     //GET: api/Orders  
  19.     /// <summary>  
  20.     /// Gets the order.  
  21.     /// </summary>  
  22.     /// <returns>Task<ActionResult<IEnumerable<Order>>>.</returns>  
  23.     [HttpGet]  
  24.     public async Task<ActionResult<IEnumerable<Order>>> GetOrder()  
  25.     {  
  26.       return Ok(await _ordersDbContext.Orders.ToListAsync());  
  27.     }  
  28.   
  29.     // GET: api/Orders/5  
  30.     /// <summary>  
  31.     /// Gets the order.  
  32.     /// </summary>  
  33.     /// <param name="id">The identifier.</param>  
  34.     /// <returns>Task<ActionResult<Order>>.</returns>  
  35.     [HttpGet("{id}")]  
  36.     public async Task<ActionResult<Order>> GetOrder(int id)  
  37.     {  
  38.       var order = await _ordersDbContext.Orders.FindAsync(id);  
  39.   
  40.       if (order == null)  
  41.       {  
  42.         return NotFound();  
  43.       }  
  44.   
  45.       return Ok(order);  
  46.     }  
  47.   
  48.     // PUT: api/Orders/5  
  49.     /// <summary>  
  50.     /// Puts the order.  
  51.     /// </summary>  
  52.     /// <param name="id">The identifier.</param>  
  53.     /// <param name="order">The order.</param>  
  54.     /// <returns>Task<IActionResult>.</returns>  
  55.     [HttpPut("{id}")]  
  56.     public async Task<IActionResult> PutOrder(int id, Order order)  
  57.     {  
  58.       if (id != order.OrderId)  
  59.       {  
  60.         return BadRequest();  
  61.       }  
  62.   
  63.       _ordersDbContext.Entry(order).State = EntityState.Modified;  
  64.   
  65.       try  
  66.       {  
  67.         await _ordersDbContext.SaveChangesAsync();  
  68.       }  
  69.       catch (DbUpdateConcurrencyException)  
  70.       {  
  71.         if (!IsOrderExists(id))  
  72.         {  
  73.           return NotFound();  
  74.         }  
  75.   
  76.         throw;  
  77.       }  
  78.   
  79.       return NoContent();  
  80.     }  
  81.   
  82.     // POST: api/Orders  
  83.     /// <summary>  
  84.     /// Posts the order.  
  85.     /// </summary>  
  86.     /// <param name="order">The order.</param>  
  87.     /// <returns>Task<ActionResult<Order>>.</returns>  
  88.     [HttpPost]  
  89.     public async Task<ActionResult<Order>> PostOrder(Order order)  
  90.     {  
  91.       _ordersDbContext.Orders.Add(order);  
  92.       await _ordersDbContext.SaveChangesAsync();  
  93.   
  94.       return CreatedAtAction("GetOrder"new { id = order.OrderId }, order);  
  95.     }  
  96.   
  97.     // DELETE: api/Orders/5  
  98.     /// <summary>  
  99.     /// Deletes the order.  
  100.     /// </summary>  
  101.     /// <param name="id">The identifier.</param>  
  102.     /// <returns>Task<ActionResult<Order>>.</returns>  
  103.     [HttpDelete("{id}")]  
  104.     public async Task<ActionResult<Order>> DeleteOrder(int id)  
  105.     {  
  106.       var order = await _ordersDbContext.Orders.FindAsync(id);  
  107.       if (order == null)  
  108.       {  
  109.         return NotFound();  
  110.       }  
  111.   
  112.       _ordersDbContext.Orders.Remove(order);  
  113.       await _ordersDbContext.SaveChangesAsync();  
  114.   
  115.       return Ok(order);  
  116.     }  
  117.   
  118.     /// <summary>  
  119.     /// Determines whether [is order exists] [the specified identifier].  
  120.     /// </summary>  
  121.     /// <param name="id">The identifier.</param>  
  122.     /// <returns><c>true</c> if [is order exists] [the specified identifier]; otherwise, <c>false</c>.</returns>  
  123.     private bool IsOrderExists(int id)  
  124.     {  
  125.       return _ordersDbContext.Orders.Any(e => e.OrderId == id);  
  126.     }  
  127. }  
Besides, we have to register the database context. I have added the registration in the startup class. 
  1. public class Startup  
  2. {  
  3.         public Startup(IConfiguration configuration)  
  4.         {  
  5.             Configuration = configuration;  
  6.         }  
  7.   
  8.         public IConfiguration Configuration { get; }  
  9.   
  10.         // This method gets called by the runtime. Use this method to add services to the container.  
  11.         public void ConfigureServices(IServiceCollection services)  
  12.         {  
  13.             services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);  
  14.   
  15.             services.AddDbContext<OrderDbContext>(options =>  
  16.                   options.UseSqlite(Configuration.GetConnectionString("OrdersConnectionSqlite")));  
  17.         }  
  18.   
  19.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
  20.         public void Configure(IApplicationBuilder app, IHostingEnvironment env)  
  21.         {  
  22.             if (env.IsDevelopment())  
  23.             {  
  24.                 app.UseDeveloperExceptionPage();  
  25.             }  
  26.   
  27.             app.UseMvc();  
  28.         }  
  29. }  
Unfortunately, the application still not built, because of missing libraries.
 
Entity Framework Core in Docker Container
 
To solve the problem, we have to install Microsoft.EntityFrameworkCore.SQLite .
 
In the Package Manager Console, I have executed: “Install-Package Microsoft.EntityFrameworkCore.Sqlite”.
 
Entity Framework Core in Docker Container
 
We have fixed the problem, and we can build and start the project; however before we do that, we shall create and feed the database. In SQLite, we need to generate the migration files that are required to create the database from scratch.
 
I use here also the Package Manager Console to generate the migration files.
 
“Add-Migration InitialCreate -Project EntityFrameworkSQLite”
 
Entity Framework Core in Docker Container
 
Visual Studio has generated the migration files with some dummy data, and the database can be created.
 
I have made an extension method “CreateDatabase”, which I use to create the database, as defined below.
  1. public static class ExtensionMethods  
  2. {  
  3.     /// <summary>  
  4.     /// Migrates the database.  
  5.     /// </summary>  
  6.     /// <typeparam name="T"></typeparam>  
  7.     /// <param name="webHost">The web host.</param>  
  8.     /// <returns>IWebHost.</returns>  
  9.     public static IWebHost CreateDatabase<T>(this IWebHost webHost) where T : DbContext  
  10.         {  
  11.             using (var scope = webHost.Services.CreateScope())  
  12.             {  
  13.                 var services = scope.ServiceProvider;  
  14.                 try  
  15.                 {  
  16.                     var db = services.GetRequiredService<T>();  
  17.                     db.Database.Migrate();  
  18.                 }  
  19.                 catch (Exception ex)  
  20.                 {  
  21.                     var logger = services.GetRequiredService<ILogger<Program>>();  
  22.                     logger.LogError(ex, "Database Creation/Migrations failed!");  
  23.                 }  
  24.             }  
  25.             return webHost;  
  26.         }  
  27. }   
I am calling the extension method when the application starts up,
  1. public class Program  
  2. {  
  3.         public static void Main(string[] args)  
  4.         {  
  5.             CreateWebHostBuilder(args).Build().CreateDatabase<OrderDbContext>().Run();  
  6.         }  
  7.   
  8.         public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>  
  9.             WebHost.CreateDefaultBuilder(args)  
  10.                 .UseStartup<Startup>();  
  11. }  
I have modified DockerFile and docker-compose.yml,
 
DockerFile
  1. FROM mcr.microsoft.com/dotnet/core/aspnet:2.2-stretch-slim AS base  
  2. WORKDIR /app  
  3.   
  4. EXPOSE 32034  
  5.   
  6. FROM mcr.microsoft.com/dotnet/core/sdk:2.2-stretch AS build  
  7. WORKDIR /src  
  8. COPY ["../OrdersSQLite/OrdersSQLite.csproj""../OrdersSQLite/"]  
  9. RUN dotnet restore "../OrdersSQLite/OrdersSQLite.csproj"  
  10. COPY . .  
  11. WORKDIR "/src/../OrdersSQLite"  
  12. RUN dotnet build "OrdersSQLite.csproj" -c Release -o /app  
  13.   
  14. FROM build AS publish  
  15. RUN dotnet publish "OrdersSQLite.csproj" -c Release -o /app  
  16.   
  17. FROM base AS final  
  18. WORKDIR /app  
  19. COPY --from=publish /app .  
  20. ENTRYPOINT ["dotnet""OrdersSQLite.dll"]  
docker-compose.yml
  1. version: '3.4'  
  2.   
  3. services:  
  4.   OrdersSQLite:  
  5.     image:  ${DOCKER_REGISTRY}orderswebapi  
  6.     build:  
  7.       context: .  
  8.       dockerfile: ../OrdersSQLite/Dockerfile  
  9.   
  10.   ProductsSqlServer:  
  11.     image:  ${DOCKER_REGISTRY}productswebapi  
  12.     build:  
  13.       context: .  
  14.       dockerfile: ProductsSqlServer/Dockerfile  
  15.     links:  
  16.       - sqlserver  
  17.   
  18.   sqlserver:  
  19.     image: microsoft/mssql-server-linux:2017-latest  
  20.     hostname: 'sqlserver'  
  21.     environment:  
  22.       ACCEPT_EULA: Y  
  23.       SA_PASSWORD: "BigPassw0rd"  
  24.     volumes:  
  25.       - ./data/mssql:/var/opt/mssql3  
  26.     ports:  
  27.       - '1433:1433'  
  28.     expose:  
  29.       - 1433  
  30.       - 1433  
docker-compose.override.yml
  1. version: '3.4'  
  2.   
  3. services:  
  4.   OrdersSQLite:  
  5.     environment:  
  6.       - ASPNETCORE_ENVIRONMENT=Development  
  7.     ports:  
  8.       - "32034:80"  
  9.   
  10.   ProductsSqlServer:  
  11.     environment:  
  12.       - ASPNETCORE_ENVIRONMENT=Development  
  13.     ports:  
  14.       - "32033:80"  
Before you start the application process, please make sure the docker-compose is set as the startup project. Press F5 and enjoy it.
 
Entity Framework Core in Docker Container
 
Let us add a new Order. I am using, here again, Postman (Part I).
 
You have to do it by yourself.
  1. http://localhost:32034/api/orders  
  2. {"name""3 Pack Order"}  
So Here is my breakpoint in Visual Studio 2019.
 
Entity Framework Core in Docker Container
 
The job is done! We have a new order in the SQLite database. 
 
Entity Framework Core in Docker Container
 

How can you copy your database locally?

 
Open the command line console and execute the following command "docker container ls". ls stands for list containers.
 
As you saw, the order service has Container Id "f1b9f53be420". We will use the container id to copy the database file from the container to your computer.
 
Execute on the command line console,
  1. docker cp f1b9f53be420:/app/orders.db myLocalFilename.db  
After that when you list the files in your project directory, then you can see the SQLite database file "myLocalFilename.db".
 
Entity Framework Core in Docker Container
 
Let us open the copied database file and browse the database. I am using DB Browser SQLite (sqlitebrowser.org) to demonstrate the concept. Afterward, I am also going to show you, how can you generate the query information.
 
Entity Framework Core in Docker Container
 
I have executed in DB Browser SQLite the following query,
  1. EXPLAIN QUERY PLAN SELECT * FROM Orders where OrderId  = 2 AND name like '%Docker%'  
Entity Framework Core in Docker Container
 
 You can also browse the whole database.
 
Entity Framework Core in Docker Container
 

Summary

You can easily use SQLite and Entity Framework Core in a Docker, but if you want to apply SQLite in the containerized apps, then you have to think about the core Microservices concept (Scalability). How can you scale out (Horizontal Scaling) your services?