きなこもち.net

.NET Framework × UiPath,Orchestrator × Azure × AWS × Angularなどの忘備録

postgres × RETURNING句 × Entity Framework coreで値を受け取ってみた

ASP.NET Core postgres
Hatena

この記事の目的

この記事は、
Entity Framework coreでPostgresのReturning句から取得できる値を受け取る処理の実装メモ
を目的としています。

本題

もろもろの背景

Postgresには、更新された行のデータを取得するためのReturning句が用意されています。※ほかのDBは知らないですが・・・

例えば・・・主キーにserialやbigserialなどの型を使っている場合、Insertをしたときに自動で採番されます。この時、Insertした時の自動採番された値を取得するためには、再度Selectしないといけなくなります。この二回目のSQL実行をしなくて済むようにするのがReturning句だと思っています！
詳しくはここ↓
www.postgresql.jp

Returningでは、Insertを実行した後、結果を受け取る必要があります。

それを踏まえてEntity Framework coreのInsert処理の実装を見てみると、こうなります・・・

using (var context = new BloggingContext())
{
    var blog = context.Blogs.First();
    context.Blogs.Remove(blog);
    context.SaveChanges();
}

Entity FrameworkではSaveChanges時にSQLが反映されるため、単純にReturning句を使った処理が実装できません。そんなところを調査しようとしたのが背景になります。

ちなみに、Entity Frameworkの詳細はここ↓
docs.microsoft.com

Npgsqlのドキュメントはここ↓
www.npgsql.org

環境準備

試し実装するためにした準備をまとめておきます。
①ソリューション作成（.NET coreのコンソールアプリケーション）
②NugetでNpgsql.EntityFrameworkCore.PostgreSQLをインストール
③DB作成
※テーブルは以下のように定義

-- Table: public.serial_test

-- DROP TABLE public.serial_test;

CREATE TABLE public.serial_test
(
    bigserial_id bigint NOT NULL DEFAULT nextval('serial_test_bigserial_id_seq'::regclass),
    sample_value character varying COLLATE pg_catalog."default",
    CONSTRAINT serial_test_pkey PRIMARY KEY (bigserial_id)
)

TABLESPACE pg_default;

ALTER TABLE public.serial_test
    OWNER to postgres;

④dotnetコマンドを使って、EFのオブジェクトを作成

  • 注意点１：接続文字列は、自分の環境にあったものに変更します。
  • 注意点２：Microsoft.EntityFrameworkCore.Designがインストールされていないと、エラーが発生するので、あらかじめNugetでインストールする必要があります。
  • 注意点３：コマンドの実行は、オブジェクトを追加したいプロジェクトフォルダです。VSのプロジェクトを右クリックして「エクスプローラーで開く」を選択したときに表示されるフォルダがそれにあたります。
  • 注意点４：注意点３のディレクトリに既にContext.csファイルがあると実行時エラーになるので、削除するか、更新するコマンドを実行します。[dotnet ef]コマンドの詳細は、以下のリンクから参照できます。docs.microsoft.com
  • 注意点５：実行するdotnetコマンドのバージョンをプロジェクトのターゲットフレームワークで指定しているバージョンと同じにします。dotnetコマンドのバージョン指定については、以下のドキュメントで確認できます。docs.microsoft.com
  • 注意点６：このコマンドで自動生成できるオブジェクトは、主キーが設定されているテーブルに限定されます。


以外に注意点が多かったですｗまとめてみてよかったｗ
ということで、自分の環境では、以下のコマンドを実行しました。

dotnet ef dbcontext scaffold "Host=localhost;Database=dotnet;Username=postgres;Password=dotnet" Npgsql.EntityFrameworkCore.PostgreSQL

ここまでで、今回利用するDB、テーブルと、EFの設定が完了しました。

実装例その１

まず、EFのAddメソッドを使ったInsertを見てみます。

using (var db = new dotnetContext())
{
  db.SerialTest.Add(new SerialTest()
  {
    SampleValue = "kirito"
   }); ;
   db.SaveChanges();
}


