How To Resolve EFCore 8 Saving to Table with Trigger

Question

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");
});

Answer 1

you did not provide any code so we can only guess. Maybe you are facing the following issue?

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes?tabs=v7#sql-server-tables-with-triggers-or-certain-computed-columns-now-require-special-ef-core-configuration

Answer 2

Hi,

And you checked that your SP doesn't return any extra resultset you could have shown while writing or debugging your SP?

Answer 3

In general the "table /w trigger" in combination with EF Core is caused by the fact that EF Core now generates update code that doesn't work with tables that have triggers. To work around this you have to use a convention to switch EF back to the older code. This is documented here.

But I don't think that is the issue with your code. Your code is a casting error. My gut instinct is that your trigger doesn't include a SET NOCOUNT ON or similar and/or it is using a SELECT directly. The net result is that the trigger is generating additional data in the resultset that EF is picking up and since it doesn't care about triggers it is trying to force the result into what it expected from the results of the insert/update and hence the cast is failing.

Ensure your trigger is not returning anything (including a count). Any SELECTs need to be into tables or as part of inserts/updates. Any data returned by a trigger will cause issues with EF.