search

npgsql / efcore.pg

Entity Framework Core provider for PostgreSQL
PostgreSQL License
1.54k stars 226 forks source link

Multiple Computed columns SaveChanged Exception #1928

Closed lable closed 3 years ago

lable commented 3 years ago

Multiple computed columns SaveChange occurs Exception.

generator execute sql:

INSERT INTO order_scene_202107 (id, body, created_on, modified_on)
VALUES (@p0, @p1, @p2, @p3)
RETURNING code, pay_reqeust_on, scene;

Exception:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index') at System.Collections.Generic.List`1.get_Item(Int32 index) at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Model Define:

    [Table("order_scene")]
    public class OrderScene
    {
        /// <summary>
        /// 
        /// </summary>
        [Key]
        [Column("id")]
        public Guid Id { get; set; }

        /// <summary>
        /// 
        /// </summary>
        [Column("body", TypeName = "jsonb")]
        // public JObject Body { get; set; }
        public string Body { get; set; } //不支持 JObject

        /// <summary>
        /// 创建时间
        /// </summary>
        [Column("created_on")]
        public DateTime CreatedOn { get; set; } = DateTime.Now;

        /// <summary>
        /// 修改时间
        /// </summary>
        [Column("modified_on")]
        public DateTime ModifiedOn { get; set; } = DateTime.Now;

        /// <summary>
        /// 计算列 支付请求日期
        /// </summary>
        [Column("pay_reqeust_on")]
        public DateTime PayRequestOn { get; set; }

        /// <summary>
        /// 计算列 订单编号
        /// </summary>
        [Column("code")]
        [StringLength(64)]
        public string Code { get; set; }

        /// <summary>
        /// 计算列 
        /// </summary>
        [Column("scene")]
        [StringLength(16)]
        public string Scene { get; set; }
    }
    public class OrderSceneConfiguration : IEntityTypeConfiguration<OrderScene>
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="builder"></param>
        public void Configure(EntityTypeBuilder<OrderScene> builder)
        {
            builder.Property(a => a.Body)
                .HasColumnType("jsonb")
                .HasComment("json字段");

            //builder.HasIndex(a => a.Body)
            //    .HasMethod("gin");
            //builder.HasIndex(a => a.Body)
            //    .HasMethod("gin");

            //生成计算列
            builder.Property(a => a.PayRequestOn)
               .HasColumnType("timestamp without time zone")
               .HasComputedColumnSql("to_timestamp_immutable(body ->> 'payRequestOn')", stored: true)
               .ValueGeneratedOnAddOrUpdate()
               .Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Ignore);

            builder.Property(a => a.Code)
               .HasComputedColumnSql(" body ->> 'code'", stored: true)
               .ValueGeneratedOnAddOrUpdate()
               .Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Ignore);

            builder.Property(a => a.Scene)
               .HasComputedColumnSql(" body ->> 'scene'", stored: true)
               .ValueGeneratedOnAddOrUpdate()
               .Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Ignore);

            // 索引
            builder.HasIndex(a => new { a.CreatedOn, a.Code });

            //builder.Property(a => a.CreatedOn)
            //    .HasColumnType("timestamp without time zone");
            builder.Property(a => a.CreatedOn)
                .HasColumnType("timestamp without time zone");
            builder.Property(a => a.ModifiedOn)
                .HasColumnType("timestamp without time zone");
        }
    }