このSaveChangeメソッドの実行時に、Insertして自動採番されたIdを取得したいのですが、このメソッドの定義を見る限り値を返すI/Fは定義されていません。※DBに影響があったレコード数はintで返ってくるようですが。。。

//
// 概要:
//     Saves all changes made in this context to the database.
//
// 戻り値:
//     The number of state entries written to the database.
public virtual int SaveChanges(bool acceptAllChangesOnSuccess);

//
// 概要:
//     Saves all changes made in this context to the database.
//
// 戻り値:
//     The number of state entries written to the database.
public virtual int SaveChanges();

実装例その２

Addコマンドだけでは、やりたいことが実現できそうにないので、SQLを実行する方式で実装してみます。

using (var db = new dotnetContext())
using (var connection = db.Database.GetDbConnection())
  {
    connection.Open();
    using (var transaction= connection.BeginTransaction())
    {
        var command = connection.CreateCommand();
        command.CommandText = "INSERT INTO public.serial_test(sample_value) VALUES ('asuna') returning bigserial_id";
        var returningItem = command.ExecuteScalar();
        Console.WriteLine($"returning item is {returningItem.ToString()}");
        transaction.Commit();
    }
}

Sql Commandのインスタンスを取得して、ExecuteScalarを実行することで、Insertしたレコードの任意の値を取得することができました。今回は、任意の値の部分を自動採番されたIdとしています。

トランザクションを張らないと、postgresのオートコミットによって自動的にコミットまでされるため、実際のアプリを開発する際には、トランザクションのスコープをよく考える必要がありますね。

tips

dotnet efコマンドを使ってオブジェクトを作成すると、プロジェクト直下にテーブルの数＋ContextだけのCSファイルが生成されます。フォルダ構成をきっちりしている場合、Modelフォルダとか、適切なフォルダにまとめて管理したくなります。そんな時は、以下のオプションを使うことで、任意のフォルダに配置できるようになります。あらかじめフォルダがなくても、フォルダごと自動生成してくれるので便利です( ﾟДﾟ)

>dotnet ef dbcontext scaffold --help

Usage: dotnet ef dbcontext scaffold [arguments] [options]

Arguments:
The connection string to the database.
The provider to use. (E.g. Microsoft.EntityFrameworkCore.SqlServer)

Options:
-d|--data-annotations Use attributes to configure the model (where possible). If omitted, only the fluent API is used.
-c|--context The name of the DbContext.
--context-dir The directory to put DbContext file in. Paths are relative to the project directory.
-f|--force Overwrite existing files.
-o|--output-dir The directory to put files in. Paths are relative to the project directory.
--schema ... The schemas of tables to generate entity types for.
-t|--table ... The tables to generate entity types for.
--use-database-names Use table and column names directly from the database.
--json Show JSON output.
-p|--project The project to use.
-s|--startup-project The startup project to use.
--framework The target framework.
--configuration The configuration to use.
--runtime The runtime to use.
--msbuildprojectextensionspath The MSBuild project extensions path. Defaults to "obj".
--no-build Don't build the project. Only use this when the build is up-to-date.
-h|--help Show help information
-v|--verbose Show verbose output.
--no-color Don't colorize output.
--prefix-output Prefix output with level.

stackoverflow.com


あと、今回はDBをDockerで用意したので、yamlもメモしておきます

version: '2'
services:
db:
image: postgres
volumes:
- ./postgresql:/var/lib/postgresql
ports:
- "5432:5432"
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: dotnet
POSTGRES_DB: dotnet
adm:
image: dpage/pgadmin4
volumes:
- ./pgadmin:/var/lib/pgadmin
# - ./pgadmin:/var/lib/pgadmin/storage
ports:
- "80:80"
- "443:443"
environment:
PGADMIN_DEFAULT_EMAIL: dotnet@dotnet
PGADMIN_DEFAULT_PASSWORD: dotnet

docker-compose.ymlファイルに↑の内容をコピペして、docker-compose up でドーンと作成します。おてがるｗ




お問い合わせ プライバシーポリシー