Docker compose setup for postgres with Pg Admin

DatabaseContext.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

// Webshop database
public class Company
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string? Country { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public decimal Price { get; set; }
    public DateOnly? Expiry { get; set; }

    public Company? _Company { get; set; }
    public int CompanyID { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;
}


public class Order
{
    public int Id { get; set; }
    public Customer? _Customer { get; set; }
    public int? CustomerID { get; set; }
    public DateTime dateTime { get; set; }
    public bool isOnline { get; set; } //online offline orders
}

public class ShoppingCart
{
    public Order _Order { get; set; } = null!;
    public int Id { get; set; }
    public Product _Product { get; set; } = null!;
    public int ProductID { get; set; }
    public int Quantity { get; set; }
}


// Employee management database
public class Project
{
    public Guid ProjectId { get; set; }
    public string Name { get; set; }
    public double Budget { get; set; }
    public List<EmployeeProject> EmployeeProjects { get; set; }
}

public class EmployeeProject
{
    public Guid EmployeeProjectId { get; set; }
    public Guid EmployeeId { get; set; }
    public Employee Employee { get; set; }
    public Guid ProjectId { get; set; }
    public Project Project { get; set; }
}

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public double Salary { get; set; }
    public Guid? ManagerId { get; set; }
    public Guid? DepartmentId { get; set; }
    public Employee? Manager { get; set; }
    public Department? Department { get; set; }
    public List<EmployeeProject> EmployeeProjects { get; set; }
}

public class Department
{
    public Guid DepartmentId { get; set; }
    public string Name { get; set; }

    public List<Employee> Workers { get; set; }
}

