How To Resolve EFCore 8 Saving to Table with Trigger

Boluwade Kujero 0 Reputation points
2024-10-28T11:31:39.85+00:00

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

mytable

Here is a snapshot of the table B

desttable

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");
});
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
747 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,925 questions
{count} votes

3 answers

Sort by: Most helpful
  1. AgaveJoe 28,456 Reputation points
    2024-10-28T11:55:23.4833333+00:00

  2. PatriceSc 166 Reputation points
    2024-10-29T13:47:32.32+00:00

    Hi,

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

    0 comments No comments

  3. Michael Taylor 54,481 Reputation points
    2024-10-29T14:17:33.1966667+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.