How to resolve batch update returned unexpected row count error in NHibernate?

Question

I have getting following error while inserting the new records in database using NHibernate.

{"Batch update returned unexpected row count from update; actual row count: 0; expected: 1"}

I have two tables with primary and foreign relationship. I want to inset the records in the both table.: here is the mapping classes

DemoStudentMap.cs

 public DemoStudentMap() {
            Table("DEMO_Student");
            Id(t => t.StudentId).Column("StudentId").GeneratedBy.Identity();
            Map(t => t.Name, "Name");
            Map(t => t.Class, "Class");
            Map(t => t.Board, "Board");
            Map(t => t.Enabled, "Enabled");
            Map(t => t.Isdeleted).Column("IsDeleted");
            Map(t => t.Createddate).Column("CreatedDate");
            Map(t => t.Lastmodifyby).Column("LastModifyBy").Nullable();
            Map(t => t.Lastmodifieddate).Column("LastModifiedDate").Nullable();
            References(x => x.DemoScore).ForeignKey("RollNumber");
          }

DemoScoreMap.cs

public DemoScoreMap() {
            Table("DEMO_Score");
            Id(t => t.rollnumber).Column("RollNumber");
            Map(t => t.math, "Math");
            Map(t => t.physics, "Physics");
            Map(t => t.english, "English");
            Map(t => t.enabled, "Enabled");
            Map(t => t.isdeleted).Column("IsDeleted");
            Map(t => t.createddate).Column("CreatedDate");
            Map(t => t.lastmodifyby).Column("LastModifyBy").Nullable();
            Map(t => t.lastmodifieddate).Column("LastModifiedDate").Nullable();
        }

I am using Asp.net WebAPI. In the Api controller's Post method i retrieved the values which i want to insert. Here is my ApiController:

DemoScoreViewModel newScore = new DemoScoreViewModel();
DemoScore score = newScore.ConvertDemoScoreViewModelToDemoS(newStudent, _crudStatusCreate);
bool resultScore = _demoScoreTask.Create(score);
DemoStudent student = newStudent.ConvertDemoStudentViewModelToDemoStudent(newStudent, score, _crudStatusCreate);
bool result = _demoStudentTask.Create(student);

Here I got the values in "score" and "student" variables which i want to save in the database. I have the following methods for creating new records which returns bool result as shown in code.

But on the time of saving the data i got the above mentioned error. Here is the code i inserting. I got same error for both score and student. Here is my code for create:

For Student:

 public bool Create(DemoStudent newStudent)
        {
            try
            {
                _demoStudentRepo.DbContext.BeginTransaction();
                _demoStudentRepo.SaveOrUpdate(newStudent);
                _demoStudentRepo.DbContext.CommitTransaction();
            }
            catch
            {
                return false;
            }
            return true;
        }

Fore Score

public bool Create(DemoScore newScore)
        {
            try
            {
                _demoScoreRepo.DbContext.BeginTransaction();
                _demoScoreRepo.SaveOrUpdate(newScore);
                _demoScoreRepo.DbContext.CommitTransaction();
            }
            catch
            {
                return false;
            }
            return true;
        }

Note: when i remove the transaction i does not got this error but still my data is not saved.

Solution 1

Finally got the problem. The error is in Mapping.

public DemoScoreMap() {
            Table("DEMO_Score");
            Id(t=>t.rollnumber).Column("RollNumber").GeneratedBy.Assigned();
            Map(t => t.math, "Math");
            Map(t => t.physics, "Physics");
            Map(t => t.english, "English");
            Map(t => t.enabled, "Enabled");
            Map(t => t.isdeleted).Column("IsDeleted");
            Map(t => t.createddate).Column("CreatedDate");
            Map(t => t.lastmodifyby).Column("LastModifyBy").Nullable();
            Map(t => t.lastmodifieddate).Column("LastModifiedDate").Nullable();
        }

Solution 2

The problem here is hidden in a fact, that the SaveOrUpdate() is called. NHibernate, for some reasons (discussed later) decided to call "UPDATE". But because we are creating new instances, the updated count of rows... is 0. While expecting the 1

What could be the reason? That the DemoScore or the DemosStudent do have the incnosistency in the default id value, and the UnsavedValue setting.

I.e. NHibernate expects that Id == 0 means, new... while any other value (even negative) would be treated as existing entity... which should be updated.

So check what value is assigned to the ID inside of the _demoScoreTask.Create(score);

The default setting (that 0 means new) could be adjusted in the mapping, e.g. .UnsavedValue(-1)

NOTE: the reason, why the version without transaction is not throwing the exception is, that the Flush() is not called. Please check 9.6. Flush. We could change the: sess.FlushMode = FlushMode.Commit; to Auto for example, but the Commit is the appropriate.

Solution 3

In my case it was the identity key. It was missing the .GeneratedBy logic.

 Table("JobDetailsBlob");
 Id(x => x.Id, "JobDetailId");

changed to

 Table("JobDetailsBlob");
 Id(x => x.Id, "JobDetailId").GeneratedBy.Assigned();

because it obviously cannot create the identity column correctly. So it was a mapping problem. hope it can helps.

Comments

[Bhupendra Shukla]

I debugged the Create function and i found that the Id is 0. Which according to me is correct. Then what it gives me error. ((SharpArch.Domain.DomainModel.EntityWithTypedId<int>)(newSc‌​ore)).Id is 0.

[Radim Köhler]

Try to explicitly set The UnsavedValue(0). It seems that it is set to something else (-1). I am almost sure, because the SaveOrUpdate does Update... (I would say), and that must mean that the 0 is not indicated as the unsaved-value. *(Note: The only other option could be that the Insert is returning 0 rows... instead of 1, but that seems to me too complicated, e.g. some trigger or setting... so most likly the above is true)

[Bhupendra Shukla]

where i have to write UnsavedValue(0).

[Radim Köhler]

It is the mapping. But do you know what would be very helpful. Try to change the SaveOrUpdate in your code to Save(). This way we will instruct NHiberante to explicitly call the INSERT, not UPDATE. And we will see if this will work... if you know what I mean. Also, check this mapping stackoverflow.com/a/1940513/1679310

[Radim Köhler]

Honestly, here I am surprised. Usually if the Identity should be Assigned, NHibernate will issue INSERT without the Id (RollNumber column) and we should get the exception: try ti insert null into not-null column. IE not batch returned... And also, please, keep in mind, that once you are using the Assigned you should not use SaveOrUpdate. NHibernate in this scenario can hardly identicate the assinged vs not-assigned value... as described below ;) good luck with NHibernate anyhow ..