How use of select for associations in linq to NHibernate

Question

I have a application by NHibernate Implementation. My project has a Person Class :

public class Person : RootEntityBase
{    
    virtual public string FirstName { get; set; }
    virtual public string LastName { get; set; }
    virtual public string FatherName { get; set; }

    virtual public IList<Asset> Assets { get; set; }
    virtual public IList<PersonPicture> PersonPictures { get; set; }
}

By this mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Domain" namespace="Domain.Entities">
  <class name="Person" table="Person_Person" >

    <id name="Id">
      <generator class="native" />
    </id>

    <property name="FirstName" not-null="true"/>

    <property name="LastName" not-null="true"/>

    <property name="FatherName" not-null="true"/>

    <bag name="Assets" inverse="true" table="Person_Asset" cascade="all-delete-orphan" >
      <key column="Person_id_fk"/>
      <one-to-many class="Asset"/>
    </bag>

    <bag name="PersonPictures" inverse="true" table="Person_PersonPicture" cascade="all-delete-orphan" >
      <key column="Person_id_fk"/>
      <one-to-many class="PersonPicture"/>
    </bag>

  </class>

I need a query by linq where it has a select for some properties of Person.

var q = SessionInstance.Query<Person>()
               .Select(x => new Person(x.Id)
                                  {
                                      FirstName = x.FirstName,
                                      LastName = x.LastName,
                                      PersonPictures = x.PersonPictures //this line has error
                                  })
               .ToList();

By add PersonPictures to select, this query has a runtime exception by this message :

could not execute query\r\n[ select person0_.Id as col_0_0_, person0_.FirstName as col_1_0_, person0_.LastName as col_2_0_, ...

Message of inner exception is :

Incorrect syntax near the keyword 'as'.

Stack Trace is :

   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
   at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)
   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters)
   at NHibernate.Impl.ExpressionQueryImpl.List()
   at NHibernate.Linq.NhQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery)
   at NHibernate.Linq.NhQueryProvider.Execute(Expression expression)
   at NHibernate.Linq.NhQueryProvider.Execute[TResult](Expression expression)
   at Remotion.Data.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

How can I do this?

score 0 · Answer 1 · edited May 23 '17 at 12:12

I'm no Linq To nHibernate ninja, but what about the following?

var q = SessionInstance.Query<Person>().Fetch(p => p.PersonPictures).ToList();

Do you need to do that projection? I have omitted it from my answer, though it could be added in if needed.

References:

Linq for NHibernate and fetch mode of eager loading

Fetch vs FetchMany in NHibernate Linq provider

http://mikehadlow.blogspot.co.nz/2010/08/nhibernate-linq-eager-fetching.html

score 0 · Answer 2 · answered Nov 13 '12 at 07:31

Your mapping is correct. When using Criteria API I can achieve results as expected.

But the problem you are referencing is known and reported: https://nhibernate.jira.com/browse/NH-2707

But I am afraid, not fixed. So, If you can (in this scenario) use Criteria:

// example returning the complete list
var query = session.CreateCriteria<Preson>().List<Person>();

Then you can continue:

query.Select(x => new Person(x.Id)
...

score 0 · Answer 3 · answered Nov 13 '12 at 10:10

I can't understand why you're trying to make the following query:

var q = SessionInstance.Query<Person>()
           .Select(x => new Person(x.Id)
                              {
                                  FirstName = x.FirstName,
                                  LastName = x.LastName,
                                  PersonPictures = x.PersonPictures //this line has error
                              })
           .ToList();

The mapping and the domain class look correct. So you should simply proceed with the following:

IList<Person> persons = (from p in SessionInstance.Query<Person>()
select p).ToList();

If you want to apply filters to Pictures:

IList<Person> persons = (from p in SessionInstance.Query<Person>()
select p
where p.PersonPictures.Any(pp => pp.Property == [...]).ToList();

No need to remap every property, that's the job of the NHibernate mapping + NHibernate LINQ provider.

My `Person` class is very great. It has many properties & associations. Your solution is equivalent by `select * from tblPersons` in SQL. But I need to select some properties and associations of Person class. — Ehsan, Nov 14 '12 at 10:16

How use of select for associations in linq to NHibernate

3 Answers3