public class DatabaseContext : DbContext
{
    // Webshop database
    public DbSet<Company> Companies { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<ShoppingCart> ShoppingCarts { get; set; }

    // Employee management database
    public DbSet<Project> Projects { get; set; }
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Department> Departments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
        .LogTo(Console.WriteLine, LogLevel.Information)
        .UseNpgsql(@"Host=localhost;Username=postgres;Password=postgres;Database=mydatabase");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Department>().HasKey(d => d.DepartmentId);
        modelBuilder.Entity<Employee>().HasKey(e => e.EmployeeId);
        modelBuilder.Entity<EmployeeProject>().HasKey(ep => ep.EmployeeProjectId);
        modelBuilder.Entity<Project>().HasKey(p => p.ProjectId);

        modelBuilder.Entity<EmployeeProject>()
            .HasOne(ep => ep.Employee)
            .WithMany(e => e.EmployeeProjects)
            .HasForeignKey(ep => ep.EmployeeId);

        modelBuilder.Entity<EmployeeProject>()
            .HasOne(ep => ep.Project)
            .WithMany(p => p.EmployeeProjects)
            .HasForeignKey(ep => ep.ProjectId);

        modelBuilder.Entity<Employee>()
            .HasOne(e => e.Manager)
            .WithMany()
            .HasForeignKey(e => e.ManagerId)
            .OnDelete(DeleteBehavior.SetNull);

        modelBuilder.Entity<Employee>()
            .HasOne(e => e.Department)
            .WithMany(d => d.Workers)
            .HasForeignKey(e => e.DepartmentId)
            .OnDelete(DeleteBehavior.Cascade);

        // Seed Departments
        // Use fixed Guids for seeding
        var departments = new[]
        {
            new Department { DepartmentId = Guid.Parse("11111111-1111-1111-1111-111111111111"), Name = "Engineering" },
            new Department { DepartmentId = Guid.Parse("22222222-2222-2222-2222-222222222222"), Name = "Human Resources" },
            new Department { DepartmentId = Guid.Parse("33333333-3333-3333-3333-333333333333"), Name = "Marketing" },
            new Department { DepartmentId = Guid.Parse("44444444-4444-4444-4444-444444444444"), Name = "Finance" }
        };

        // Seed Managers (one per department)
        var managers = new[]
        {
            new Employee { EmployeeId = Guid.Parse("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"), Name = "Alice Johnson", Salary = 120000, DepartmentId = departments[0].DepartmentId },
            new Employee { EmployeeId = Guid.Parse("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb2"), Name = "Brian Smith", Salary = 110000, DepartmentId = departments[1].DepartmentId },
            new Employee { EmployeeId = Guid.Parse("cccccccc-cccc-cccc-cccc-ccccccccccc3"), Name = "Catherine Lee", Salary = 115000, DepartmentId = departments[2].DepartmentId },
            new Employee { EmployeeId = Guid.Parse("dddddddd-dddd-dddd-dddd-ddddddddddd4"), Name = "David Brown", Salary = 118000, DepartmentId = departments[3].DepartmentId }
        };

        // Seed Employees (assigning managers and departments)
        var employees = new[]
        {
            // Engineering
            new Employee { EmployeeId = Guid.Parse("e0000000-0000-0000-0000-000000000001"), Name = "Ethan Clark", Salary = 90000, DepartmentId = departments[0].DepartmentId, ManagerId = managers[0].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("f0000000-0000-0000-0000-000000000002"), Name = "Fiona Adams", Salary = 85000, DepartmentId = departments[0].DepartmentId, ManagerId = managers[0].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("a0000000-0000-0000-0000-000000000003"), Name = "George Miller", Salary = 87000, DepartmentId = departments[0].DepartmentId, ManagerId = managers[0].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("b0000000-0000-0000-0000-000000000004"), Name = "Hannah Wilson", Salary = 88000, DepartmentId = departments[0].DepartmentId, ManagerId = managers[0].EmployeeId },

            // Human Resources
            new Employee { EmployeeId = Guid.Parse("c0000000-0000-0000-0000-000000000005"), Name = "Irene Davis", Salary = 70000, DepartmentId = departments[1].DepartmentId, ManagerId = managers[1].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("d0000000-0000-0000-0000-000000000006"), Name = "Jack Evans", Salary = 72000, DepartmentId = departments[1].DepartmentId, ManagerId = managers[1].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("e0000000-0000-0000-0000-000000000007"), Name = "Karen White", Salary = 71000, DepartmentId = departments[1].DepartmentId, ManagerId = managers[1].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("f0000000-0000-0000-0000-000000000008"), Name = "Liam Harris", Salary = 73000, DepartmentId = departments[1].DepartmentId, ManagerId = managers[1].EmployeeId },

            // Marketing
            new Employee { EmployeeId = Guid.Parse("a0000000-0000-0000-0000-000000000009"), Name = "Megan Lewis", Salary = 80000, DepartmentId = departments[2].DepartmentId, ManagerId = managers[2].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("b0000000-0000-0000-0000-000000000010"), Name = "Nathan Walker", Salary = 82000, DepartmentId = departments[2].DepartmentId, ManagerId = managers[2].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("c0000000-0000-0000-0000-000000000011"), Name = "Olivia Hall", Salary = 81000, DepartmentId = departments[2].DepartmentId, ManagerId = managers[2].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("d0000000-0000-0000-0000-000000000012"), Name = "Paul Young", Salary = 83000, DepartmentId = departments[2].DepartmentId, ManagerId = managers[2].EmployeeId },

            // Finance
            new Employee { EmployeeId = Guid.Parse("e0000000-0000-0000-0000-000000000013"), Name = "Quinn King", Salary = 95000, DepartmentId = departments[3].DepartmentId, ManagerId = managers[3].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("f0000000-0000-0000-0000-000000000014"), Name = "Rachel Scott", Salary = 94000, DepartmentId = departments[3].DepartmentId, ManagerId = managers[3].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("a0000000-0000-0000-0000-000000000015"), Name = "Samuel Green", Salary = 93000, DepartmentId = departments[3].DepartmentId, ManagerId = managers[3].EmployeeId },
            new Employee { EmployeeId = Guid.Parse("b0000000-0000-0000-0000-000000000016"), Name = "Tina Baker", Salary = 92000, DepartmentId = departments[3].DepartmentId, ManagerId = managers[3].EmployeeId }
        };

        // Add managers to employees list (managers have no ManagerId)
        var allEmployees = managers.Concat(employees).ToArray();

        // Seed data
        modelBuilder.Entity<Department>().HasData(departments);
        modelBuilder.Entity<Employee>().HasData(allEmployees);

        var noDeptEmployees = new[]
        {
            new Employee { EmployeeId = Guid.Parse("c0000000-0000-0000-0000-000000000017"), Name = "Uma Perez", Salary = 60000, ManagerId = null },
            new Employee { EmployeeId = Guid.Parse("d0000000-0000-0000-0000-000000000018"), Name = "Victor Reed", Salary = 65000, ManagerId = null }
        };

        modelBuilder.Entity<Employee>().HasData(noDeptEmployees);

        // Seed Projects
        var projects = new[]
        {
            new Project { ProjectId = Guid.Parse("55555555-5555-5555-5555-555555555555"), Name = "Project Apollo", Budget = 500000 },
            new Project { ProjectId = Guid.Parse("66666666-6666-6666-6666-666666666666"), Name = "Project Borealis", Budget = 300000 },
            new Project { ProjectId = Guid.Parse("77777777-7777-7777-7777-777777777777"), Name = "Project Cygnus", Budget = 200000 }
        };

        modelBuilder.Entity<Project>().HasData(projects);

        // Seed EmployeeProject relationships
        var employeeProjects = new[]
        {
            // Employees working on just 1 project
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000001"), EmployeeId = Guid.Parse("e0000000-0000-0000-0000-000000000001"), ProjectId = projects[0].ProjectId },
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000002"), EmployeeId = Guid.Parse("c0000000-0000-0000-0000-000000000005"), ProjectId = projects[1].ProjectId },
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000003"), EmployeeId = Guid.Parse("a0000000-0000-0000-0000-000000000009"), ProjectId = projects[2].ProjectId },

            // Employees working on multiple projects
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000004"), EmployeeId = Guid.Parse("f0000000-0000-0000-0000-000000000002"), ProjectId = projects[0].ProjectId },
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000005"), EmployeeId = Guid.Parse("f0000000-0000-0000-0000-000000000002"), ProjectId = projects[1].ProjectId },
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000006"), EmployeeId = Guid.Parse("b0000000-0000-0000-0000-000000000004"), ProjectId = projects[0].ProjectId },
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000007"), EmployeeId = Guid.Parse("b0000000-0000-0000-0000-000000000004"), ProjectId = projects[2].ProjectId },

            // Managers working on a project
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000008"), EmployeeId = Guid.Parse("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"), ProjectId = projects[0].ProjectId },
            new EmployeeProject { EmployeeProjectId = Guid.Parse("10000000-0000-0000-0000-000000000009"), EmployeeId = Guid.Parse("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb2"), ProjectId = projects[1].ProjectId }
            // Some employees (e.g., "Uma Perez", "Victor Reed", "George Miller", etc.) are not assigned to any project
        };

        modelBuilder.Entity<EmployeeProject>().HasData(employeeProjects);

        // Seed Companies
        var companies = new[]
        {
            new Company { Id = 1, Name = "Contoso Ltd", Country = "USA" },
            new Company { Id = 2, Name = "Fabrikam Inc", Country = "UK" },
            new Company { Id = 3, Name = "Globex Corp", Country = "Germany" },
            new Company { Id = 4, Name = "Initech", Country = "Canada" }
        };
        modelBuilder.Entity<Company>().HasData(companies);

        // Seed Products
        var products = new[]
        {
            new Product { Id = 1, Name = "Laptop", Price = 1200.00m, Expiry = null, CompanyID = 1 },
            new Product { Id = 2, Name = "Smartphone", Price = 800.00m, Expiry = null, CompanyID = 1 },
            new Product { Id = 3, Name = "Tablet", Price = 500.00m, Expiry = null, CompanyID = 1 },
            new Product { Id = 4, Name = "Monitor", Price = 300.00m, Expiry = null, CompanyID = 2 },
            new Product { Id = 5, Name = "Keyboard", Price = 50.00m, Expiry = null, CompanyID = 2 },
            new Product { Id = 6, Name = "Mouse", Price = 30.00m, Expiry = null, CompanyID = 2 },
            new Product { Id = 7, Name = "Printer", Price = 200.00m, Expiry = null, CompanyID = 2 },
            new Product { Id = 8, Name = "Desk Chair", Price = 150.00m, Expiry = null, CompanyID = 3 },
            new Product { Id = 9, Name = "Desk Lamp", Price = 40.00m, Expiry = null, CompanyID = 3 },
            new Product { Id = 10, Name = "Webcam", Price = 70.00m, Expiry = null, CompanyID = 3 },
            new Product { Id = 11, Name = "Router", Price = 90.00m, Expiry = null, CompanyID = 3 },
            new Product { Id = 12, Name = "External HDD", Price = 120.00m, Expiry = null, CompanyID = 4 },
            new Product { Id = 13, Name = "USB Drive", Price = 20.00m, Expiry = null, CompanyID = 4 },
            new Product { Id = 14, Name = "Projector", Price = 400.00m, Expiry = null, CompanyID = 4 },
            new Product { Id = 15, Name = "Scanner", Price = 180.00m, Expiry = null, CompanyID = 4 },
            new Product { Id = 16, Name = "Graphics Card", Price = 350.00m, Expiry = null, CompanyID = 1 },
            new Product { Id = 17, Name = "Motherboard", Price = 220.00m, Expiry = null, CompanyID = 2 },
            new Product { Id = 18, Name = "RAM 16GB", Price = 90.00m, Expiry = null, CompanyID = 3 },
            new Product { Id = 19, Name = "SSD 1TB", Price = 130.00m, Expiry = null, CompanyID = 4 },
            new Product { Id = 20, Name = "Power Supply", Price = 80.00m, Expiry = null, CompanyID = 1 }
        };
        modelBuilder.Entity<Product>().HasData(products);

        // Seed Customers
        var customers = new[]
        {
            new Customer { Id = 1, FirstName = "John", LastName = "Doe" },
            new Customer { Id = 2, FirstName = "Jane", LastName = "Smith" },
            new Customer { Id = 3, FirstName = "Michael", LastName = "Johnson" },
            new Customer { Id = 4, FirstName = "Emily", LastName = "Davis" },
            new Customer { Id = 5, FirstName = "Chris", LastName = "Brown" },
            new Customer { Id = 6, FirstName = "Jessica", LastName = "Wilson" },
            new Customer { Id = 7, FirstName = "David", LastName = "Martinez" },
            new Customer { Id = 8, FirstName = "Sarah", LastName = "Anderson" },
            new Customer { Id = 9, FirstName = "Daniel", LastName = "Thomas" },
            new Customer { Id = 10, FirstName = "Laura", LastName = "Taylor" }
        };
        modelBuilder.Entity<Customer>().HasData(customers);

    }
}

