LINQ-to-NH (3.2.0) and one-to-one generates wrong SQL?

[mysterd429]

Hi all.

I'm trying to execute a query using LINQ-to-NH, and the SQL that gets generated seems wrong to me.  The following statement:

nh.Query<ManyToOneChild>().Where(a => a.Parent.OneToOneChild != null).ToList()

generates the following SQL (which I formatted and removed the aliases from):

select
    MANY_TO_ONE_CHILD.MANY_TO_ONE_CHILD_ID
  , MANY_TO_ONE_CHILD.PROPERTY_A
  , MANY_TO_ONE_CHILD.PARENT_ID
  , MANY_TO_ONE_CHILD.PROPERTY_C_GRANTPARENT_ID
from
    MANY_TO_ONE_CHILD
inner join
    PARENT
on
    MANY_TO_ONE_CHILD.PARENT_ID = parent.PARENT_ID
where
    PARENT.PARENT_ID is not null

My mapping file has the following:

<class name="OneToOneChild" table="ONE_TO_ONE_CHILD" lazy="true" >
    <many-to-one name="Parent" class="Parent" unique="true">
        <column name="PARENT_ID" />
    </many-to-one>
</class>
<class name="Parent" table="PARENT" lazy="true" >
    <one-to-one name="OneToOneChild" class="OneToOneChild" property-ref="Parent" />
</class>

I would have expected a sub-query or a join to check the ONE_TO_ONE_CHILD.

Any ideas?

Thanks!

[mysterd429]

All,

This happens even if I greatly simplify: nh.Query<Parent>().Where(p => p.OnetoOneChild == null).ToList() also generated the check for PARENT_ID IS NULL.

Thanks.

[Oskar Berggren]

Sounds similar to this one:
https://nhibernate.jira.com/browse/NH-3117

A way to help with this is to write a test case suitable for the
NHibernate code base, possibly based on the code found in the
attachment to issue NH-3117.
See this
http://nhforge.org/blogs/nhibernate/archive/2008/10/04/the-best-way-to-solve-nhibernate-bugs-submit-good-unit-test.aspx

Or fork NHibernate on github and submit the test case directly as a
pull request.

/Oskar


2013/1/7 mysterd429 <don.lave...@gmail.com>:

> --
> 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/-/cfTFjqNYfrkJ.
>
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to
> nhusers+u...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.

[Don]

Thanks, Oskar.  I'll try to get that done when I have a chance.  I altered the code locally based on the issue attachment to include my scenario, and they do appear to be the same.

[Don]

Oh, and as a work-around, I was able to use

    session.Query<Person>().Where(o => o.Employee.EmployeeId != null)

However, in my application, I use a data access interface with an NHibernate implementation for integration testing and production and a LINQ-to-Objects implementation for unit testing, so that doesn't work.  I ended up selected all of the PersonId into a list from an IQueryable<Employee> and checking to see if that list did (or didn't) contain the person ID, something like:

    var personWithEmployeePersonIds = wrapper.Query<Employee>().Select(e => e.Person.PersonId).ToList();
    var people wrapper.Query<Person>().Where(o => !personWithEmployeePersonIds.Contains(o.personId)).ToList();

[Don]

I'm trying to run the unit tests, and I keep getting the following for each test class:

NHibernate.Test.Linq.AggregateTests:
Parent SetUp failed in LinqReadonlyTestsContext

I have created a new database, nhibernate, with changing the ./SqlExpress to localhost in nhibernate.connection.connection_string in build-common\nhibernate-properties.xml and connection_string in src\NHibernate.Test\app.config.

Can you help me get the tests running so I can add the ones I need?

Thanks!

[Oskar Berggren]

2013/1/9 Don <don.lave...@gmail.com>:

> I'm trying to run the unit tests, and I keep getting the following for each
> test class:
>
> NHibernate.Test.Linq.AggregateTests:
> Parent SetUp failed in LinqReadonlyTestsContext

Try to get more error information from this.

>
> I have created a new database, nhibernate, with changing the ./SqlExpress to
> localhost in nhibernate.connection.connection_string in
> build-common\nhibernate-properties.xml and connection_string in
> src\NHibernate.Test\app.config.

Revert those changes.

Then create a file src\NHibernate.test\hibernate.cfg.xml with contents
similar to this:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.Test">
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">
Server=(local);initial catalog=nhibernate;Integrated Security=SSPI
</property>
<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
</session-factory>
</hibernate-configuration>

This should take effect when tests are run either using a visual
studio plugin or by garelease/showbuildmenu (the latter of which can
also generate AssemblyInfo.cs for VS if you hadn't discovered that).
Since this doesn't change a file tracked by git, it won't pester you
about uncommitted changes.

/Oskar

[Don]

Oskar,

I set the solution up for VS using showbuildmenu option A, built the solution using showbuildmenu option E, added the file you indicated, opened the project in VS, opened the project in the NUnit GUI, attached the VS debugger to NUnit, and I got the following exception:

{"Could not find the dialect in the configuration"} {System.InvalidOperationException}
   at NHibernate.Dialect.Dialect.GetDialect(IDictionary`2 props) in C:\path\to\\nhibernate-core-3.2.0GA\src\NHibernate\Dialect\Dialect.cs:line 175

Thanks,

    Don

[Oskar Berggren]

The visual studio build and the build options from the build menu
don't generate the exact same output in the exact same places. I
suspect you need to build from within VS to make the nunit gui work. I
haven't tried myself, as I use a VS extension to run the test.

/Oskar


2013/1/10 Don <don.lave...@gmail.com>:

> --
> 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/-/ZEfc5m3fAPkJ.