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 <[email protected]> wrote:
> Seems similar to this bughttp://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"
>
>
>
>
>
>
>
> <[email protected]> wrote:
> > 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.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
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.