Migration:

            migrationBuilder.CreateTable(
                name: "order_scene",
                columns: table => new
                {
                    id = table.Column<Guid>(type: "uuid", nullable: false),
                    created_on = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    body = table.Column<JObject>(type: "jsonb", nullable: true, comment: "json字段"),
                    modified_on = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    pay_reqeust_on = table.Column<DateTime>(type: "timestamp without time zone", nullable: false, computedColumnSql: "to_timestamp_immutable(body ->> 'payRequestOn')", stored: true),
                    code = table.Column<string>(type: "character varying(64)", maxLength: 64, nullable: true, computedColumnSql: " body ->> 'code'", stored: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_order_scene", x => x.id);
                });

            migrationBuilder.CreateIndex(
                name: "IX_order_scene_created_on_code",
                table: "order_scene",
                columns: new[] { "created_on", "code" });

table ddl:

CREATE TABLE IF NOT EXISTS public.order_scene_202107
(
    id uuid NOT NULL,
    created_on timestamp without time zone NOT NULL,
    body jsonb,
    modified_on timestamp without time zone NOT NULL,
    pay_reqeust_on timestamp without time zone GENERATED ALWAYS AS (to_timestamp_immutable((body ->> 'payRequestOn'::text))) STORED,
    code character varying(64) COLLATE pg_catalog."default" GENERATED ALWAYS AS ((body ->> 'code'::text)) STORED,
    scene character varying(16) COLLATE pg_catalog."default" GENERATED ALWAYS AS ((body ->> 'scene'::text)) STORED,
    CONSTRAINT "PK_order_scene_202107" PRIMARY KEY (id)
)

The SQL was manually executed successfully：

INSERT INTO order_scene_202107 (id, body, created_on, modified_on)
VALUES ('08d94d7f-4b8f-15fc-a9b7-c30490037c80', 
        '
{
    "qr": "RRQV0j5Y",
    "fee": 0,
    "bank": "微信",
    "body": null,
    "code": "6824279983075430402",
    "money": 22,
    "scene": "PARK",
    "trxid": null,
    "canPay": false,
    "cardNO": null,
    "errMsg": null,
    "remark": null,
    "result": null,
    "source": null,
    "status": 3,
    "termNo": "RRQV0j5Y",
    "termid": "39f7ef18-f5eb-595f-6193-0084c0a12fd9",
    "bizCode": "6824279982941212673",
    "inOutId": null,
    "jumpUrl": "http://bong17.com",
    "outTime": 0,
    "payDate": null,
    "payType": null,
    "srcCode": null,
    "trxcode": null,
    "authCode": null,
    "exitDate": null,
    "limitPay": null,
    "parkName": "测试",
    "parkTime": "59分",
    "payOrgan": null,
    "payParms": null,
    "termName": null,
    "typeName": "消费",
    "brancheId": 158,
    "brancheNo": "00001",
    "notifyUrl": null,
    "parkingID": "1",
    "payAmount": 0,
    "sceneFlag": 0,
    "validtime": null,
    "cashAmount": 0,
    "customerId": null,
    "merchantId": 88,
    "merchantNo": "KB-202003056280",
    "moneyMoney": 0,
    "outStation": null,
    "statusName": "xxx",
    "trxreserve": null,
    "brancheName": "xxx",
    "payDateDate": null,
    "payNotifyOn": "2021-07-23T10:43:12.2164104+08:00",
    "plateNumber": "云A123456",
    "refundedAmt": 0,
    "requestAcct": null,
    "calculatDate": "2021-07-23 10:12:16",
    "customerCode": "6778584527813345294",
    "customerInfo": null,
    "customerName": null,
    "entranceDate": null,
    "lastModifyOn": null,
    "merchantName": "xxxx",
    "onLineAmount": 0,
    "parkTimeLong": 59,
    "payRequestOn": "2021-07-23T10:12:16.9571086+08:00",
    "providerName": "演示",
    "apiResponseOn": "2021-07-23T10:12:16.9251327+08:00",
    "parkOrderCode": "DEMO210723101216778328",
    "payResponseOn": null,
    "payResultBody": null,
    "discountAmount": 0,
    "outStationName": null,
    "payChannelCode": "ALLINPAY_H5_CASHIER",
    "payProductName": null,
    "parkOrderSerial": "210723101216187813",
    "calculatDateDate": "2021-07-23T10:12:16.9198318+08:00",
    "entranceDateDate": null,
    "outstandingAmount": 0
}       
        ', '2021-07-23 10:43:12.235632', '2021-07-23 10:43:12.235632')
RETURNING code, pay_reqeust_on, scene;

微信截图_20210726165514

lable commented 3 years ago

Try adding the computed column fields one by one！ Why? 微信截图_20210726171739 1、 11 2、 微信截图_20210726171822 3、 3