NH Linq Query Problems over several joins

[Tim Hoolihan - @thoolihan]

About the project: .Net 4, NH 3.0.0.4000, Castle, NH 3 Linq
(Remotion.Data.Linq), SQL Server 2008

I have the following related entities...

Violation
Property Property

Property
ICollection<PropertyAddress>

PropertyAddress
Property Property
Address Address
bool IsPrimary

Address
string Address1
string City
string State

Some relevant mapping snippets...

<class name="Violation" table="Violation">
... id, version and other fields ...
<many-to-one name="Property" class="Property"
column="PropertyId" cascade="save-update" />
</class>

<class name="Property" table="Property">
... id, version and other fields ...
<set name="Addresses" cascade="save-update">
<cache usage="read-write" />
<key column="PropertyId" />
<one-to-many class="PropertyAddress" />
</set>
</class>

<class name="PropertyAddress" table="PropertyAddress">
<cache usage="read-write" region="PropertyAddress" />
<id name="Id" column="PropertyAddressId" type="long">
<generator class="hilo" />
</id>
<version name="Version" />

<property name="IsPrimary" />
<many-to-one name="Address" class="Address" column="AddressId"
cascade="save-update" unique="true" />
<many-to-one name="Property" class="Property"
column="PropertyId" cascade="save-update" unique="true" />
</class>

<class name="Address" table="Address">
... id, version and other fields ...

<property name="Address1">
<column name="Address1" length="50"
index="IDX_Address_Address1" />
</property>
<property name="City">
<column name="City" length="50" index="IDX_Address_City" /
>
</property>
<property name="State">
<column name="State" length="2" index="IDX_Address_State" /
>
</property>
</class>

I'm trying to get a list of violations sorted by the primary addresses
state in a linq query (because this is one of many similar queries
depending on user sorting and filtering, so it's easier to add pieces
of the filtering off of sorting and filtering using IQueryable). I can
achieve this in sql with:

SELECT distinct v.ViolationId, v.Description, a.Address1, a.City,
a.State
FROM Violation v
inner join Property p on v.PropertyId = p.PropertyId
inner join PropertyAddress pa on pa.PropertyId = p.PropertyId
inner join Address a on a.AddressId = pa.AddressId
where pa.IsPrimary = 1
order by a.State

In Linq, I've tried several different approaches, I put the error in a
comment above each...


//{"Exception of type 'Antlr.Runtime.NoViableAltException' was thrown.
[.OrderBy(NHibernate.Linq.NhQueryable`1[Kryptos.Model.Violation],
Quote((v, ) => //(.Select(.Where(v.Property.Addresses, (a, ) =>
(a.IsPrimary), ), (pa, ) => (pa.Address.State), ))), )]"}

using (var tx = session.BeginTransaction())
{
session.Query<Violation>()
.OrderBy(v => v.Property.Addresses.Where(a =>
a.IsPrimary).Select(pa => pa.Address.State))
.ForEach(ShowViolation);
tx.Commit();
}

//{"Exception of type 'Antlr.Runtime.NoViableAltException' was thrown.
[.OrderBy(NHibernate.Linq.NhQueryable`1[Kryptos.Model.Violation],
Quote((v, ) => //(.FirstOrDefault(v.Property.Addresses, (pa, ) =>
(pa.IsPrimary), ).Address.State)), )]"}

using (var tx = session.BeginTransaction())
{
session.Query<Violation>()
.OrderBy(v =>
v.Property.Addresses.FirstOrDefault(pa => pa.IsPrimary).Address.State)
.ForEach(ShowViolation);
tx.Commit();
}

//{"Could not parse expression
'[-1].Property.Addresses.AsQueryable()': This overload of the method
'System.Linq.Queryable.AsQueryable' is currently not supported, but
you can register //your own parser if needed."}

using (var tx = session.BeginTransaction())
{
session.Query<Violation>()
.OrderBy(v =>
v.Property.Addresses.AsQueryable().FirstOrDefault(pa =>
pa.IsPrimary).Address.State)
.ForEach(ShowViolation);
tx.Commit();
}

I'll gladly post more code or info if I missed something relevant. If
anyone can shed some light on how to do this in linq it would be
greatly appreciated. If it's not possible in linq, are there any
recommended methods that are still friendly to dynamically building up
the sorting and filtering piece incrementally.By that, I mean in a
presenter like layer, we take an IQueryable source and apply a where
clause based on one view property, and then apply a sort based on
another view property, so this would easily map to something like a
single sproc with parameters.

[José F. Romaniello]

It is fixed on trunk version, i send a patch several months ago and
was aplied few weeks ago

2011/2/4, Tim Hoolihan - @thoolihan <tim.ho...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> 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.
>
>

--
Enviado desde mi dispositivo móvil

[Tim Hoolihan - @thoolihan]

I checked out trunk (svn co https://nhibernate.svn.sourceforge.net/svnroot/nhibernate/trunk
nhibernate)
built (ShowBuildMenut.bat option F)
copied the binaries to my lib folder, rebuilt and made sure it
referenced the new assemblies by checking version number.

All 3 errors are exactly the same. Which one were you expecting to
work? Is there something else I'm missing?

On Feb 4, 2:46 pm, José F. Romaniello <jfromanie...@gmail.com> wrote:
> It is fixed on trunk version, i send a patch several months ago and
> was aplied few weeks ago
>

> 2011/2/4, Tim Hoolihan - @thoolihan <tim.hooli...@gmail.com>:

[José F. Romaniello]

I thought it was this

http://216.121.112.228/browse/NH-2203

Try to create a test fixture and i will have a look.

2011/2/4, Tim Hoolihan - @thoolihan <tim.ho...@gmail.com>:

[Nicola Tuveri]

Seems similar to this bug http://216.121.112.228/browse/NH-2516.
Please try to rewrite the query like this:

from v in session.Query<Violation>()
from a in session.Query<PropertyAddress>()
where v.Property == a.Property && a.IsPrimary == true
ordery by a.State


On Feb 4, 8:29 pm, "Tim Hoolihan - @thoolihan"

[Tim Hoolihan - @thoolihan]

Nicola, Thanks so much, that was worked! The query ended up being:

(from v in session.Query<Violation>()
from pa in session.Query<PropertyAddress>()
where v.Property == pa.Property &&
pa.IsPrimary == true
orderby pa.Address.State
select v).ForEach(ShowViolation);

I did know you could query with two sources like that in Linq. Again,
thanks so much for your help.

On Feb 5, 8:56 am, Nicola Tuveri <ntuv...@gmail.com> wrote:
> Seems similar to this bughttp://216.121.112.228/browse/NH-2516.