I have a Web API based on EF Core 8 that inserts a record per time into a table A based on a post to one of its endpoints. The table has an insert trigger that fires to executes a stored procedure that updates the record in another table B if the value of a field of the insert matches the value of an identical field of a record in table B
The SP also additionally inserts new records into two other tables C & D.
The problem I'm having is that when I insert into table A from SSMS and it matches a record in table B, the trigger fires and executes the SP to update the record in table B and inserts records into tables C & D successfully. The Web API however experiences failure doing the same. The error it returns is:
Microsoft.EntityFrameworkCore.Update[10000] An exception occurred in the database while saving changes for context type 'Project_Context_name'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Int64'.
If the insert does not match a record in table B, it succeeds without error. It is only if the inserted record matches the condition indicated for updating table B that the API throws the error.
The only column in table A that has a System.Int64 date type is the id column, which is automatically created and not been inserted into, hence no conversion. The column being updated in table B is also not of int64 data type, so no conversion to int64 is involved.
Here is a snapshot of the structure of table A with the trigger
Here is a snapshot of the table B
My Web API code already incorporates the mitigation advised for EF Core 8 when working with SQL tables that have triggers. The particular error I have having says it cannot cast from System.String data type to System.Int64 data type. There is no field in either tables requires conversion.
Like I said, everything works fine if I insert into the table via SSMS. It is the EF Core 8 Web API that returns the error.
Here is my code for the entity in the dbcontext
modelBuilder.Entity<I----------ns>(entity =>
{
entity
.ToTable(tb => tb.HasTrigger("trg----aid"));
entity
.ToTable("In--------tions");
entity.Property(e => e.id)
.HasColumnType("biginteger");
entity.Property(e => e.In------tion)
.HasMaxLength(100)
.IsUnicode(false);
entity.Property(e => e.I------No)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.R----o)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.I------Date)
.HasColumnType("smalldatetime");
entity.Property(e => e.I-----ID)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.I-----tems)
.HasMaxLength(250)
.IsUnicode(false);
entity.Property(e => e.I-------Name)
.HasMaxLength(200)
.IsUnicode(false);
entity.Property(e => e.I--------o)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.I-------Email)
.HasMaxLength(100)
.IsUnicode(false);
entity.Property(e => e.I----Amount)
.HasColumnType("decimal(38,2)");
entity.Property(e => e.I-----arges)
.HasColumnType("decimal(38,2)");
entity.Property(e => e.I------able)
.HasColumnType("decimal(38,2)");
entity.Property(e => e.A-----bited)
.HasColumnType("decimal(38,2)");
entity.Property(e => e.D---Date)
.HasColumnType("smalldatetime");
entity.Property(e => e.C---el)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.C----ID)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.T------ID)
.HasMaxLength(100)
.IsUnicode(false);
entity.Property(e => e.R-----N)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.S-------N)
.HasMaxLength(20)
.IsUnicode(false);
entity.Property(e => e.N----------ode)
.HasMaxLength(10)
.IsUnicode(false);
entity.Property(e => e.N--------sage)
.HasMaxLength(200)
.IsUnicode(false);
entity.Property(e => e.A-------it)
.HasMaxLength(200)
.IsUnicode(false);
entity.Property(e => e.R---------rks)
.HasMaxLength(500)
.IsUnicode(false);
entity.Property(e => e.B---------k)
.HasMaxLength(100)
.IsUnicode(false);
entity.Property(e => e.P-----------ment)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.C----------er)
.HasMaxLength(100)
.IsUnicode(false);
entity.Property(e => e.C--------------me)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.C-----------N)
.HasMaxLength(50)
.IsUnicode(false);
entity.Property(e => e.C------------y)
.HasMaxLength(6)
.IsUnicode(false);
entity.Property(e => e.N------------ate)
.HasColumnType("datetime");
});