MonoRail ActiveRecord - The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE

Question

I'm new to MonoRail and ActiveRecord and have inherited an application that I need to modify. I have a Category table (Id, Name) and I'm adding a ParentId FK which references the Id in the same table so that you can have parent/child category relationships.

I tried adding this to my Category model class:

[Property] 
public int ParentId { get; set; }

I also tried doing it this way:

[BelongsTo("ParentId")] 
public Category Parent { get; set; }

When I call a method to get all parent categories (they have a null ParentId), it works fine:

public static Category[] GetParentCategories() 
        { 
            var criteria = DetachedCriteria.For<Core.Models.Category>(); 
            return (FindAllByProperty("ParentId", null)); 
        }

However, when I call a method to get all child categories within a specific category, it errors out:

public static Category[] GetChildCategories(int parentId) 
        { 
            var criteria = DetachedCriteria.For<Core.Models.Category>(); 
            return (FindAllByProperty("ParentId", parentId)); 
        }

The error is:

"The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint \"FK_Category_ParentId\". The conflict occurred in database \"UCampus\", table \"dbo.Category\", column 'Id'.\r\nThe statement has been terminated."

I'm hard-coding in the parentId parameter as 1 and I'm 100% sure it exists as an id in the Category table so I don't know why it'd give this error. Also, I'm doing a select, not an update, so what is going on here??

Thanks for any input on this,

Justin

Answer 1

You only need this one:

[BelongsTo("ParentId")] 
public Category Parent { get; set; }

Remove this property:

[Property] 
public int ParentId { get; set; }

When you use FindAllByProperty() (or any other NHibernate/ActiveRecord criteria), the property name you use is the mapped property, not the table column, so the correct usage is FindAllByProperty("Parent.Id", parentId), not FindAllByProperty("ParentId", parentId). (assuming Category has a PK property Id)

If you see an UPDATE when you're doing a query it usually means that you're using automatic session flushing and one or more of your session entities is dirty, so NHibernate has to flush the session and UPDATE these dirty entities before querying to make sure you don't get stale results.

Minor nitpick: remove the DetachedCriteria, it's not being used there.