I have query return IContentQuery<TPart,TRecord>,i want use sum(),what can i do

Topics: Administration, Core, Customizing Orchard
May 25, 2015 at 4:29 AM
I have query return IContentQuery<TPart,TRecord>,i want use sum(),but DefaultContentQuery not sum(),what can i do
Developer
May 25, 2015 at 1:16 PM
You have to sum from LINQ to objects (after a List() on content query) as unfortunately there are no such aggregate functions.
May 26, 2015 at 8:17 AM
Piedone wrote:
You have to sum from LINQ to objects (after a List() on content query) as unfortunately there are no such aggregate functions.
thanks,if i use list on content query,this will query all my data,i need like Count().

SELECT count(*) as y0_
FROM QM_Orchard_Framework_ContentItemVersionRecord this_
   inner join QM_Orchard_Framework_ContentItemRecord contentite1_
     on this_.ContentItemRecord_id = contentite1_.Id
   inner join QM_HappyZu_FenXiao_UserKPIPartRecord userkpipar2_
     on contentite1_.Id = userkpipar2_.Id
WHERE userkpipar2_.Id < 1000 / @p0 /
   and this_.Published = 1 /* @p1 */
   and this_.Published = 1 /* @p2 */
I try to extend contentquery,but appear an error,like this:
my query code:
            query.Sum<UserKPIPartRecord>(x=>x.BrokerageAmount);
my extend contentquery:
    decimal Sum<TRecord>(Expression<Func<TRecord, object>> predicate) 
    {
        var criteria = (ICriteria)BindItemVersionCriteria().Clone();
        criteria.ClearOrders();

        criteria.ApplyVersionOptionsRestrictions(_versionOptions);

        return criteria.SetProjection(Projections.Sum<TRecord>(predicate)).UniqueResult<decimal>();
    }
error:
  could not resolve property: BrokerageAmount of: Orchard.ContentManagement.Records.ContentItemVersionRecord
May 29, 2015 at 10:31 PM
Didn't try this way where criteria seem to be applied to the ContentItemVersionRecord... But I've tried another solution that seems to work. This with a QueryOver() to be able to use NHibernate Projections Extensions. Note that you need a session object that you can acquire with an ISessionLocator
var session = _sessionLocator.For(typeof(YourPartRecord));
....
var sum = session.QueryOver<YourPartRecord>()   
    .JoinQueryOver<ContentItemRecord>(x => x.ContentItemRecord)
    .JoinQueryOver<ContentItemVersionRecord>(x => x.Versions)
    .Where(x => x.Published)
    .Select(Projections.Sum<YourPartRecord>(x => x.YourProperty))
    .UnderlyingCriteria.UniqueResult();

Best