docker-compose.yml

services:
  db:
    image: postgres
    container_name: local_pgdb
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    volumes:
      - local_pgdata:/var/lib/postgresql/data
  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin4_container
    restart: always
    ports:
      - "8888:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: postgres
    volumes:
      - pgadmin-data:/var/lib/pgadmin

volumes:
  local_pgdata:
  pgadmin-data:

EFGetStarted.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.5">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.0.4" />
  </ItemGroup>

</Project>

Program.cs

using System.Collections.Generic;
using System.Linq;

var _context = new DatabaseContext();

_context.Employees.Add(new Employee
{
    Name = "John Doe",
    Salary = 60000,
});


double _fraction = 1.25;
string _country = "USA";

var product_query = from p in _context.Products
                    join c in _context.Companies on p.CompanyID equals c.Id
                    where c.Country == _country
                    select p;

var product_query2 = _context.Products.Join
    (_context.Companies.Where(x => x.Country == _country),
        p => p.CompanyID,// outer key 
        c => c.Id, // inner key
        (p, _) => p) // result selector
        ;

var products = product_query.ToList();
Console.WriteLine($"Found {products.Count} products in country {_country}.");
foreach (var product in products)
{
    Console.WriteLine($"Product: {product.Name}");
}

_context.Products.UpdateRange(products);
_context.SaveChanges();



