BlogPost: Getting Orchard content items out of HQL

Topics: Customizing Orchard
Developer
Sep 24, 2014 at 1:17 AM
As I'm interested in performing queries in Orchard that are more advanced than what I can accomplish with IContentManager, I've been following Bertrand's recent blog posts rather closely.

https://weblogs.asp.net/bleroy/querying-orchard-in-hql
https://weblogs.asp.net/bleroy/joining-orchard-part-records-in-hql
https://weblogs.asp.net/bleroy/getting-orchard-content-items-out-of-hql

Trying to replicate what I've gleaned, I'm having some troubles.
I'm trying to see what I'm doing wrong by simplifying my problem, but I still hit some roadblocks.
public class ProfilePartRecord : ContentPartVersionRecord
{
    public virtual string FirstName { get; set; }

    public virtual string LastName { get; set; }

    public virtual string PrimaryPhone { get; set; }

    public virtual string PrimaryPhoneLabel { get; set; }

    public virtual string SecondaryPhone { get; set; }

    public virtual string SecondaryPhoneLabel { get; set; }
}
var pageSql = "SELECT DISTINCT(Profile.Id)"
                + " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
                + " JOIN ItemVersion.ContentItemRecord Item"
                + " JOIN ItemVersion.ProfilePartRecord Profile"
                + " WHERE ItemVersion.Published = true";

var countSql = "SELECT COUNT(DISTINCT Profile.Id)"
                + " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
                + " JOIN ItemVersion.ContentItemRecord Item"
                + " JOIN ItemVersion.ProfilePartRecord Profile"
                + " WHERE ItemVersion.Published = true";

var pageQuery = _profilePartSession.CreateQuery(pageSql)
        .SetFirstResult(pager.GetStartIndex())
        .SetMaxResults(pager.PageSize);

var countQuery = _profilePartSession.CreateQuery(countSql);

var resultSets = _profilePartSession
    .CreateMultiQuery()
    .Add(countQuery)
    .Add(pageQuery)
    .SetCacheable(true)
    .List();
Results in the following error..
2014-09-23 20:04:40,191 [90] NHibernate.Impl.MultiQueryImpl - (null) - Failed to execute multi query: [select count(distinct profilepar2_.Id) as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join FindATemp_Core_ProfilePartRecord profilepar2_ on contentite0_.Id=profilepar2_.Id where contentite0_.Published=1;
select distinct profilepar2_.Id as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join FindATemp_Core_ProfilePartRecord profilepar2_ on contentite0_.Id=profilepar2_.Id where contentite0_.Published=1 ORDER BY GETDATE() OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY;
]
 (null)
