Cannot Insert new Data in the Database in .NET Core API error Database operation

Question

I am working on an API and when I started adding new data. I received this error. It was working when I manually add the ID every input but now I got this error and after adding some solutions from here its still not working.

Error:

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.

Code for insert:

    public bool Insert(string UserName, SendInventoryModel sendInventoryModel)
    {
        using (DatabaseContext context = new DatabaseContext())
        {
            bool flag = false;

            // Create new
            InventoryEntity inventoryEntity = new InventoryEntity 
            {
                UserName = sendInventoryModel.UserName,
                Item = sendInventoryModel.Item ,
            };
            context.Table.Add(inventoryEntity);
            context.SaveChanges();

            // Check
            var model = CheckUserNameID(UserName, sendInventoryModel.Item);
            var data = context.Table.Find(model.Id);

            if (null != data)
            {
                flag = true;
            }

            return flag;
        }
    }

SendInventoryModel:

public class SendSiteMailModel
    {
        [Required]
        public string UserName { get; set; }

        [Required]
        public string Item{ get; set; }
    }

InventoryController:

[HttpPost("{username}")]
[Authorize]
public JObject Post([Required] string UserName, [FromBody] SendInventoryModel sendInventoryModel)
{
    ResponseModel x = new ResponseModel();

    try
    {
        InventoryRepository InventoryRepository = new InventoryRepository();
        bool isSuccess = InventoryRepository.Insert(UserName, sendInventoryModel);
    }
    catch (Exception error)
    {
        // if not successful
    }

    return Json(x);
}

I already added [DatabaseGenerated(DatabaseGeneratedOption.Identity)] in my InventoryEntity and InventoryModel.

InventoryEntity:

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

InventoryModel:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

I also added the below code in my DBContext.cs:

public virtual DbSet<OtherTableEntity> Table{ get; set; }
public virtual DbSet<InventoryEntity> Table{ get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     modelBuilder.Entity<OtherTableEntity>();
     modelBuilder.Entity<InventoryEntity>().Property(x => x.Id).ValueGeneratedOnAdd();

     base.OnModelCreating(modelBuilder);
}

partial void OnModelCreatingPartial(ModelBuilder modelBuilder);

Add finally my table design: Inventory ID:

(Is Identity) = Yes
Identity Increment = 1
Identity Seed = 1

Note that there is no Primary Key in the Inventory table. And its an old table with existing data. The current database was migrated from membership to identity.

After all the things that I have added the context.SaveChanges(); in the insert method still does not work. Any ideas or suggestion on how to fix this problem?

Note: I've changed the table entity names and models per Asherguru suggestion since its kinda confusing and generic.

https://stackoverflow.com/questions/49781548/database-operation-expected-to-affect-1-rows-but-actually-affected-0-rows — Dale K, Jul 30 '20 at 04:25
I checked the link but I already tried most of the solution there. — Jean2020, Jul 30 '20 at 05:19
Please update your question with all the things you have tried. One of the most common is having a trigger on the table... can you confirm you definitely don't have a trigger? — Dale K, Jul 30 '20 at 05:39
Now that you mention it. There is a trigger in the Table. I think this is the problem. In this case do I need to fill in all missing information needed in the trigger? — Jean2020, Jul 30 '20 at 05:47

Asherguru · Accepted Answer · 2020-07-31T09:58:39.227

0

Are your TableEntity and Table in database same table names?

Why different names - TableEntity and Table?

Try to add [Table("YourTableNameInDatabase")] in TableEntity class. Then EF can find actual table in database and insert into this table.

[Table("YourTableNameInDatabase")]
public partial class TableEntity
{
      public int Id { get; set; }
}

It would be less confusing if you show actual table names with some necessary screenshots.

edited Jul 31 '20 at 09:58

answered Jul 30 '20 at 06:01

Asherguru

1,687
1
5
10

ahh yes. Im using [Table(Variables.TABLE_Entity)]. Where TABLE_Entity in the Variables class named the same as the one in my database. – Jean2020 Jul 30 '20 at 06:12
your context use wrong table. supposed to use context.TableEntity.Add(tableEntity); – Asherguru Jul 30 '20 at 06:19
it's hard to find your issue if you replace all actual table names with "Table" name. I cannot tell which issue. It would be better for us to find issue if you use actual table names. – Asherguru Jul 30 '20 at 06:22
Im sorry. Let me edit it. Its like this in the DBContext. public virtual DbSet Table{ get; set; } – Jean2020 Jul 30 '20 at 06:32
Your OtherTableEntity also use Table. probably, context use OtherTableEntity when you trying to add TableEntity? – Asherguru Jul 30 '20 at 06:39
No , they are using the correct table. I can display the data and update them fine but when it comes to insert its not working. So somewhere in my insert functionality there is something that does not work or missing. – Jean2020 Jul 30 '20 at 07:00
I've changed the names. I realize you were right about the naming. Its kinda confusing. Thank you for the suggestion. – Jean2020 Jul 30 '20 at 07:29
Oh, didn't notice your "no Primary key" word. you need primary key then you able to insert. – Asherguru Jul 30 '20 at 09:01
Ok so let's say if the ID is primary key? Any suggestion ? – Jean2020 Jul 30 '20 at 10:55
then your insert is working already. your code for insert is correct. – Asherguru Jul 31 '20 at 09:37
if still cant work, you may use scaffold-dbcontext to re-update your dbcontext and models. do remember to backup your whole project before you ready to do scaffold-dbcontext in case when anything important in your current models or dbcontext gone as scaffold-dbcontext overwrite your dbcontext and models. – Asherguru Jul 31 '20 at 09:43

Cannot Insert new Data in the Database in .NET Core API error Database operation

1 Answers1