var query_inner_join = from e in _context.Employees
                       join d in _context.Departments
                           on e.DepartmentId equals d.DepartmentId
                       select new
                       {
                           Employee = e.Name,
                           Department = d.Name
                       };

var query_left_join = from e in _context.Employees
                      join d in _context.Departments
                          on e.DepartmentId equals d.DepartmentId into maybe_departments
                      from maybe_department in maybe_departments.DefaultIfEmpty()
                      select new
                      {
                          Employee = e.Name,
                          Department = maybe_department != null ? maybe_department.Name : "No Department"
                      };

var query_right_join = from d in _context.Departments
                       join e in _context.Employees
                           on d.DepartmentId equals e.DepartmentId into maybe_employees
                       from maybe_employee in maybe_employees.DefaultIfEmpty()
                       select new
                       {
                           Employee = maybe_employee != null ? maybe_employee.Name : "No Employee",
                           Department = d.Name
                       };


var result = query_inner_join.ToList();
// var result = query_left_join.ToList();
// var result = query_right_join.ToList();

foreach (var employee in result)
{
    Console.WriteLine($"Employee: {employee.Employee}, Department: {employee.Department}");
}

// See https://aka.ms/new-console-template for more information
Console.WriteLine("Hello, World!");

• https://learn.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=netcore-cli
• https://www.nuget.org/packages/Npgsql.EntityFrameworkCore.PostgreSQL