2

I have two tables, Table 1 has a identity column(AttachmentID) and other fields, Table 2 has a AttachmentID column which is FK of Table1.AttachmentID and PK(not identity column), so Table1.AttachmentID = Table2.AttachmentID, records may or may not present in Table2 for all the records of Table1.

When I am saving records using Nhibernate entity classes, Records in table 1 and Table2 are being inserted correctly with the correct relationship but it also executing SCOPE_IDENTITY() after insert in table 2, since table 2 doesn't have any identity column so SCOPE_IDENTITY is giving null and because of this complete operation is being failed.

Following insert queries are being executed

exec sp_executesql N'INSERT INTO Table1 (FileName, GroupID, RelativeStorageLocation, FileSize, FileStatus, StorageTypeID, DocumentId, AccessKey, AttachmentSource, FileThumbnailID) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9); select SCOPE_IDENTITY()',N'@p0 nvarchar(4000),@p1 int,@p2 nvarchar(4000),@p3 bigint,@p4 int,@p5 int,@p6 int,@p7 nvarchar(4000),@p8 int,@p9 int',@p0=N'IMG_20180108_145645.jpg',@p1=52122,@p2=NULL,@p3=0,@p4=3,@p5=0,@p6=0,@p7=NULL,@p8=5,@p9=NULL    
exec sp_executesql N'INSERT INTO Table2 (DateTaken, ImageSource, ImageLocation, AttachmentID) VALUES (@p0, @p1, @p2, @p3); select SCOPE_IDENTITY()',N'@p0 datetime,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 int',@p0='2018-01-08 14:56:45',@p1=N'Xiaomi Redmi Note 4',@p2=N'Murzuq District, Libya',@p3=13045

Following are the Nhibernate hbm files

Table 1(GenericAttachment)

<id name="_id" column="AttachmentID" access="field">
  <generator class="identity"/>
</id>

<property name="FileName" update="false"/>
<property name="GroupID"/>
<many-to-one name="GenericAttachmentGroup" class="GenericAttachmentGroup" column="GroupID" fetch="select" cascade="none" not-null="false" update="false" insert="false"/>
<property name="RelativeStorageLocation"/>
<property name="FileSize"/>
<property name="FileStatus" />
<property name="StorageTypeID" column="StorageTypeID" />
<property name="DocumentId" not-null="false"/>
<property name="AccessKey" not-null="false"/>
<property name="AttachmentSource" column="AttachmentSource" />
<many-to-one name="Thumbnail" class="FileInformation" column="FileThumbnailID" fetch="select" cascade="save-update" not-null="false" not-found="ignore"/>
<one-to-one lazy="proxy" name="Metadata" class="GenericAttachmentMatadataDetails" fetch="select" cascade="save-update" outer-join="true"  property-ref="GenericAttachment" />

Table 2(GenericAttachmentMatadataDetails)

<id name="_id" column="AttachmentID" access="field">
  <generator class="native"/>
</id>
<property name="DateTaken" column="DateTaken" />
<property name="ImageSource" column="ImageSource" />
<property name="ImageLocation" column="ImageLocation" />
<many-to-one name="GenericAttachment" class="Guru.Business.Entities.GenericAttachment, Guru.Business"  column="AttachmentID"  fetch="select" lazy="proxy" />

I am using C# with SQL server 2012.

Thanks

gorkem
  • 731
  • 1
  • 10
  • 17
Sumit Bansal
  • 63
  • 10

2 Answers2

1

You mapped Table 2's id as native. This will use the default implementation for the underlying database. For SQL server this is identity.

native

picks identity, sequence or hilo depending upon the capabilities of the underlying database.

You want to change the mapping to assigned and set the id yourself.

If it's truly a one-one mapping, then change your mapping to reflect that.

Also the mapping as you've written it does not make much sense. You can't have a child table have a duplicate PK which you would be doing by adding the AttachmentID as the PK for Table 2.

Fran
  • 6,440
  • 1
  • 23
  • 35
0

Generator class "foreign" solved the problem, Updated mapping configuration

Table 1(GenericAttachment)

<id name="_id" column="AttachmentID" access="field">
  <generator class="identity"/>
</id>
<property name="FileName" update="false"/>
<property name="GroupID"/>
<many-to-one name="GenericAttachmentGroup" class="GenericAttachmentGroup" column="GroupID" fetch="select" cascade="none" not-null="false" update="false" insert="false"/>
<property name="RelativeStorageLocation"/>
<property name="FileSize"/>
<property name="FileStatus" />
<property name="StorageTypeID" column="StorageTypeID" />
<property name="DocumentId" not-null="false"/>
<property name="AccessKey" not-null="false"/>
<property name="AttachmentSource" column="AttachmentSource" />
<many-to-one name="Thumbnail" class="FileInformation" column="FileThumbnailID" fetch="select" cascade="save-update" not-null="false" not-found="ignore"/>
<one-to-one lazy="proxy" name="Metadata" class="GenericAttachmentMetadataDetails" fetch="select" cascade="save-update" outer-join="true" property-ref="GenericAttachment"/>

Table 2(GenericAttachmentMatadataDetails)

<id name="_id" column="AttachmentID" access="field">
  <generator class="foreign">
        <param name="property">GenericAttachment</param>
   </generator>
</id>
<property name="DateTaken" column="DateTaken" />
<property name="ImageSource" column="ImageSource" />
<property name="ImageLocation" column="ImageLocation" />
<many-to-one name="GenericAttachment" access="property" class="GenericAttachment" fetch="select" insert="false" update="false" cascade="none" column="AttachmentID" unique="true" />
Sumit Bansal
  • 63
  • 10