System.Data.SqlServerCe.SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = distinct ]
   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
   at NHibernate.Driver.BatcherDataReaderWrapper..ctor(IBatcher batcher, IDbCommand command)
   at NHibernate.Driver.BasicResultSetsCommand.GetReader(Nullable`1 commandTimeout)
   at NHibernate.Impl.MultiQueryImpl.DoList()
It seems that HQL is definitely happier with COUNT(DISTINCT(Profile.Id))
var countSql = "SELECT COUNT(DISTINCT(Profile.Id))"
                + " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
                + " JOIN ItemVersion.ContentItemRecord Item"
                + " JOIN ItemVersion.ProfilePartRecord Profile"
                + " WHERE ItemVersion.Published = true";
Although changing it results in a new error...
2014-09-23 20:09:27,407 [81] NHibernate.Hql.Parser - (null) - NoViableAltException(96@[])
 (null)
2014-09-23 20:09:27,425 [81] NHibernate.Hql.Parser - (null) - MismatchedTokenException(33!=21)
 (null)
2014-09-23 20:09:27,428 [81] NHibernate.Hql.Parser - (null) - Antlr.Runtime.RecognitionException: FROM expected (non-filter queries must contain a FROM clause)
   at NHibernate.Hql.Ast.ANTLR.HqlParser.selectFrom()
 (null)
2014-09-23 20:09:27,439 [81] Orchard.Exceptions.DefaultExceptionPolicy - Default - An unexpected exception was caught
NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: A recognition error occurred. near line 1, column 21
   at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
   at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()
   at NHibernate.Hql.StringQueryExpression.Translate(ISessionFactoryImplementor factory, Boolean filter)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)
And this is where I'm currently stuck...
Developer
Sep 24, 2014 at 2:09 AM
Before anyone asks, I'm using 1.x
Coordinator
Sep 25, 2014 at 6:11 PM
OK, so the problem is that you're using SQL CE, which doesn't support distinct inside count. An additional problem is that HQL won't do subqueries in FROM. One thing you can do is get rid of DISTINCT altogether. Just make sure your query can't return duplicates, which seems to be the case here, or use a GROUP BY.

Does this make sense?

I'll update my posts.
Developer
Sep 28, 2014 at 10:03 PM
Hey Bertrand,

Yes what you said makes sense.
Using SQL Server Compact Toolbox, I was able to verify that DISTINCT(COUNT(...)) was definitely not supported.

I modified my SQL.
var countSql = "SELECT COUNT(Profile.Id)"
    + " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
    + " JOIN ItemVersion.ContentItemRecord Item"
    + " JOIN ItemVersion.ProfilePartRecord Profile"
    + " JOIN ItemVersion.AddressPartRecord Address" 
    + " WHERE ItemVersion.Published = true"
    + " GROUP BY Profile.Id";

var pageSql = "SELECT Profile.Id"
    + " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
    + " JOIN ItemVersion.ContentItemRecord Item"
    + " JOIN ItemVersion.ProfilePartRecord Profile"
    + " JOIN ItemVersion.AddressPartRecord Address"
    + " WHERE ItemVersion.Published = true";
And I get the following exception..
2014-09-28 16:57:43,125 [83] NHibernate.Impl.MultiQueryImpl - (null) - Failed to execute multi query: [select count(profilepar2_.Id) as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join FindATemp_Core_ProfilePartRecord profilepar2_ on contentite0_.Id=profilepar2_.Id inner join FindATemp_Core_AddressPartRecord addresspar3_ on contentite0_.Id=addresspar3_.Id where contentite0_.Published=1 group by profilepar2_.Id;
select profilepar2_.Id as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join FindATemp_Core_ProfilePartRecord profilepar2_ on contentite0_.Id=profilepar2_.Id inner join FindATemp_Core_AddressPartRecord addresspar3_ on contentite0_.Id=addresspar3_.Id where contentite0_.Published=1 ORDER BY GETDATE() OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY;
]
 (null)
System.Data.SqlServerCe.SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = select ]
   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
   at NHibernate.Driver.BatcherDataReaderWrapper..ctor(IBatcher batcher, IDbCommand command)
   at NHibernate.Driver.BasicResultSetsCommand.GetReader(Nullable`1 commandTimeout)
   at NHibernate.Impl.MultiQueryImpl.DoList()
