Update multiple columns after insert using Entity Framework

Question

I've created a database trigger on my table that updates a field in the table after an insert. When doing an insert using EF I'll get the ID and the number. On the database I've created this code:

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

^{Note: For the logical flaw inside the trigger, I'll refer to Create an incremental number with year and month without updating the entire table using a trigger on Database Administrators SE.}

This part of my code works fine, ignoring the logical flaw inside the trigger… The problem I'll try to solve in this question, is when I insert a thing in my table from Entity Framework (database first). There is my code and the output:

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 =

In the background EF is doing this code on the server when calling SaveChangesAsync():

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

SELECT [ID]
FROM [dbo].[Things]
WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()

Now can EF update the ID in the C# object. But how could I get the number without using code below before closing the using block?

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

Your trigger has a major logical flaw...it never references the inserted virtual table. That means you are updating the entire table every time you insert a new row. — Sean Lange, Aug 22 '19 at 14:30
@SeanLange: what do you mean? It works every new row got an new number. The rest is unchanged. — H. Pauwelyn, Aug 22 '19 at 14:36
You are updating every row in Things every time you insert a new row. You just happen to be updating the existing rows to the same value. A trigger should reference the inserted and/or deleted virtual tables. Also, why not use an identity instead of rolling your own? — Sean Lange, Aug 22 '19 at 14:39
@SeanLange: S***, now I see. When removing one thing, the numbers after are changed too. Could you give me an example how you would do it? — H. Pauwelyn, Aug 22 '19 at 14:49
I can help but I am not quite sure what you are trying to do. Can you explain what you are trying to do to the Number column. — Sean Lange, Aug 22 '19 at 14:52
@SeanLange It must be an unique and constant number beginning with the year and month of the time when it's created, followed by an index number. A bit like a primary key. The number is added to replace the ID in future versions of the database. — H. Pauwelyn, Aug 22 '19 at 15:01
So you want an incremental number for each year and month? That is not a great approach to numbering as it is kind of ugly to implement. Not to mention you can end up reusing values when a row is deleted. This has all the tell tale signs of being an [xy problem](http://xyproblem.info/) I would ask why an identity isn't good enough. You already have a number that is constant and guaranteed to be unique. Do you record the date the row was inserted in another column. Maybe a computed column here would meet your goals? — Sean Lange, Aug 22 '19 at 15:07
You can use a SEQUENCE and a default value expression to assign a custom, incrementing value. See eg: https://stackoverflow.com/questions/51693101/how-to-create-a-custom-auto-generated-id-number-for-a-primary-key-column and https://stackoverflow.com/questions/51459805/generate-a-unique-time-based-id-on-a-table-in-sql-server for examples — David Browne - Microsoft, Aug 22 '19 at 16:28
@SeanLange: I've created another question for that flaw on Database Administrators SE. https://dba.stackexchange.com/questions/246065/create-an-incremental-number-with-year-and-month-without-updating-the-entire-tab — H. Pauwelyn, Aug 23 '19 at 06:59
@DavidBrowne-Microsoft: I've created another question for that flaw on Database Administrators SE. https://dba.stackexchange.com/questions/246065/create-an-incremental-number-with-year-and-month-without-updating-the-entire-tab — H. Pauwelyn, Aug 23 '19 at 07:00

score 0 · Accepted Answer · answered Aug 23 '19 at 09:13

I've found it to get the ID and the Number without writing a 2^nd select statement. This is my code:

using (Database db = new Database())
{
    Thing thing = new Thing();
    string sql = @"insert into Things() 
                    values ();
                    select ID, Number
                    from Things
                    where @@rowcount > 0 and ID = scope_identity();";

    KeyMapper recentlyRecevedKeys = await db
        .Database
        .SqlQuery<KeyMapper>(sql)
        .FirstAsync();

    thing.ID = recentlyRecevedKeys.ID;
    thing.Number = recentlyRecevedKeys.Number;  
}

// Nested class
private class KeyMapper
{
    public int ID { get; set; }
    public string Number { get; set; }
}

Update multiple columns after insert using Entity Framework

1 Answers1