DEV Community

Cover image for Part 1 - High Volume Ticket Booking System using C#, EF Core, Redis Cache and SQL Server
Olabamiji Oyetubo
Olabamiji Oyetubo

Posted on

Part 1 - High Volume Ticket Booking System using C#, EF Core, Redis Cache and SQL Server

A few weeks ago, I went to the cinemas to watch Superman. As expected for a blockbuster, the ticket lines were incredibly long, and the cashiers weren't helping much. This experience got me thinking, 'Why not build an app to simplify this?' And that's exactly what we'll do in this article.

This project will follow Clean architechure, and other best practices to make sure it's scalable.

Part 1 of this project will focus on scaffolding the entire application, Database connection and making sure we can at least make a booking.

In Part 2, we'll focus on Scalability by introducing Background workers and Redis cache

This is going to be the structure of our project

TicketBooking.sln
├─ TicketBooking.API                 // Controllers, Program.cs, DI
├─ TicketBooking.Core                // Entities, DTOs, Exceptions
├─ TicketBooking.Application         // Services, Interfaces (use cases)
├─ TicketBooking.Infrastructure      // DbContext, Migrations, Seed
└─ TicketBooking.Tests               // Unit tests NUnit + Moq

Enter fullscreen mode Exit fullscreen mode

Now, create a folder called TicketBooking and cd into that folder in the command line.

Then execute these commands to create all the projects

