SQL error when selecting a column of a subclass when sibling classes have a column of the same name

Description

I have an entity class "DataRecord" which has four joined sub-classes "Incident", "Problem", "RequestForChange" and "Change". Each sub-class has property called "State" which is a many-to-one reference to four other tables - "IncidentState", "ProblemState", "RequestForChangeState" and "ChangeState" - respectively.

If I use the NHibernate LINQ provider to select one of "State" columns by explicitly providing the sub-class to look for, invalid SQL is created referencing the wrong sub-table.

My mapping is basically:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Entities" namespace="Entities.Data"> <class name="DataRecord" table="DataRecords" abstract="false" > <id name="Id" column="Id"> <generator class="hilo"> <param name="table">AppDBHiLo</param> <param name="column">NextHiValue</param> <param name="max_lo">10</param> </generator> </id> <discriminator column="Type" insert="false" type="int"/> <version name="Version" column="Version" /> <property name="Subject" column="Subject" not-null="false" /> <joined-subclass name="Entities.Data.Incident" table="Incidents"> <key column="DataRecordId"/> <many-to-one name="State" column="StateDataIncidentStateId" fetch="join" cascade="none" class="Entities.Data.DataIncidentState" not-null="false"/> </joined-subclass> <joined-subclass name="Entities.Data.Problem" table="Problems"> <key column="DataRecordId"/> <many-to-one name="State" column="StateDataProblemStateId" fetch="select" cascade="none" class="Entities.Data.DataProblemState" not-null="false"/> </joined-subclass> <joined-subclass name="Entities.Data.RequestForChange" table="RequestForChanges"> <key column="DataRecordId"/> <many-to-one name="State" column="StateDataRequestForChangeStateId" fetch="select" cascade="none" class="Entities.Data.DataRequestForChangeState" not-null="false"/> </joined-subclass> <joined-subclass name="Entities.Data.Change" table="Changes"> <key column="DataRecordId"/> <many-to-one name="State" column="StateDataChangeStateId" fetch="select" cascade="none" class="Entities.Data.DataChangeState" not-null="false"/> </joined-subclass> </class> </hibernate-mapping>

My LINQ query is:

1 2 3 4 5 var query = new NhQueryable<DataRecord>(session.GetSessionImplementation()).Select(x => new { Subject = x.Subject, State = ((Change)x).State.Description });

The NhQueryable must use the parent class here, because the query is dynamically built and may or may not include properties of the other sub-classes.

The constructed expression, i.e. query.Expression.DebugView, is:

1 2 3 4 5 6 7 8 9 10 .Call System.Linq.Queryable.Select( .Constant<NHibernate.Linq.NhQueryable`1[Entities.Data.DataRecord]>(NHibernate.Linq.NhQueryable`1[Entities.Data.DataRecord]), '(.Lambda #Lambda1<System.Func`2[Entities.Data.DataRecord,<>f__AnonymousType0`2[System.String,System.String]]>)) .Lambda #Lambda1<System.Func`2[Entities.Data.DataRecord,<>f__AnonymousType0`2[System.String,System.String]]>(Entities.Data.DataRecord $x) { .New <>f__AnonymousType0`2[System.String,System.String]( $x.Subject, (((Entities.Data.Change)$x).State).Description) }

When executing I get the following GenericADOException:

1 2 3 4 5 6 7 8 9 10 11 12 could not execute query [ select datarecord0_.[Subject] as col_0_0_, datarecord1_.[Description] as col_1_0_ from [DataRecords] datarecord0_ left outer join [Incidents] datarecord0_1_ on datarecord0_.[Id]=datarecord0_1_.[DataRecordId] left outer join [Problems] datarecord0_2_ on datarecord0_.[Id]=datarecord0_2_.[DataRecordId] left outer join [RequestForChanges] datarecord0_3_ on datarecord0_.[Id]=datarecord0_3_.[DataRecordId] left outer join [Changes] datarecord0_4_ on datarecord0_.[Id]=datarecord0_4_.[DataRecordId] left outer join [DataStates] datarecord1_ on datarecord0_1_.[StateDataChangeStateId]=datarecord1_.[Id] ]}}

with the InnerException:

Invalid column name 'StateDataChangeStateId'.

As you can see the last join to access ((Change)x).State is correctly using the foreign key column name "StateDataChangeStateId" of "Change" but incorrectly using the table for "Incident" (datarecord0_1_).

Instead of

1 datarecord0_1_.[StateDataChangeStateId]

it should be

1 datarecord0_4_.[StateDataChangeStateId]

.

Environment

SQLServer 2012

Status

Assignee

Unassigned

Reporter

Maik Schott

Labels

Components

Affects versions

4.1.1
4.0.0.GA

Priority

Minor
Configure