Windows 11 Professional
Docker Desktop 4.34.3 (170107)
ASP.NET Core 8.0
PostgreSQL 17.0

services:
  db:
    image: postgres:17
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

-- 新しいユーザーの作成
CREATE USER app_user WITH PASSWORD 'password';

-- 新しいユーザーに権限を付与
GRANT ALL PRIVILEGES ON DATABASE postgres TO app_user;

-- ユーザーを管理するテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,         -- ユーザーID (自動生成)
    username VARCHAR(50) NOT NULL, -- ユーザー名
    email VARCHAR(100) UNIQUE NOT NULL, -- メールアドレス (ユニーク)
    password_hash VARCHAR(255) NOT NULL, -- パスワードのハッシュ
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 作成日時
);

-- TODO項目を管理するテーブル
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,         -- タスクID (自動生成)
    user_id INT NOT NULL,          -- 関連ユーザーのID
    title VARCHAR(255) NOT NULL,   -- タスクのタイトル
    description TEXT,              -- タスクの説明 (任意)
    is_completed BOOLEAN DEFAULT FALSE, -- タスクが完了しているかどうか
    due_date DATE,                 -- 期限 (任意)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 作成日時
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 更新日時
    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) -- ユーザーIDに対する外部キー
);

-- インデックスを作成して検索を高速化
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);

-- サンプルユーザーとタスクを挿入 (データの確認用)
INSERT INTO users (username, email, password_hash) VALUES
('john_doe', '[email protected]', 'hashed_password'),
('jane_doe', '[email protected]', 'hashed_password');

INSERT INTO tasks (user_id, title, description, due_date) VALUES
(1, 'Buy groceries', 'Milk, eggs, and bread', '2024-10-30'),
(1, 'Finish project', 'Complete the project by the end of the week', '2024-11-01'),
(2, 'Plan vacation', 'Book tickets and find accommodations', '2024-11-15');

docker compose up -d

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*",

    "ConnectionStrings": {
        "PostgreSqlConnection": "Host=localhost;Port=5432;Database=postgres;Username=postgres;Password=postgres"
    }
}

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Entity
{
    public class User
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Required]
        [MaxLength(50)]
        [Column("username")]
        public string Username { get; set; }

        [Required]
        [MaxLength(100)]
        [Column("email")]
        public string Email { get; set; }

        [Required]
        [MaxLength(255)]
        [Column("password_hash")]
        public string PasswordHash { get; set; }

        [Column("created_at")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;

        // タスクとのリレーション
        public ICollection<Task> Tasks { get; set; } = new List<Task>();
    }
}

using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace Entity
{
    public class Task
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Required]
        [MaxLength(255)]
        [Column("title")]
        public string Title { get; set; }

        [Column("description")]
        public string Description { get; set; }

        [Column("is_completed")]
        public bool IsCompleted { get; set; } = false;

        [Column("due_date")]
        public DateTime? DueDate { get; set; }

        [Column("created_at")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;

        [Column("updated_at")]
        public DateTime UpdatedAt { get; set; } = DateTime.Now;

        // 外部キーの定義
        [ForeignKey("User")]
        [Column("user_id")]
        public int UserId { get; set; }
        public User User { get; set; }
    }
}

using Entity;
using Microsoft.EntityFrameworkCore;

namespace Context
{
    public class ApplicationDbContext : DbContext
    {
        public DbSet<Entity.User> Users { get; set; }
        public DbSet<Entity.Task> Tasks { get; set; }
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Entity.User>()
                .ToTable("users");

            // tasksテーブルのUserへの外部キー制約を設定
            modelBuilder.Entity<Entity.Task>()
                .ToTable("tasks")
                .HasOne(t => t.User)
                .WithMany(u => u.Tasks)
                .HasForeignKey(t => t.UserId)
                .OnDelete(DeleteBehavior.Cascade);

            base.OnModelCreating(modelBuilder);
        }
    }
}

using Microsoft.EntityFrameworkCore;
using Context;

var builder = WebApplication.CreateBuilder(args);

// PostgreSQLへの接続を設定
var connectionString = builder.Configuration.GetConnectionString("PostgreSqlConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString));

// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

using Context;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace WebApplication2.Controllers
{
    public class TasksController : Controller
    {
        private readonly ApplicationDbContext _context;

        public TasksController(ApplicationDbContext context)
        {
            _context = context;
        }

        // GET: Tasks
        public async Task<IActionResult> Index()
        {
            var tasks = await _context.Tasks
                .Include(t => t.User) // ユーザー情報も一緒に取得
                .ToListAsync();

            return View(tasks); // ビューにタスクのリストを渡す
        }

    }
}

@model IEnumerable<Entity.Task>

@{
    ViewData["Title"] = "Tasks List";
}

<h2>Tasks List</h2>

<table class="table">
    <thead>
        <tr>
            <th>タスクID</th>
            <th>ユーザー</th>
            <th>タイトル</th>
            <th>説明</th>
            <th>完了</th>
            <th>期限</th>
            <th>作成日時</th>
            <th>更新日時</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var task in Model)
        {
            <tr>
                <td>@task.Id</td>
                <td>@task.User?.Username</td>
                <td>@task.Title</td>
                <td>@task.Description</td>
                <td>@task.IsCompleted</td>
                <td>@task.DueDate?.ToString("yyyy-MM-dd")</td>
                <td>@task.CreatedAt.ToString("yyyy-MM-dd HH:mm:ss")</td>
                <td>@task.UpdatedAt.ToString("yyyy-MM-dd HH:mm:ss")</td>
            </tr>
        }
    </tbody>
</table>

using Npgsql;

var connString = "Host=localhost;Username=my_user;Password=my_password;Database=my_database";

using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();

    using (var cmd = new NpgsqlCommand("SELECT * FROM my_table", conn))
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetString(0)); // 0はカラムインデックス
        }
    }
}

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Username=my_user;Password=my_password;Database=my_database");
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// 使用例
using (var context = new ApplicationDbContext())
{
    var entities = context.MyEntities.Where(e => e.Name == "example").ToList();
}