Query ContentTypes and Filter on Taxonomy Field

Topics: Core
Aug 15, 2013 at 3:23 PM
Edited Aug 15, 2013 at 7:18 PM
Hi,

I have a ContentType called "Klant" with a Taxonomy field attached to it.

When I Query this ContentType I want to filter it by the terms that are selected on the Taxonomy field.

I'm getting close when using the TermsPartRecord like this:
var lQuery = _orchardServices.ContentManager
    .Query(new[] { "Klant" })
        .WhereIf<TermsPartRecord>((selectedIds.Count > 0), 
            m => m.Terms.Any(y => y.TermRecord.Id == 28))
        .WhereIf<KlantPartRecord>(!string.IsNullOrWhiteSpace(options.Expression),
            m =>
                m.NAAM.Contains(options.Expression) ||
                m.PLAATS.Contains(options.Expression) ||
                m.EMAIL.Contains(options.Expression) ||
                m.SOFTWARE_VERSIE.Contains(options.Expression) ||
                m.FTP_USER.Contains(options.Expression) ||
                m.OPMERKINGEN.Contains(options.Expression)
        );                        );
This filters the "Klant" content items which has selected the Term with an id of 28.

But when I have multiple id's I want to filter on and use the following query: ( selectedIds is an List<int> )
var lQuery = _orchardServices.ContentManager
    .Query(new[] { "Klant" })
        .WhereIf<TermsPartRecord>((selectedIds.Count > 0),
            m => m.Terms.Any(
                y => selectedIds.IndexOf(y.TermRecord.Id) != -1))
        .WhereIf<KlantPartRecord>(!string.IsNullOrWhiteSpace(options.Expression),
            m =>
                m.NAAM.Contains(options.Expression) ||
                m.PLAATS.Contains(options.Expression) ||
                m.EMAIL.Contains(options.Expression) ||
                m.SOFTWARE_VERSIE.Contains(options.Expression) ||
                m.FTP_USER.Contains(options.Expression) ||
                m.OPMERKINGEN.Contains(options.Expression)
    );
I get the following error: "Cannot use subqueries on a criteria without a projection."
2013-08-15 16:20:47,403 [5] Orchard.Exceptions.DefaultExceptionPolicy - An unexpected exception was caught
NHibernate.QueryException: Cannot use subqueries on a criteria without a projection.
   at NHibernate.Criterion.SubqueryExpression.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition(IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persister, CriteriaQueryTranslator translator, ISessionFactoryImplementor factory, ICriteria criteria, String rootEntityName, IDictionary`2 enabledFilters)
   at NHibernate.Criterion.SubqueryExpression.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition(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.UniqueResult[T]()
   at Orchard.ContentManagement.DefaultContentQuery.Count() in c:\OECDEV\Projects\Orchard\Orchard.Source.1.7\src\Orchard\ContentManagement\DefaultContentQuery.cs:line 174
   at Orchard.ContentManagement.DefaultContentQuery.ContentQuery`1.Orchard.ContentManagement.IContentQuery<T>.Count() in c:\OECDEV\Projects\Orchard\Orchard.Source.1.7\src\Orchard\ContentManagement\DefaultContentQuery.cs:line 220
   at Cordent.Backoffice.Services.KlantService.GetKlantenCount(KlantenSearchVM options)
   at Cordent.Backoffice.Controllers.KlantAdminController.Index(PagerParameters pagerParameters, KlantenSearchVM search)
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass13.<InvokeActionMethodWithFilters>b__10()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass13.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass13.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
I don't understand why the "selectedIds.IndexOf(y.TermRecord.Id)" part is modifying the underlying query which then breaks.

Thank you in advance!

note: the "WhereIf" is a custom modification of IContentQuery, it only applies the predicate if the the condition is met.

edit: I'm working on the 1.7 sourcecode release.
Developer
Aug 16, 2013 at 5:41 AM
This means that the method you're using cannot be translated into SQL (or HQL, I don't know if it's a direct translation or not) by the LINQ provider.
Perhaps try this:
y => selectedIds.Contains(y.TermRecord.Id))
Aug 16, 2013 at 7:03 AM
Ah, I remember I encountered this type problem before indeed. Thank you!


The code below works. Now I'm going to add some code to handle the case of multiple taxonomy fields on the ContentType Klant.
 var lQuery = _orchardServices.ContentManager
                .Query(new[] { "Klant" })
                    .WhereIf<TermsPartRecord>((selectedIds.Count > 0),
                        m => m.Terms.Any(
                            y => selectedIds.Contains(y.TermRecord.Id)))
                    .WhereIf<KlantPartRecord>(!string.IsNullOrWhiteSpace(options.Expression),
                        m =>
                            m.NAAM.Contains(options.Expression) ||
                            m.PLAATS.Contains(options.Expression) ||
                            m.EMAIL.Contains(options.Expression) ||
                            m.SOFTWARE_VERSIE.Contains(options.Expression) ||
                            m.FTP_USER.Contains(options.Expression) ||
                            m.OPMERKINGEN.Contains(options.Expression)
                );