2014-09-28 16:57:43,152 [83] Orchard.Exceptions.DefaultExceptionPolicy - Default - An unexpected exception was caught
NHibernate.Exceptions.GenericADOException: Failed to execute multi query[SQL: select count(profilepar2_.Id) as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join FindATemp_Core_ProfilePartRecord profilepar2_ on contentite0_.Id=profilepar2_.Id inner join FindATemp_Core_AddressPartRecord addresspar3_ on contentite0_.Id=addresspar3_.Id where contentite0_.Published=1 group by profilepar2_.Id;
select profilepar2_.Id as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join FindATemp_Core_ProfilePartRecord profilepar2_ on contentite0_.Id=profilepar2_.Id inner join FindATemp_Core_AddressPartRecord addresspar3_ on contentite0_.Id=addresspar3_.Id where contentite0_.Published=1 ORDER BY GETDATE() OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY;
] ---> System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = select ]
   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
   at NHibernate.Driver.BatcherDataReaderWrapper..ctor(IBatcher batcher, IDbCommand command)
   at NHibernate.Driver.BasicResultSetsCommand.GetReader(Nullable`1 commandTimeout)
   at NHibernate.Impl.MultiQueryImpl.DoList()
   --- End of inner exception stack trace ---
   at NHibernate.Impl.MultiQueryImpl.DoList()
   at NHibernate.Impl.MultiQueryImpl.List()
I realized the problem was the MultiQuery, I replaced the call to CreateMultiQuery with...
var totalCount = countQuery.SetCacheable(true).List<long>().First();
var queryResultSet = pageQuery.SetCacheable(true).List<int>();
And I was able to collect my results properly.

Thanks for the help.
Coordinator
Sep 29, 2014 at 4:46 AM
So multiple queries won't work with CE either? That sucks. I'll update the post accordingly.
Developer
Sep 29, 2014 at 2:42 PM
Okay... So this should be the last question...
Applying ORDER BY to a calculated column...

I can order by Profile.Id with no problem.
But If I try to order by a computed field, the SQL fails.
var pageSql = "SELECT Profile.Id, concat(Profile.FirstName, Profile.LastName) AS FullName"
    + " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
    + " JOIN ItemVersion.ContentItemRecord Item"
    + " JOIN ItemVersion.ProfilePartRecord Profile"
    + " JOIN ItemVersion.AddressPartRecord Address"
    + " WHERE ItemVersion.Published = true"
    + " ORDER BY FullName";
Generates the following SQL
select profilepar2_.Id             as col_0_0_,
       ( profilepar2_.FirstName
         + profilepar2_.LastName ) as col_1_0_
from   Orchard_Framework_ContentItemVersionRecord contentite0_
       inner join Orchard_Framework_ContentItemRecord contentite1_
               on contentite0_.ContentItemRecord_id = contentite1_.Id
       inner join FindATemp_Core_ProfilePartRecord profilepar2_
               on contentite0_.Id = profilepar2_.Id
       inner join FindATemp_Core_AddressPartRecord addresspar3_
               on contentite0_.Id = addresspar3_.Id
where  contentite0_.Published = 1
order  by FullName  
How do I get HQL to keep my aliases? Other than inserting ORDER BY col_1_0_ in my input.

In this example I'm attempting to sort by something generic.
In my actual use case, I'm attempting to order by distance, computed by Latitude and Longitude

acos(sin(:lat) * sin(Address.Latitude/57.29) + cos(:lat) * cos(Address.Latitude/57.29) * cos(Address.Longitude/57.29 - (:lon))) * 3956 AS distance

Thanks again
Sep 29, 2014 at 2:59 PM
Edited Sep 29, 2014 at 3:11 PM
In SQL Server there are special spatial Geographical data type to do this as in this sample
http://msdn.microsoft.com/en-us/library/ff929109.aspx

using this with NH is another problem, since I use Orchard and NH, I can 't use all the power of stored procedures, it's hard to live
Developer
Sep 29, 2014 at 11:25 PM
I wrote a reply and somehow managed to close the browser without saving. Sigh.
Sep 30, 2014 at 12:09 AM
Edited Sep 30, 2014 at 12:09 AM
I frequently do this ... but I'm old :)
(The Dark Side Comments)
Developer
Sep 30, 2014 at 4:51 AM
But yea, I know SQL Server, and a few other DB Providers have special routines to deal with spatial queries.
But I rather keep SQL CE as my day to day development environment.
So I'm trying to keep backwards compatibility as best as possible.
Developer
Sep 30, 2014 at 6:12 AM
An additional problem is that HQL won't do subqueries in FROM.
That part of the problem just sunk in for me now. Damnit.
Developer
Oct 2, 2014 at 1:21 PM
So I switched to using SQL Server, but I couldn't get around the ORDER BY issue was experiencing above.

I was doing some searching to understand why that wouldn't work... until i found this...

Alias in HQL Order By Clause is Not Replaced - https://nhibernate.jira.com/browse/NH-3035

So it's an almost 3 year old open NHibernate bug....

I'm looking into learning how to use NHibernate's QueryOver

This guy seems like he has written a good resource on the topic... http://blog.andrewawhitaker.com/blog/2014/03/12/queryover-series-part-1-why-queryover/
Developer
Oct 2, 2014 at 2:45 PM
I think it's easier for me to fix this nHibernate bug.
Oct 2, 2014 at 4:11 PM
Have you considered the time to include your fix in NH distribution ?
Developer
Oct 2, 2014 at 5:14 PM
I annoyed my way into this community by sending pull requests.
I'm sure I can do it again.

Can someone point me to the nearest "Sebastien of nHibernate"?
Oct 2, 2014 at 5:20 PM
StanleyGoldman wrote:
Can someone point me to the nearest "Sebastien of nHibernate"?
I doubt he is still alive, looking to the vitality of NH community.
Sebastien of orchard should know him.
Developer
Oct 2, 2014 at 7:23 PM
Edited Oct 2, 2014 at 7:23 PM
I'm surprised you haven't mentioned Entity Framework yet... ;)
Oct 2, 2014 at 7:28 PM
Edited Oct 2, 2014 at 7:29 PM
Should I ?
No, the recent evolution of EF 7 with VNext confirms me all this is not good for Orchard.
In fact we should write our own DB abstraction layer for ContentItems, everything is already in Orchard, using an ORM only complicates, duplicates and badly slow down things, even a document DB is not totally adapted to what orchard is doing when associating parts and fields into content items.
Coordinator
Oct 2, 2014 at 8:00 PM
I am not disagreeing with christian here. Something similar to YesSql but adapted for Orchard, using a micro orm like dapper. And maybe a SQL dialect abstraction like NHibernate dialects.
Oct 3, 2014 at 8:47 AM
Edited Oct 3, 2014 at 9:06 AM
Question could be why bother with a DB System and not use directly Btrees on any file system ?
There should be something Open Source and reliable today and working with linq ?
EDIT: Ok I had a look and nothing but Nosql DBMS....strange that linq must go sql to query tables...
Developer
Oct 9, 2014 at 4:01 PM
I've managed to enlist the help of the nHibernate community.
Seems we will get this fixed.
Developer
Oct 11, 2014 at 3:53 AM
So I got some help with some people in the nHibernate community to port a patch from Hibernate to nHibernate.
I quickly realized that the patch we ported was from Hibernate only covers ORDER BY and doesn't cover the WHERE.

At this point I think HQL really sucks.
Oct 11, 2014 at 7:19 AM
Anyway good try.
So NH HQL really sucks ... :)
Oct 11, 2014 at 9:21 AM
Do you know BrightstarDB? http://brightstardb.com/ an open source db oriented to graphs that maybe fits better than a document db or a relational one with orchard
Oct 11, 2014 at 12:52 PM
Is there a running demo somewhere ?
It seems to be a dedicated DB for mobile phones ? On top of other storages (I can see the ccode where they store something on disk/file/whatever blob ) ?
Developer
Oct 12, 2014 at 1:18 AM
So I'm trying to understand how to use QueryOver<>

There are no Orchard examples in this, and I might be completely understanding it wrong.
But it seems like I'm very stuck.

To the best of my understanding (which is very little so far) QueryOver requires object to object relationships.
It wont work since ProfilePartRecord has a relationship to ContentItemRecord but not directly to another "PartRecord".
Which makes me think I misunderstand QueryOver, because it seems nHibernate is able to 'Figure It Out' ™ when processing HQL

How do I join parts using QueryOver<>()?

Here is my best attempt:
ContentItemRecord contentItemRecord = null;
            
AddressPartRecord addressPartRecord = null;
ContentItemRecord addressContentItemRecord = null;

var results = _profilePartSession.QueryOver<ProfilePartRecord>()
    .JoinAlias(record => record.ContentItemRecord, () => contentItemRecord)
    .JoinAlias(() => addressPartRecord.ContentItemRecord, () => addressContentItemRecord)
    .Where(() => addressContentItemRecord.Id == contentItemRecord.Id)
    .SelectList(builder => builder
        .Select(p => p.Id)
    ).List<object[]>();
Which earns me the exception:
2014-10-11 16:12:30,841 [10] Orchard.Exceptions.DefaultExceptionPolicy - Default - An unexpected exception was caught
System.Exception: Could not determine member from "address"
   at NHibernate.Impl.ExpressionProcessor.FindMemberExpression(Expression expression)
   at NHibernate.Criterion.QueryOver`2.JoinAlias(Expression`1 path, Expression`1 alias)
   at NHibernate.Criterion.QueryOver`2.NHibernate.IQueryOver<TRoot,TSubType>.JoinAlias(Expression`1 path, Expression`1 alias)
I've been using the information from these two sources.
http://blog.andrewawhitaker.com/blog/2014/03/12/queryover-series-part-1-why-queryover/
http://puredotnetcoder.blogspot.com/2011/07/queryover-with-multiple-joins-into-dto.html
Oct 16, 2014 at 6:05 AM
@CSADNT as far as I know it can work as an embedded db and also in client server configuration.

An important advantage of these kind of dbs is it overcomes performance limitations of relational dbs for JOIN operations but it doesn't force you to maintain to redundant data as document dbs do. Another interesting feature is it allows to manage schema less data but alternatively gives you an API Entity Framework-like to work with your structured data if it is easier for you. I feel those two points fit special well with Orchard.
More info in this tutorial http://brightstardb.readthedocs.org/en/latest/Why_BrightstarDB_/

I took interest on this db one year ago but the project in which I was evaluating the possibility of using this db was cancelled. So I'm not the best guy to answer your questions. However I remember I got quick and detailed answer to my doubts through its forums.
https://brightstardb.codeplex.com/discussions

It would be nice to see join forces to those two great .Net open source projects! ;)
Developer
Oct 31, 2014 at 5:30 PM
CSADNT wrote:
In SQL Server there are special spatial Geographical data type to do this as in this sample
http://msdn.microsoft.com/en-us/library/ff929109.aspx

using this with NH is another problem, since I use Orchard and NH, I can 't use all the power of stored procedures, it's hard to live
nHibernate is miserable.

http://stackoverflow.com/questions/238050/using-sql-server-2008-geography-types-with-nhibernates-createsqlquery
Oct 31, 2014 at 5:36 PM
Edited Oct 31, 2014 at 5:36 PM
I raise my mind to the idea that NH usage was just a touch and go for me....
Developer
Oct 31, 2014 at 5:47 PM
You can't f*cking escape a colon in nHibernate.

http://stackoverflow.com/questions/12459255/how-to-escape-colon-using-nhibernate

Why would anyone create a special character that you can't escape?
Oct 31, 2014 at 8:50 PM
StanleyGoldman wrote:
Why would anyone create a special character that you can't escape?
Art !
Developer
Oct 31, 2014 at 9:37 PM
I was able to create a User Defined Function, that contained the colons, and call that from my sql instead...
Just so poor nHibernate doesn't choke while trying to do it's job.