How to deal with SQL Server Sequences?


in my database, I have a table with a field that has, as default value, defined a squence number. So if I do an INSERT statement and provide no actual value for this field, the next number of the sequence is calculated by SQL Server and used here (pretty similar to an identity column).

The value is defined by a constraint:

ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_AccountNo_Seq] DEFAULT (NEXT VALUE FOR [dbo].[AccountNoSequence]) FOR [AccountNo]

Radzen has generated this code in my data context class:

          .Property(p => p.AccountNo)
          .HasDefaultValueSql("(NEXT VALUE FOR [dbo].[AccountNoSequence])").ValueGeneratedNever();

The problem is now, if I create a form in Radzen to add new data to that "account" table, Radzen will always try to use "0" as value for this "AccountNo" field. How can I force Radzen to use the new/next sequence number. Again, if I would do a manual insert in SQL to that table, I would not specify the field at all. However, excluding the field from the data model in Radzen is also no option since I need to display the values of this field in a different page.

Any help would be welcome! Thanks!

The value will stay 0 since it will be calculated by your SQL server and your application will not know the new value until the new item is inserted.

Hi @enchev - thanks for your reply! That was also my first thought. But this does not work here. I actually inserts the value "0" and next time, I do another insert, I get an error that a record with "0" already exists since I have also defined this field to be unique.

I was able to fix this by removing .ValueGeneratedNever(); from the context class. However, when ever I scaffold the database again, this will get lost.

We will improve our generated code for the next update later this week to not set this. In the meantime you can add the DbContext to your application ignore list to keep your changes on subsequent runs.

1 Like