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" <[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.
