Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have an issue during an insert operation into the table using Entity Framework Core.

_context.Entry(item).State = EntityState.Added;
var r = await _context.ServiceWorkOrders.AddAsync(item);
_context.SaveChangesAsync(); <-- (fails)
Some context when dealing with this issue.
The table is owned by client so I have to work around this issue 
The table to insert into, contains triggers and stored procedures set to run after Insert/Update/Delete operations.
The table have relationship properties (Foreign keys)
The table's primary key is set to auto-increment, hence the primary key field of the inserting entity is set to 0, along with the fields that is required during this insert. 
I am trying to use Stored Procedure directly using ExecuteSqlCommand, but I would prefer to use EF to manage the database access. Moreover, correct me if I am wrong, I would have to list all the optional parameters in the Stored Procedure in order to add the entity in to prevent writing into the wrong fields. Currently this method inserts the entity, but it writes on the wrong fields, even if I used SqlParameters("@named_field", value).
I have tried using the Synchronous method as well, but it gives the same exception.
The exception returned:
  
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: 'Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.'
edit: 
Here is the entity model:
[Table("ASM_ServiceWorkOrder")]
public class ServiceWorkOrder: BaseEntity
    [Key, Column(name: "ROWUID"), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int RowUID { get; set; }
    [Column(TypeName = "nvarchar(25)")]
    public string CompanyID { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string DocNumber { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string RevisionNumber { get; set; }
    [Column(TypeName = "nvarchar(40)")]
    public string CustomerDirectoryID { get; set; }
    [ForeignKey("CustomerLocation")]
    public Int32? CustomerLocationRowUID { get; set; }
    public AssetLocation CustomerLocation { get; set; }
    [Column(TypeName = "nvarchar(50)")]
    public string WorkOrderType { get; set; }
    [Column(TypeName = "nvarchar(25)")]
    public string IssueType { get; set; }
    [Column(TypeName = "nvarchar(40)")]
    public string AssetItemCode { get; set; }
    [Column(TypeName = "nvarchar(40)")]
    public string AssetSerialNo { get; set; }
    [ForeignKey("AssetRegister")]
    public int? AssetRegisterROWUID { get; set; }
    public AssetRegister AssetRegister { get; set; }
    [Column(TypeName = "nvarchar(40)")]
    public string ProjectDirectoryID { get; set; }
    [Column(TypeName = "nvarchar(10)")]
    public string Priority { get; set; }
    [Column(TypeName = "nvarchar(25)")]
    public string Status { get; set; }
    [Column(TypeName = "nvarchar(25)")]
    public string StatusForClient { get; set; }
    public bool? Billable { get; set; }
    [Column(TypeName = "decimal(18, 2)")]
    public decimal? QuotedFee { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime? DueDate { get; set; }
    [Column(TypeName = "nvarchar(240)")]
    public string Description { get; set; }
    [Column(TypeName = "nvarchar(50)")]
    public string ReportedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime? ReportedDate { get; set; }
    [Column(TypeName = "nvarchar(35)")]
    public string BusinessDataType { get; set; }
    [Column(TypeName = "nvarchar(240)")]
    public string DocRemarks { get; set; }
    [Column(TypeName = "nvarchar(2000)")]
    public string ErrorText { get; set; }
    public Guid? RowGlobalUID { get; set; }
    public Int32? HeaderROWUID { get; set; }
    [Column(TypeName = "datetime"), DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime? DateOfDocument { get; set; }
    //[Timestamp]
    //public byte RowVersion { get; set; }
    //public IList<ServiceWorkOrderAttachment> Attachments { get; set; }
    public IList<ServiceWorkOrderDetails> Details { get; set; }
                Please post the entity class along with relevant data annotations/fluent configuration. If PK is configured properly and is initially 0, it should become negative value after executing _context.Entry(item).State = EntityState.Added;
– Ivan Stoev
                Sep 12, 2019 at 12:42
                yes the PK turned negative after running that line, but at the point of SaveChangesAsync, it throws the exception.
– John Kuan
                Sep 12, 2019 at 14:08
                Hmm, may be the insert trigger somehow is affecting the scope_identity() returned value (although it shouldn't). Can you disable the insert trigger for that table in your test database and see it works? Just to verify whether the cause of the issue is the trigger or something else.
– Ivan Stoev
                Sep 12, 2019 at 15:03
                Looks like you are experiencing the following EF Core bug github.com/aspnet/EntityFrameworkCore/issues/10443. Unfortunately not fixed (and not even scheduled to be addressed). Looks like inserting with raw SQL is the only option.
– Ivan Stoev
                Sep 12, 2019 at 17:10
                oh man.. that sucks.. I chanced upon that link just moments before I saw yours. Regardless, thanks for the assist!
– John Kuan
                Sep 12, 2019 at 17:53
Are u sure u created ITEM? And indicate table where u try save row?
If u wanna insert row in table u must create your item first.
f.ex: 
var = new ServiceWorkOrders(){Column1=var1,Column2=var2 etc.};
await _context.ServiceWorkOrders.AddAsync<ServiceWorkOrders>(item);
await _context.SaveChangesAsync();
                @llyas Yes I created the item from a mobile frontend and passed back through a POST method. I am using Mapper to map the DTO to model, and with regards to the mapping, it is working fine. The item is mapped as intended before inserting.   I am using data annotations to configure the model.
– John Kuan
                Sep 12, 2019 at 12:28
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.