Hi all,
I'm using the LINQ-to-NH provider that comes with 3.2.0.4000 to access a
SQL Server database with missing foreign keys that are part of composite
primary keys. If I query to check to see if a parent is not null, the SQL
query that gets generated
I have not-found="ignore" set:
<?xml version="1.0" encoding="utf-8"?>
> <hibernate-mapping assembly="DomainObjects" namespace="DomainObjects"
> xmlns="urn:nhibernate-mapping-2.2">
> <class name="Child" table="CHILD" lazy="true" >
> <composite-id>
> <key-property name="ParentOneId" column="PARENT_ONE_ID" />
> <key-property name="ParentTwoId" column="PARENT_TWO_ID" />
> <key-property name="ParentThreeId" column="PARENT_THREE_ID" />
> </composite-id>
> <many-to-one not-found="ignore" insert="false" update="false"
> name="ParentOne" class="ParentOne">
> <column name="PARENT_ONE_ID" not-null="true" />
> </many-to-one>
> <many-to-one not-found="ignore" insert="false" update="false"
> name="ParentTwo" class="ParentTwo">
> <column name="PARENT_TWO_ID" not-null="true" />
> </many-to-one>
> <many-to-one not-found="ignore" insert="false" update="false"
> name="ParentThree" class="ParentThree">
> <column name="PARENT_THREE_ID" not-null="true" />
> </many-to-one>
> <property name="AnotherProperty">
> <column name="ANOTHER_PROPERTY" />
> </property>
> <property name="YetAnotherProperty">
> <column name="YET_ANOTHER_PROPERTY" not-null="true" />
> </property>
> </class>
> </hibernate-mapping>
>
I run my query using the following:
IQueryable<Child> resultQuery = m_hibernateSession.Query<Child>();
> resultQuery = resultQuery.Where(a => a.ParentOne != null)
> recordCount = data.Count();
> resultQuery = resultQuery.Skip(recordsPerPage * (pageNumber -
> 1)).Take(recordsPerPage);
>
> IList<Child> withParentOne = resultQuery.ToList();
> Assert.AreNotEqual(0, withParentOne.Count);
> Assert.IsNotNull(withParentOne[0].ParentOne); // Fails
>
That's the code that gets executed, though it actual code spans a few
functions; I have a class that does pagination on an IQueryable<Entity>.
The data I have looks kind of like this; the rows with an asterisk indicate
that no parent exists with that ID:
+-------------------------------------------------+
> | CHILD |
> +---------------+---------- ----+-----------------+
> | PARENT_ONE_ID | PARENT_TWO_ID | PARENT_THREE_ID |
> +---------------+---------------+-----------------+
> | 0* | 12345 | 123 |
> | 0* | 12345 | 124 |
> | 101 | 12346 | 125 |
> | 102 | 12346 | 129* |
> | 103 | 12347* | 130 |
> +---------------+---------------+-----------------+
>
The problem is that the query that gets run includes where
child0_.PARENT_ONE_ID IS NOT NULL rather than joining out to the parent
table and checking that way.
Is there a way to tell NHibernate to join out to the parent table when
checking to see if a record is missing? If not, I can create views that do
the joins and return nulls instead of the bad primary keys, but I don't
know what NHibernate would do when it gets a NULL as part of a composite
primary key. Maybe I could create separate columns in the view to use for
the primary and foreign keys. Or is there another way to do it?
Thanks!
Don
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/_bW-JqGJgBsJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
