NH-3435 - Ability to select entities in Criteria projections

[nhibernate-bot]

Jonathan Hynes created an issue — 1st April 2013, 13:44:03:

The Criteria API need a way to select an Entity as a projection. Suggest the following methods:

Projection.RootEntity()
Projection.Entity(string associationPath)

This issue may be a duplicate of one of the following, but these issues have been closed and the problem is not resolved:
NH-1372
NH-928

Background to the issue can be found here:
http://stackoverflow.com/questions/15744057/nhibernate-how-to-select-the-root-entity-in-a-projection

---

Ricardo Peres added a comment — 6th March 2014, 20:46:57:

I implemented a solution: http://weblogs.asp.net/ricardoperes/archive/2014/03/06/custom-nhibernate-criteria-projections.aspx.
If anyone can confirm that it works, I may submit a pull request.

---

Jonathan Hynes added a comment — 14th March 2014, 22:42:07:

I tested using the MsSql2008Dialect. This does not appear to work because aliases are the property name and are not escaped/quoted. This can cause issues if keywords are used as column names. This could also cause problems if multiple projections have the same property name.

---

Ricardo Peres added a comment — 14th March 2014, 22:56:53:

What does "does not appear to work" mean? Does it return, in normal cases, what it should, or does it not?
Not using aliases was a deliberate option, so that we can use AliasToBean in order to generate entities from the projection. Quoting is easy to do.
Anyway, this was a proof of concept, I even made chances to the sample after I published that post. If I ever submit a pull request, I will address some of this issues.

---

Jonathan Hynes added a comment — 14th March 2014, 23:11:03:

My test is as follows:

//Code to persist 20 Person objects here

var list = Session.CreateCriteria<Person>()
              .SetFirstResult(5)
              .SetMaxResults(10)
              .SetProjection(new RootEntityProjection(), Projections.SqlFunction("rowcount", NHibernateUtil.Int64))
              .List<object[]>();

Assert.Equal(10, list.Count);
            Assert.Equal(20, (long)list<0>[1]);
            Assert.IsType<Person>(list<0>[0]);

The Person class has a User property. Since User is a SQL server keyword, I am getting the following error:

System.Data.SqlClient.SqlException : Incorrect syntax near the keyword 'User'.

Looking at the generated SQL, given the lack of uniquifying aliases, I also make the conjecture that the generated SQL could have alias conflicts. For instance, suppose Person had the following property:

public virtual Person Father{get;set;}

And I modified the query to say:

.CreateAlias("Father", "f")
.SetProjection(new RootEntityProjection(), new EntityProjection(Person, "f"))

I conjecture that this would create duplicate aliases in the generated SQL for every property in the Person class.

---

Ricardo Peres added a comment — 14th March 2014, 23:18:08:

The problem with having aliases is that we cannot turn the results into entities. Anyway, I will post a new version that addresses your concerns.

---

Ricardo Peres added a comment — 15th March 2014, 11:22:15:

Jonathan, thanks for your feedback! Can I please ask you to try this new version?

---

Jonathan Hynes added a comment — 15th March 2014, 12:07:05:

Now I am getting this error:

System.ArgumentOutOfRangeException : Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Result StackTrace:	
at System.ThrowHelper.ThrowArgumentOutOfRangeException()
   at System.SZArrayHelper.get_Item<T>(Int32 index)
   at System.Linq.Enumerable.ElementAt<TSource>(IEnumerable`1 source, Int32 index)
   at NHibernate.Criterion.BaseProjection.NHibernate.Criterion.IProjection.ToSqlString(ICriteria criteria, Int32 position, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters) in d:\dev\npf\Drg.M3\Dao\Base\BaseProjection.cs:line 131
   at NHibernate.Criterion.ProjectionList.ToSqlString(ICriteria criteria, Int32 loc, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetSelect(IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persister, CriteriaQueryTranslator translator, ISessionFactoryImplementor factory, ICriteria criteria, String rootEntityName, IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persister, ISessionFactoryImplementor factory, CriteriaImpl rootCriteria, String rootEntityName, IDictionary`2 enabledFilters)
   at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results)
   at NHibernate.Impl.CriteriaImpl.List(IList results)
   at NHibernate.Impl.CriteriaImpl.List<T>()
   at Drg.M3.Tests.Unit.Core.NHibernateFacts.RootEntityProjectionWorks() in d:\dev\npf\Drg.M3.Tests.Unit\Core\NHibernateFacts.cs:line 26

FYI, my persister is a JoinedSubclassEntityPersister. It's getting this error on the first property and first column name (i==0, c==0), and the property in question is a one-to-one.

---

Ricardo Peres added a comment — 13th August 2014, 14:31:12:

Pull request: #299

---

Ricardo Peres added a comment — 14th August 2014, 10:22:41:

New pull request: #302

---

Ricardo Peres added a comment — 10th October 2014, 13:26:18:

Fix version? Maybe 4.1?

---

Alexander Zaytsev added a comment — 12th August 2016, 4:40:11:

Move unresolved improvements and new features with minor and trivial priorities to 4.2.0

---

Alexander Zaytsev added a comment — 14th September 2017, 1:47:17:

Move issues from 4.2 to 5.0

---

Alexander Zaytsev added a comment — 14th September 2017, 1:50:03:

Move new features to 5.1