使用实体框架插入后更新多列

Question

我在表上创建了一个数据库触发器，该触发器在插入后更新表中的字段。使用 EF 进行插入时，我将获得 ID 和编号。在数据库上我创建了以下代码：

create table Things (

    ID int primary key identity not null,

    Number nvarchar(20)

);

create trigger UpdateThingsNumberTrigger on Things

after insert

as

begin

    declare @month nvarchar(2);

    select @month = cast(month(getdate()) as nvarchar(2));

    declare @code nvarchar(15);

    select @code = cast(year(getdate()) as nvarchar(4)) + 

        '.' + 

        replicate('0', 2 - len(@month)) + 

        @month + 

        '.';

    declare @max nvarchar(20);

    select @max = t.ID

    from Things t

    where ID like @code + '%';

    with CTE_UPD as 

    (

        select 

            replicate('0', 

                4 -

                len(cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)))) + 

                cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)) as NextNo, 

            ID

        from Things ins

    )   

    update Things

    set Number = @code + NextNo 

    from Things t inner join CTE_UPD ins on ins.ID = t.ID;

end

我的代码的这一部分工作正常，忽略触发器内的逻辑缺陷......我将在这个问题中尝试解决的问题是当我从实体框架（首先是数据库）在表中插入一个东西时。这是我的代码和输出：

using (Database db = new Database())

{

    Thing thing = new Thing(); // --> just an empty constructor.

    db.Entry(thing).State = EntityState.Added;

    await db.SaveChangesAsync();

    Console.WriteLine($"ID = {thing.ID}");

    Console.WriteLine($"Number = {thing.Number}");

}

// Output:

// ID = 1

// Number = 

在后台 EF 调用时在服务器上执行此代码SaveChangesAsync()：

INSERT [dbo].[Things]([Number])

VALUES (NULL)

SELECT [ID]

FROM [dbo].[Things]

WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()

现在可以 EF 更新 C# 对象中的 ID。但是，在关闭 using 块之前，如何在不使用下面的代码的情况下获取数字？

Thing recentlyInsertedThing = await db.Things.FindAsync(thing.ID);

Answer 1

我发现它无需编写第二个select语句即可获取 ID 和编号。这是我的代码：using (Database db = new Database()){&nbsp; &nbsp; Thing thing = new Thing();&nbsp; &nbsp; string sql = @"insert into Things()&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; values ();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select ID, Number&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from Things&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where @@rowcount > 0 and ID = scope_identity();";&nbsp; &nbsp; KeyMapper recentlyRecevedKeys = await db&nbsp; &nbsp; &nbsp; &nbsp; .Database&nbsp; &nbsp; &nbsp; &nbsp; .SqlQuery<KeyMapper>(sql)&nbsp; &nbsp; &nbsp; &nbsp; .FirstAsync();&nbsp; &nbsp; thing.ID = recentlyRecevedKeys.ID;&nbsp; &nbsp; thing.Number = recentlyRecevedKeys.Number;&nbsp;&nbsp;}// Nested classprivate class KeyMapper{&nbsp; &nbsp; public int ID { get; set; }&nbsp; &nbsp; public string Number { get; set; }}