Databases
StackOverflow2013
Postsdbo
POUpdate entity, delete associations in many to many relationship using Fluent NHibernate
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate entity, delete associations in many to many relationship using Fluent NHibernate
    text
    copied!<p>I have two entities like this:</p> <pre><code>public class Service { public virtual int ServiceId { get; set; } public virtual char LayoutCode { get; set; } public virtual string ServiceNameEn { get; set; } public virtual string ServiceNameTh { get; set; } public virtual string ServiceDescEn { get; set; } public virtual string ServiceDescTh { get; set; } public virtual string ServicePhone { get; set; } public virtual byte[] ServiceImage { get; set; } public virtual bool Permanent { get; set; } public virtual bool Active { get; set; } public virtual bool IsAutoAssign { get; set; } public virtual DateTime CreatedDatetime { get; set; } public virtual DateTime LastUpdatedDatetime { get; set; } public virtual string RedirectUrl { get; set; } public virtual IList&lt;Reply&gt; Replies { get; set; } public virtual IList&lt;Employee&gt; Employees { get; set; } public virtual IList&lt;ServicesOfUser&gt; ServicesOfUser { get; set; } private IList&lt;Category&gt; _categories = new List&lt;Category&gt;(); public virtual IEnumerable&lt;Category&gt; Categories { get { return _categories; } } public virtual void Add(Category category) { if (!_categories.Any(x =&gt; x.CategoryId == category.CategoryId)) { _categories.Add(category); } } public virtual void Clear() { _categories.Clear(); } } </code></pre> <p>and</p> <pre><code>public class Category { public virtual int CategoryId { get; set; } public virtual string CategoryNameEn { get; set; } public virtual string CategoryNameTh { get; set; } public virtual string CategoryDescEn { get; set; } public virtual string CategoryDescTh { get; set; } public virtual byte[] CategoryImage { get; set; } public virtual bool Active { get; set; } public virtual DateTime CreatedDatetime { get; set; } public virtual DateTime LastUpdatedDatetime { get; set; } public virtual IList&lt;Service&gt; Services { get; set; } } </code></pre> <p>The relationship of these two entities is many-to-many with mappings:</p> <pre><code>public class ServiceMap : ClassMap&lt;Service&gt; { public ServiceMap() { Table("[SERVICES]"); Id(x =&gt; x.ServiceId) .GeneratedBy.Identity() .Column("service_id") .CustomType("int") .Access.Property() .CustomSqlType("int") .Not.Nullable(); Map(x =&gt; x.LayoutCode) .Column("layout_code") .CustomType("char") .Access.Property() .CustomSqlType("char") .Length(1) .Not.Nullable(); Map(x =&gt; x.ServiceNameEn) .Column("service_name_en") .Access.Property() .CustomType("string") .CustomSqlType("varchar") .Length(128) .Not.Nullable(); Map(x =&gt; x.ServiceNameTh) .Column("service_name_th") .Access.Property() .CustomType("string") .CustomSqlType("nvarchar") .Length(128) .Not.Nullable(); Map(x =&gt; x.ServiceDescEn) .Column("service_desc_en") .CustomType("string") .Access.Property() .CustomSqlType("varchar") .Length(4000) .Nullable(); Map(x =&gt; x.ServiceDescTh) .Column("service_desc_th") .CustomType("string") .Access.Property() .CustomSqlType("nvarchar") .Length(4000) .Nullable(); Map(x =&gt; x.ServicePhone) .Column("service_phone") .Access.Property() .CustomType("string") .CustomSqlType("nvarchar") .Length(50) .Nullable(); Map(x =&gt; x.ServiceImage) .Column("service_image") .CustomType("BinaryBlob") .Access.Property() .Generated.Never() .CustomSqlType("image") .Length(131231) .Nullable(); Map(x =&gt; x.Permanent) .Column("permanent") .CustomType("bool") .Access.Property() .CustomSqlType("bit") .Not.Nullable(); Map(x =&gt; x.Active) .Column("active") .CustomType("bool") .Access.Property() .CustomSqlType("bit") .Not.Nullable(); Map(x =&gt; x.IsAutoAssign) .Column("is_auto_assign") .CustomType("bool") .Access.Property() .CustomSqlType("bit") .Not.Nullable(); Map(x =&gt; x.CreatedDatetime) .Column("created_datetime") .Access.Property() .CustomType("DateTime") .CustomSqlType("datetime") .Not.Nullable(); Map(x =&gt; x.LastUpdatedDatetime) .Column("last_updated_datetime") .Access.Property() .CustomType("DateTime") .CustomSqlType("datetime") .Not.Nullable(); Map(x =&gt; x.RedirectUrl) .Column("redirect_url") .Access.Property() .CustomType("string") .CustomSqlType("nvarchar") .Length(213123) .Nullable(); HasMany&lt;ServicesOfUser&gt;(x =&gt; x.ServicesOfUser) .AsBag() .Cascade.None() .LazyLoad() .Inverse() .KeyColumns.Add("service_id", mapping =&gt; mapping.Name("service_id") .SqlType("int") .Nullable()); HasMany&lt;Reply&gt;(x =&gt; x.Replies) .AsBag() .Cascade.None() .LazyLoad() .Inverse() .KeyColumns.Add("send_to_service_id", mapping =&gt; mapping.Name("send_to_service_id") .SqlType("int") .Nullable()); HasManyToMany&lt;Employee&gt;(x =&gt; x.Employees) .AsBag() .Cascade.None() .LazyLoad() .Table("SERVICE_MONITORS") .Inverse() .ChildKeyColumns.Add("employee_id", mapping =&gt; mapping.Name("employee_id") .SqlType("uniqueidentifier") .Not.Nullable()) .ParentKeyColumns.Add("service_id", mapping =&gt; mapping.Name("service_id") .SqlType("int") .Not.Nullable()); HasManyToMany&lt;Category&gt;(x =&gt; x.Categories) .AsBag() .Access.CamelCaseField(Prefix.Underscore) .Cascade.AllDeleteOrphan() .Table("SERVICES_IN_CATEGORIES") .ChildKeyColumns.Add("category_id", mapping =&gt; mapping.Name("category_id") .SqlType("int") .Not.Nullable()) .ParentKeyColumns.Add("service_id", mapping =&gt; mapping.Name("service_id") .SqlType("int") .Not.Nullable()); } } </code></pre> <p>And</p> <pre><code>public class CategoryMap : ClassMap&lt;Category&gt; { public CategoryMap() { Table("[SERVICE_CATEGORIES]"); Id(x =&gt; x.CategoryId) .GeneratedBy.Identity() .Column("category_id") .CustomType("int") .Access.Property() .CustomSqlType("int") .Not.Nullable(); Map(x =&gt; x.CategoryNameEn) .Column("category_name_en") .CustomType("string") .Access.Property() .CustomSqlType("varchar") .Length(128) .Not.Nullable(); Map(x =&gt; x.CategoryNameTh) .Column("category_name_th") .CustomType("string") .Access.Property() .CustomSqlType("nvarchar") .Length(128) .Not.Nullable(); Map(x =&gt; x.CategoryDescEn) .Column("category_desc_en") .CustomType("string") .Access.Property() .CustomSqlType("varchar") .Length(3423423) .Not.Nullable(); Map(x =&gt; x.CategoryDescTh) .Column("category_desc_th") .CustomType("string") .Access.Property() .CustomSqlType("nvarchar") .Length(3243234) .Not.Nullable(); Map(x =&gt; x.CategoryImage) .Column("category_image") .CustomType("BinaryBlob") .Access.Property() .Generated.Never() .CustomSqlType("image") .Length(131231) .Nullable(); Map(x =&gt; x.Active) .Column("active") .CustomType("bool") .Access.Property() .CustomSqlType("bit") .Not.Nullable(); Map(x =&gt; x.CreatedDatetime) .Column("created_datetime") .Access.Property() .CustomType("DateTime") .CustomSqlType("datetime") .Not.Nullable(); Map(x =&gt; x.LastUpdatedDatetime) .Column("last_updated_datetime") .Access.Property() .CustomType("DateTime") .CustomSqlType("datetime") .Not.Nullable(); HasManyToMany&lt;Service&gt;(x =&gt; x.Services) .AsBag() .Cascade.AllDeleteOrphan() .Inverse() .LazyLoad() .Table("SERVICES_IN_CATEGORIES") .ChildKeyColumns.Add("service_id", mapping =&gt; mapping.Name("service_id") .SqlType("int") .Not.Nullable()) .ParentKeyColumns.Add("category_id", mapping =&gt; mapping.Name("category_id") .SqlType("int") .Not.Nullable()); } } </code></pre> <p>Coding:</p> <pre><code>using (unitOfWork = new UnitOfWork(SessionFactory)) { serviceRepo = new ServiceRepo(unitOfWork.Session); try { Service service = new Service(); if (id != null) service = serviceRepo.GetById((int)id); // set values for service here IList&lt;Category&gt; categories = new List&lt;Category&gt;(); // add some categories to service serviceRepo.Save(service); unitOfWork.Commit(); return service; } catch (Exception ex) { unitOfWork.Rollback(); return null; } } </code></pre> <p>Service is updated, but the association records in join table aren't deleted.</p> <p>If I add this code below before adding categories to service:</p> <pre><code>service.Clear(); </code></pre> <p>When commit, I get this error message: </p> <pre><code>not-null property references a null or transient value ilu.src.Entities.Category.CategoryDescEn </code></pre> <p>Full stack trace:</p> <pre><code> at NHibernate.Engine.Nullability.CheckNullability(Object[] values, IEntityPersister persister, Boolean isUpdate) at NHibernate.Event.Default.DefaultDeleteEventListener.DeleteEntity(IEventSource session, Object entity, EntityEntry entityEntry, Boolean isCascadeDeleteEnabled, IEntityPersister persister, ISet transientEntities) at NHibernate.Event.Default.DefaultDeleteEventListener.OnDelete(DeleteEvent event, ISet transientEntities) at NHibernate.Impl.SessionImpl.FireDelete(DeleteEvent event, ISet transientEntities) at NHibernate.Impl.SessionImpl.Delete(String entityName, Object child, Boolean isCascadeDeleteEnabled, ISet transientEntities) at NHibernate.Engine.CascadingAction.DeleteCascadingAction.Cascade(IEventSource session, Object child, String entityName, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeToOne(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollectionElements(Object parent, Object child, CollectionType collectionType, CascadeStyle style, IType elemType, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollection(Object parent, Object child, CascadeStyle style, Object anything, CollectionType type) at NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything) at NHibernate.Event.Default.DefaultDeleteEventListener.CascadeBeforeDelete(IEventSource session, IEntityPersister persister, Object entity, EntityEntry entityEntry, ISet transientEntities) at NHibernate.Event.Default.DefaultDeleteEventListener.DeleteEntity(IEventSource session, Object entity, EntityEntry entityEntry, Boolean isCascadeDeleteEnabled, IEntityPersister persister, ISet transientEntities) at NHibernate.Event.Default.DefaultDeleteEventListener.OnDelete(DeleteEvent event, ISet transientEntities) at NHibernate.Impl.SessionImpl.FireDelete(DeleteEvent event, ISet transientEntities) at NHibernate.Impl.SessionImpl.Delete(String entityName, Object child, Boolean isCascadeDeleteEnabled, ISet transientEntities) at NHibernate.Engine.CascadingAction.DeleteCascadingAction.Cascade(IEventSource session, Object child, String entityName, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeToOne(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollectionElements(Object parent, Object child, CollectionType collectionType, CascadeStyle style, IType elemType, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollection(Object parent, Object child, CascadeStyle style, Object anything, CollectionType type) at NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything) at NHibernate.Event.Default.DefaultDeleteEventListener.CascadeBeforeDelete(IEventSource session, IEntityPersister persister, Object entity, EntityEntry entityEntry, ISet transientEntities) at NHibernate.Event.Default.DefaultDeleteEventListener.DeleteEntity(IEventSource session, Object entity, EntityEntry entityEntry, Boolean isCascadeDeleteEnabled, IEntityPersister persister, ISet transientEntities) at NHibernate.Event.Default.DefaultDeleteEventListener.OnDelete(DeleteEvent event, ISet transientEntities) at NHibernate.Impl.SessionImpl.FireDelete(DeleteEvent event, ISet transientEntities) at NHibernate.Impl.SessionImpl.Delete(String entityName, Object child, Boolean isCascadeDeleteEnabled, ISet transientEntities) at NHibernate.Engine.Cascade.DeleteOrphans(String entityName, IPersistentCollection pc) at NHibernate.Engine.Cascade.CascadeCollectionElements(Object parent, Object child, CollectionType collectionType, CascadeStyle style, IType elemType, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeCollection(Object parent, Object child, CascadeStyle style, Object anything, CollectionType type) at NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled) at NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything) at NHibernate.Event.Default.AbstractFlushingEventListener.CascadeOnFlush(IEventSource session, IEntityPersister persister, Object key, Object anything) at NHibernate.Event.Default.AbstractFlushingEventListener.PrepareEntityFlushes(IEventSource session) at NHibernate.Event.Default.AbstractFlushingEventListener.FlushEverythingToExecutions(FlushEvent event) at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event) at NHibernate.Impl.SessionImpl.Flush() at NHibernate.Transaction.AdoTransaction.Commit() at ilu.src.Common.UnitOfWork.Commit() in D:\Arunsawad\iLertU\ilu\ilu\src\Common\UnitOfWork.cs:line 41 at ilu.Controllers.AdminController.SaveService(Nullable`1 id, String name, String nameth, String desc, String descth, String phone, String catids, String url, Char layoutcode, Boolean permanent, Boolean active, Boolean autoassign) in D:\Arunsawad\iLertU\ilu\ilu\Controllers\AdminController.cs:line 107 </code></pre> <p>Is there anything I'm missing here?</p> <p>Thank you!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload