PostgreSQL EF Core Logging

筆記 PostgreSQL 在 EF Core 啟用紀錄相關

問題 syntax error at or near "GENERATED"

結論 AsEnumerable 之前的Where會包含至SQL

之後附加的Where則不會在SQL內 (無關 Lazy Load)

Nuget

Microsoft.EntityFrameworkCore.Design

Npgsql.EntityFrameworkCore.PostgreSQL

Code

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace ConsoleApp1
{
    public class Program
    {
        public static readonly ILoggerFactory MyLoggerFactory
            = LoggerFactory.Create(builder => { builder.AddConsole(); });

        public static void Main(string[] args)
        {
            using (var db = new BloggingContext())
            {
                // Read
                Console.WriteLine("Querying for a blog start...");
                var blogs = db.Blogs.Where(x => x.BlogId > 1).AsEnumerable();
                var blog = blogs.FirstOrDefault();
                Console.WriteLine($"{blog?.Url}");
                var blog2 = blogs.Where(x => x.BlogId == 5).FirstOrDefault();
                Console.WriteLine($"{blog2?.Url}");
                Console.WriteLine("Querying for a blog finish...");
            }
        }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseLoggerFactory(Program.MyLoggerFactory)
                .UseNpgsql("Host=127.0.0.1;Database=jake;Username=jake;Password=jake",
                    options => options.SetPostgresVersion(new Version(9, 6)));
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
    }
}

Migrations 有問題記得設定 PostgreSQL 版本

SetPostgresVersion

設定後 Remove Migrations 再整個重建

因為建立表的欄位自動增長語法有改動

PostgreSQL 10

CREATE TABLE distributors (
     did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     name   varchar(40) NOT NULL CHECK (name <> '')
);

PostgreSQL 9.x

CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

參考

開始使用 EF Core

EF Core 記錄

Npgsql Entity Framework Core Provider

Generated Columns Not Compatible with PSQL 9.x

PostgreSQL 9.6.19 Documentation

PostgreSQL 10 Documentation

PS5

AsEnumerable
Entity Framework
Log
Npgsql
PostgreSQL
回首頁

本文章從點部落遷移至 Writerside

14 October 2025