dotnet new sln -n TicketBooking
dotnet new webapi -n TicketBooking.API
dotnet new classlib -n TicketBooking.Core
dotnet new classlib -n TicketBooking.Application
dotnet new classlib -n TicketBooking.Infrastructure
dotnet new nunit -n TicketBooking.Tests
dotnet sln add **/*.csproj
Enter fullscreen mode Exit fullscreen mode

Your Solution should look like this

Solution Explorer Overview

  • If any project is missing, right-click the TicketBooking solution in the Solution Explorer, then select Add --> Existing Project.

  • Navigate to the respective project folder and select the .csproj file for that

Now, Let's begin to build the application.

In TicketBooking.Core, add these 4 folders Dtos, Entities, Exceptions, Interfaces

Ticket Booking Core

In the Dtos folder, add a class called AddBookingDto with the below code

public class AddBookingDto
{
    public string NameOfPerson { get; set; } = string.Empty;
    public int ScreeningId { get; set; }
    public List<string> SeatNumbers { get; set; } = new List<string>();
}
Enter fullscreen mode Exit fullscreen mode

Next, in the Entities folder add the 4 classes Booking, Movie, Screening, Seat with the following code;

public class Booking
{
    public int Id { get; set; }

    public int ScreeningId { get; set; }

    public List<string> BookedSeats { get; set; } = new List<string>();

    public string NameOfPerson { get; set; }

    public DateTime BookedAt { get; set; } = DateTime.UtcNow;
}
Enter fullscreen mode Exit fullscreen mode
public class Screening
{
    public int Id { get; set; }
    public int MovieId { get; set; }
    public DateTime StartTime { get; set; }
    public ICollection<Seat> Seats { get; set; } = new List<Seat>();
}
Enter fullscreen mode Exit fullscreen mode
public class Movie
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public ICollection<Screening> Screenings { get; set; } = new List<Screening>();
}
Enter fullscreen mode Exit fullscreen mode
public class Seat
{
    public int Id { get; set; }
    public int ScreeningId { get; set; }
    public string SeatNumber { get; set; } = string.Empty;
    public bool IsBooked { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

Next, In our Extensions folder, we'll add 2 classes AppException and SeatAlreadyBookedException

 public class AppException : Exception
 {
     public int StatusCode { get; }

     public AppException(string message, int statusCode = 400) : base(message)
     {
         StatusCode = statusCode;
     }
 }
Enter fullscreen mode Exit fullscreen mode
public class SeatAlreadyBookedException : AppException
{
    public IEnumerable<string> SeatNumbers { get; }

    public SeatAlreadyBookedException(IEnumerable<string> seatNumbers)
        : base($"The following seats are already booked: {string.Join(", ", seatNumbers)}", 409)
    {
        SeatNumbers = seatNumbers;
    }
}
Enter fullscreen mode Exit fullscreen mode

AppException is custom exception class that extends Exception and adds a StatusCode property to represent the HTTP status code, with a default of 400.

SeatAlreadyBookedException is subclass of AppException that represents an exception for already booked seats, with a 409 status code and a list of seat numbers.

Then in the Interfaces folder, add a class called IBookingService with the following code

public interface IBookingService
{
    Task<bool> BookSeatsAsync(AddBookingDto bookingDto);
}
Enter fullscreen mode Exit fullscreen mode

Now, navigate to TickbetBooking.Infrastructure, this project will house all our data operations. Then run these commands to install the neccesary packages in this project;

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.Extensions.Configuration.Json
Enter fullscreen mode Exit fullscreen mode

Then, add a folder called Data with these classes; ApplicationDbContext ApplicationDbContextFactory and DbInitializer

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

     public DbSet<Movie> Movies => Set<Movie>();
     public DbSet<Screening> Screenings => Set<Screening>();
     public DbSet<Seat> Seats => Set<Seat>();

     public DbSet<Booking> Bookings => Set<Booking>();

     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         base.OnModelCreating(modelBuilder);
         modelBuilder.Entity<Seat>().HasIndex(s => new { s.ScreeningId, s.SeatNumber }).IsUnique();
     }
 }
Enter fullscreen mode Exit fullscreen mode

This class inherits from DbContext and provides DbSet properties for the Movie, Screening, Seat, and Booking entities.

 public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
 {
     public ApplicationDbContext CreateDbContext(string[] args)
     {
         // Get the connection string from appsettings.json
         var basePath = Path.Combine(Directory.GetCurrentDirectory(), "../TicketBooking.API");

         var configuration = new ConfigurationBuilder()
             .SetBasePath(basePath)
             .AddJsonFile("appsettings.json")
             .Build();
         var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
         optionsBuilder.UseSqlServer(configuration.GetConnectionString("TicketDB"));//Or whatever your database name is.

         return new ApplicationDbContext(optionsBuilder.Options);
     }
 }
Enter fullscreen mode Exit fullscreen mode

The ApplicationDbContextFactory class implements IDesignTimeDbContextFactory<ApplicationDbContext>, allowing the creation of ApplicationDbContext at design time. It reads the connection string from appsettings.json, configures the DbContextOptions, and returns an instance of ApplicationDbContext

public static class DbInitializer
{
    public static void SeedFromSqlFile(this ApplicationDbContext db, string path = "seed-data.sql")
    {
        if (db.Movies.Any())
            return;

        var sql = File.ReadAllText(path);
        db.Database.ExecuteSqlRaw(sql);
    }
}
Enter fullscreen mode Exit fullscreen mode

The DbInitializer class is a static utility with an extension method SeedFromSqlFile for seeding the database. If the Movies table is empty, it reads SQL commands from a file and executes them to populate the database. You can download the sample seed-data.sql here

You'll need a add a reference to TicketBooking.Core from this project as well.

Next, in TicketBooking.Application, add these packages to the project as they are needed;

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Enter fullscreen mode Exit fullscreen mode

Now, create a folder called service and add this class BookingService with the below code;

public class BookingService : IBookingService
{
    private readonly ILogger<BookingService> _logger;
    private readonly ApplicationDbContext _context;

    public BookingService(ApplicationDbContext context, ILogger<BookingService> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task<bool> BookSeatsAsync(AddBookingDto bookingDto)
    {
        _logger.LogInformation("Booking started for screening {ScreeningId} with seats: {Seats}",
        bookingDto.ScreeningId, string.Join(", ", bookingDto.SeatNumbers));

        try
        {
            var screening = await _context.Screenings
                .Include(s => s.Seats)
                .FirstOrDefaultAsync(s => s.Id == bookingDto.ScreeningId);

            if (screening == null) return false;

            var targetSeats = screening.Seats
               .Where(seat => !string.IsNullOrWhiteSpace(seat.SeatNumber)
               && bookingDto.SeatNumbers.Contains(seat.SeatNumber))
               .ToList();

            if(targetSeats.Any(x => x.IsBooked))
            {
                //Seats are taken
                _logger.LogWarning("No available seats found for booking for screening {ScreeningId} with seats: {Seats}",
                    bookingDto.ScreeningId, string.Join(", ", bookingDto.SeatNumbers));
                IEnumerable<string> seatNumber = targetSeats.Where(x => x.IsBooked).Select(x => x.SeatNumber);
                throw new SeatAlreadyBookedException(seatNumber);
            }

            if(targetSeats.Count == 0)
            {
                //Seats does not exist
                _logger.LogWarning("Seats not found for booking for screening {ScreeningId} with seats: {Seats}",
                    bookingDto.ScreeningId, string.Join(", ", bookingDto.SeatNumbers));
                throw new AppException("Seats not found for booking", 404);
            }

            foreach (var seat in targetSeats)
            {
                seat.IsBooked = true;
            }

            var booking = new Core.Entities.Booking
            {
                NameOfPerson = bookingDto.NameOfPerson,
                ScreeningId = bookingDto.ScreeningId,
                BookedSeats = targetSeats.Select(s => s.SeatNumber).ToList(),
                BookedAt = DateTime.UtcNow
            };

            _context.Bookings.Add(booking);

            await _context.SaveChangesAsync();
            return true;

        }
        catch (AppException ex)
        {
            _logger.LogWarning(ex, "Booking failed due to: {Message}", ex.Message);
            throw;
        }
        catch (DbUpdateConcurrencyException ex)
        {
            _logger.LogError(ex, "Concurrency conflict while booking seats for screening {ScreeningId}", bookingDto.ScreeningId);
            throw new AppException("Seat booking failed due to a concurrency issue. Please try again.", 409);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Unexpected error in BookSeatsAsync for screening {ScreeningId}", bookingDto.ScreeningId);
            throw;
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

The BookingService class is a service responsible for managing seat bookings in a movie screening. It implements the IBookingService interface we had created earlier on and contains methods to handle booking logic.

It retrieves the screening and associated seats from the database, checking if the specified seats are available. If any seat is already booked, it throws a SeatAlreadyBookedException. If the seats do not exist, it throws a custom AppException

For valid seat selections, the method updates their booking status, creates a new booking entry, and saves the changes to the database. Throughout the method, exceptions are handled with specific catches for different error types (e.g., AppException for business logic errors, DbUpdateConcurrencyException for concurrency issues). Any unexpected errors are logged and re-thrown for further handling.

We'll also be adding references to TicketBooking.Core and TicketBooking.Infrastructure from this project.

Next in TicketBooking.API, we'll install these packages to be used for logging information;

Install-Package Serilog.AspNetCore
Install-Package Serilog.Sinks.Console
Install-Package Serilog.Sinks.File
Install-Package Swashbuckle.AspNetCore
Enter fullscreen mode Exit fullscreen mode

Now, create a new class called BookingController in the Controllers folder with this code snippet;

    [ApiController]
    [Route("api/[controller]")]
    public class BookingController : ControllerBase
    {
        private readonly IBookingService _bookingService;

        public BookingController(IBookingService bookingService)
        {
            _bookingService = bookingService;
        }

        [HttpPost]
        [Route("AddBooking")]
        public async Task<IActionResult> Book(AddBookingDto bookingDto)
        {
            var success = await _bookingService.BookSeatsAsync(bookingDto);
            return success ? Ok("Booking successful") : BadRequest("Booking failed");
        }
    }
Enter fullscreen mode Exit fullscreen mode

Next lets create a global exception handler to handle all our requests since we don't want to do it per class. So in TicketBooking.API create a new folder called Middlewares with a class ExceptionHandlingMiddleware and add the below code;

 public class ExceptionHandlingMiddleware
 {
     private readonly RequestDelegate _next;
     private readonly ILogger<ExceptionHandlingMiddleware> _logger;

     public ExceptionHandlingMiddleware(RequestDelegate next, ILogger<ExceptionHandlingMiddleware> logger)
     {
         _next = next;
         _logger = logger;
     }
     public async Task InvokeAsync(HttpContext context)
     {
         try
         {
             await _next(context);
         }
         catch (AppException ex)
         {
             _logger.LogWarning(ex, "Handled app error");

             context.Response.StatusCode = ex.StatusCode;
             context.Response.ContentType = "application/json";

             await context.Response.WriteAsJsonAsync(new
             {
                 error = ex.Message
             });
         }
         catch (Exception ex)
         {
             _logger.LogError(ex, "Unhandled system exception");

             context.Response.StatusCode = 500;
             context.Response.ContentType = "application/json";

             await context.Response.WriteAsJsonAsync(new
             {
                 error = "An unexpected error occurred."
             });
         }
     }

 }
Enter fullscreen mode Exit fullscreen mode

The ExceptionHandlingMiddleware class handles exceptions during HTTP request processing. It catches specific exceptions like our AppException defined before and general ones, logs them, and then formats an appropriate error response in JSON. If an AppException is caught, it uses the exception's StatusCode; otherwise, it returns a 500 status code for unexpected errors.

Don't forget to add the appropriate project references.

Now, in appsetting.json add a new connection string to your file

  "ConnectionStrings": {
    "TicketDB": "Data Source=localhost\\SQLEXPRESS;Initial Catalog=TicketDB;Integrated Security=True;TrustServerCertificate=True"
  }
Enter fullscreen mode Exit fullscreen mode

Remember to replace TicketDB with whatever your database name is.

Then Modify your Program.cs class so it looks like this;

Log.Logger = new LoggerConfiguration()
    .WriteTo.Console()
    .WriteTo.File("Logs/log.txt", rollingInterval: RollingInterval.Day)
    .Enrich.FromLogContext()
    .MinimumLevel.Information()
    .CreateLogger();

var builder = WebApplication.CreateBuilder(args);

builder.Host.UseSerilog();

// Add services to the container.
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("TicketDB")));

builder.Services.AddScoped<IBookingService, BookingService>();

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

    // Safe to use now
    db.Database.Migrate();
    db.SeedFromSqlFile();
}


app.UseMiddleware<ExceptionHandlingMiddleware>();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Enter fullscreen mode Exit fullscreen mode

Do not forget to paste the Seed-data.sql file from earlier in your TicketBooking.API directory.

Now, that the project scaffold has been done, open the package manager console and run the following command, also make sure the project is set to TicketBooking.Infrastructure or which ever project your ApplicationDbContext is located;

Before running this command, Set TicketBooking.Infrastructure as the startup project

Add-Migration FirstMigration
Enter fullscreen mode Exit fullscreen mode

First Migration

After running this command, you should see a new Migrations folder generated

Now run this command to create the Database and Tables

Update-Database
Enter fullscreen mode Exit fullscreen mode

If all goes well, the database and tables have been created. Now set TicketBooking.API back as the startup project and run the application. Remember we created a method that seeds data as well to our database so this is where that operation occurs.

Scafolded API

Now, If we tried to create a new booking,

{
  "nameOfPerson": "Peter Jackson",
  "screeningId": 1,
  "seatNumbers": [
    "A1", "A3"
  ]
}
Enter fullscreen mode Exit fullscreen mode

We see that the booking was successful

Successful Booking

But if we tried to book seats A1 and A3 again

Seats already taken

We see that according to our logic, it works as expected great.

As is best practice, let's add some tests to our application.

So, in the created TicketBooking.Tests project, install these packages as we will be needing them,

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.InMemory
Install-Package Moq
Enter fullscreen mode Exit fullscreen mode

Then, add the following folders - Helpers and ServiceTests .

In the Helpers folder, add a class called TestHelper with the following code

    public static class TestHelper
    {
        public static ApplicationDbContext GetInMemoryDbContextAsync()
        {
            var options = new DbContextOptionsBuilder<ApplicationDbContext>()
                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                .Options;

            var context = new ApplicationDbContext(options);

            var screening = new Screening
            {
                Id = 1,
                MovieId = 1,
                StartTime = DateTime.UtcNow,
                Seats = new List<Seat>
            {
                new Seat { Id = 1, SeatNumber = "A1", IsBooked = false },
                new Seat { Id = 2, SeatNumber = "A2", IsBooked = true },
                new Seat { Id = 3, SeatNumber = "A3", IsBooked = false },
            }
            };

            context.Screenings.Add(screening);
            context.SaveChangesAsync();

            return context;
        }
    }
Enter fullscreen mode Exit fullscreen mode

This class provides a method GetInMemoryDbContextAsync that creates an in-memory ApplicationDbContext, seeds it with a new Screening object, and adds a list of Seat entities. The data is saved asynchronously, allowing the context to be used for testing purposes.

Then in the ServiceTests folder, add a class called BookingServiceTests and add the following code;

public class BookingServiceTests
{
    private ApplicationDbContext _dbContext;
    private Mock<ILogger<BookingService>> _logger;
    private BookingService _bookingService;

    [SetUp]
    public void Setup()
    {
        _dbContext = TestHelper.GetInMemoryDbContextAsync();
        _logger = new Mock<ILogger<BookingService>>();
        _bookingService = new BookingService(_dbContext, _logger.Object);
    }

    [TearDown]
    public void TearDown()
    {
        _dbContext?.Dispose();
    }

    [Test]
    public async Task BookSeatsAsync_Should_BookSeats_Successfully()
    {
        var dto = new AddBookingDto
        {
            NameOfPerson = "John Doe",
            ScreeningId = 1,
            SeatNumbers = new List<string> { "A1", "A3" }
        };

        var result = await _bookingService.BookSeatsAsync(dto);
        Assert.That(result, Is.True);
    }

    [Test]
    public async Task BookSeatsAsync_Should_Throw_SeatAlreadyBookedException_If_Seat_Already_Booked()
    {
        var dto = new AddBookingDto
        {
            NameOfPerson = "Jane Smith",
            ScreeningId = 1,
            SeatNumbers = new List<string> { "A2" } // already booked
        };

        var ex = Assert.ThrowsAsync<SeatAlreadyBookedException>(() => _bookingService.BookSeatsAsync(dto));

        Assert.Contains("A2", ex.SeatNumbers.ToList());
    }

    [Test]
    public async Task BookSeatsAsync_Should_Throw_AppException_If_Seat_Does_Not_Exist()
    {
        var dto = new AddBookingDto
        {
            NameOfPerson = "Alice",
            ScreeningId = 1,
            SeatNumbers = new List<string> { "B99" } // non-existent seat
        };

        var ex = Assert.ThrowsAsync<AppException>(() => _bookingService.BookSeatsAsync(dto));
        Assert.That(ex.StatusCode, Is.EqualTo(404));
    }
}
Enter fullscreen mode Exit fullscreen mode

We have created 3 tests;

  1. BookSeatsAsync_Should_BookSeats_Successfully - which checks that a new booking can be made
  2. BookSeatsAsync_Should_Throw_SeatAlreadyBookedException_If_Seat_Already_Booked - which checks that an already booked seat cannot be booked again.
  3. BookSeatsAsync_Should_Throw_AppException_If_Seat_Does_Not_Exist - which throws an exception if the client tries to book a seat that does not exist.

Add the necessary project references to TicketBooking.Core and TicketBooking.Infrastructure

Run the test project and we see that they all pass;

Test Explorer results

That concludes part 1 of this series, in Part 2, we'll optimize it by queuing booking requests for async processing and caching screening/seat data using Redis, ensuring efficient reads and cache invalidation after each booking.

If you got lost anywhere, you can find the entire project for Part 1 in the branch here

See you in the next one and happy coding.

Top comments (0)