Key with DatabaseGeneratedOption.Identity Insert Problem

Radzen Blazor Studio
1

Hello,

I have a tabel with an Identity Key column:

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Behörde_ID { get; set; }

the problem is that there are a key row with value = 0 and the concurrency checking in the CreateBehörde checks with the value = 0

            var existingItem = Context.Behördes
                              .Where(i => i.Behörde_ID == behörde.Behörde_ID)
                              .FirstOrDefault();

            if (existingItem != null)
            {
               throw new Exception("Item already available");
            }

how to tell EF to check not with 0 but with another value (-1)

robert

2

I am not sure this can be done. EF compares the existing value (0) with values in the database (which is also zero). You could try adding an empty constructor to your entity (in a partial file to avoid Radzen Blazor Studio overwriting it) and set that property to -1.

3

can you give me an example for such a class with an empty constructor?

robert

4

Hi @Mad.Rain,

I don't understand your question. Do you need an example of creating a constructor for a C# class? It is as generic C# as it gets:

public partial class MyClass
{
    public MyClass()
    {
       MyProperty = -1;
    }
}
5

I have to change the code as follows: .Where(i => i.Behörde_ID == -1)

public async Task<PDBIntern.Models.PDB.Behörde> CreateBehörde(PDBIntern.Models.PDB.Behörde behörde)
        {
            OnBehördeCreated(behörde);

            var existingItem = Context.Behördes
                              .Where(i => i.Behörde_ID == -1)
                              .FirstOrDefault();

            if (existingItem != null)
            {
                throw new Exception("Item already available");
            }

            try
            {
                Context.Behördes.Add(behörde);
                Context.SaveChanges();
            }
            catch
            {
                Context.Entry(behörde).State = EntityState.Detached;
                throw;
            }

            OnAfterBehördeCreated(behörde);

            return behörde;
        }
6

What is stopping you from changing the code?

7

If I refresh the database it is overwritten...

8

I don't understand what refreshing database means. If you want us to help please describe what the problem is in more details. You don't seem to have tried my suggestion for some reason which you didn't explain.

9

I think that creating a partial method OnBehördeCreated and setting the key to -1 should be enough (or using the solution with the constructor).

partial void OnBehördeCreated(Behörde item)
{
    item.Behörde_ID = -1;
}

Add this in a partial class which is in the same namespace as the service.

10

the problem with that solution is that the sql statement to check if the row exists is now ok with -1

exec sp_executesql N'SELECT TOP(1) [b].[Behörde_ID], [b].[Abteilung], [b].[Behörde], [b].[Benutzer], [b].[Kurzbezeichnung], [b].[Ort], [b].[Postleitzahl], [b].[Strasse], [b].[angelegt], [b].[geändert]
FROM [dbo].[Behörde] AS [b]
WHERE [b].[Behörde_ID] = @__behörde_Behörde_ID_0',N'@__behörde_Behörde_ID_0 int',@__behörde_Behörde_ID_0=-1

but EF now adds this column also to the INSERT statement but this column is a Identity column

exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [dbo].[Behörde] ([Behörde_ID], [Abteilung], [Behörde], [Benutzer], [Kurzbezeichnung], [Ort], [Postleitzahl], [Strasse], [angelegt], [geändert])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);
',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 datetime2(7),@p9 datetime2(7)',@p0=-1,@p1=NULL,@p2=NULL,@p3=N'worofka',@p4=NULL,@p5=NULL,@p6=NULL,@p7=NULL,@p8='2023-03-06 14:17:54.5387020',@p9=NULL

which resukls in the error:

"An explicit value for the identity column cannot be inserted into the authority table when IDENTITY_INSERT is set to OFF."

robert

11

I am afraid I don't have other other solutions for this problem. You can probably delete the entire check for the existing item. Normally an item with the same ID should not exist when the ID is automatically generated.

12

OK I found a solution - created a partial method with the same name but an additional paramater int defaultValue which I use in the "check"

var existingItem = Context.Behördes
.Where(i => i.Behörde_ID == defaultValue)
.FirstOrDefault();

public partial class PDBService
    {
        public async Task<PDBIntern.Models.PDB.Behörde> CreateBehörde(PDBIntern.Models.PDB.Behörde behörde, int defaultValue)
        {
            OnBehördeCreated(behörde);

            var existingItem = Context.Behördes
                              .Where(i => i.Behörde_ID == defaultValue)
                              .FirstOrDefault();

            if (existingItem != null)
            {
                throw new Exception("Item already available");
            }

            try
            {
                Context.Behördes.Add(behörde);
                Context.SaveChanges();
            }
            catch
            {
                Context.Entry(behörde).State = EntityState.Detached;
                throw;
            }

            OnAfterBehördeCreated(behörde);

            return behörde;
        }
    }