I think the most common scenario is paging in the query to fetch only the records in the current page, so its too bad you can't order by the count projection with linq
On Tue, May 18, 2010 at 6:53 PM, Diego Mijelshon <[email protected]>wrote: > Unless you need server-side paging, you can always use AsEnumerable and do > the ordering with Linq-to-objects. > > Diego > > > > On Tue, May 18, 2010 at 12:27, nadav s <[email protected]> wrote: > >> With linq trying to order by the count throws exception >> with criteria it will work, the sub query is just another projection >> >> >> On Tue, May 18, 2010 at 6:21 PM, James Crowley >> <[email protected]>wrote: >> >>> Unfortunately it doesn't seem to work the moment you want to add any kind >>> of order by clause relating to BarCount. >>> >>> James >>> >>> >>> On 18 May 2010 14:22, Diego Mijelshon <[email protected]> wrote: >>> >>>> Yes, it works. >>>> >>>> Diego >>>> >>>> >>>> >>>> On Tue, May 18, 2010 at 09:27, Mohamed Meligy <[email protected]>wrote: >>>> >>>>> Does this work on NH trunk LINQ provider? >>>>> >>>>> var query = from foo in session.Query<Foo>() select new FooDTO {Foo = >>>>> foo, BarCount = foo.Bars.Count()}; >>>>> >>>>> I think typically the problem for this might be the "IEnumerable" type >>>>> of Bars. Not sure if NH implementation can handle such query though, but >>>>> it's worth trying. >>>>> >>>>> -- >>>>> Mohamed Meligy >>>>> Senior Developer, Team Lead Backup (.Net Technologies - TDG - >>>>> Applications) >>>>> Injazat Data Systems >>>>> P.O. Box: 8230 Abu Dhabi, UAE. >>>>> >>>>> Phone: +971 2 6992700 >>>>> Direct: +971 2 4045385 >>>>> Mobile: +971 50 2623624, +971 55 2017 621 >>>>> >>>>> E-mail: [email protected] >>>>> Weblog: http://gurustop.net >>>>> >>>>> >>>>> >>>>> On Tue, May 18, 2010 at 2:50 PM, nadav s <[email protected]> wrote: >>>>> >>>>>> may be write the sql thisway: >>>>>> select foo.*, (select count(*) from bars where bars.fooid = foo.id) >>>>>> barsCount >>>>>> from foo >>>>>> >>>>>> The folowing code executes the query and get the results you wanted >>>>>> >>>>>> IClassMetadata deptMetaData = >>>>>> >>>>>> sessionFactory.GetClassMetadata(typeof(Department)); >>>>>> string[] propertyNames = deptMetaData.PropertyNames; >>>>>> >>>>>> >>>>>> ICriteria c = s.CreateCriteria<Department>("d"); >>>>>> DetachedCriteria dc = DetachedCriteria.For<Employee>("e"); >>>>>> dc.Add(Restrictions.EqProperty("d.id", "e.Department.id >>>>>> ")); >>>>>> dc.SetProjection(Projections.Count("id")); >>>>>> >>>>>> ProjectionList projList = Projections.ProjectionList(); >>>>>> foreach (string propName in propertyNames) >>>>>> { >>>>>> projList.Add(Projections.Property(propName)); >>>>>> } >>>>>> projList.Add(Projections.Alias(Projections.SubQuery(dc), >>>>>> "counting")); >>>>>> c.SetProjection(projList); >>>>>> c.SetMaxResults(1); >>>>>> IList results = c.List(); >>>>>> . >>>>>> You extract the building of the projection list of all the properties >>>>>> of the class to an a utils class like ProjectionList >>>>>> MyProjections.AllProperties(Type type) >>>>>> for creating the projections for all the properties of the class >>>>>> >>>>>> i think the proper way to read the results is with a custom result >>>>>> transformer but i'm not sure >>>>>> >>>>>> >>>>>> On Tue, May 18, 2010 at 12:46 PM, James Crowley < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> I too would be interested to get people's suggestions on how best to >>>>>>> do this? You can't seem to get the AliasToBean transformer to deal with >>>>>>> the >>>>>>> fact we want to set properties on a child property of the DTO either? >>>>>>> >>>>>>> Would appreciate any pointers you guys have! >>>>>>> >>>>>>> Thanks >>>>>>> >>>>>>> James >>>>>>> >>>>>>> >>>>>>> On 10 May 2010 13:17, kaa <[email protected]> wrote: >>>>>>> >>>>>>>> Say I have two entities Foo and Bar where Foo has-many Bar's, >>>>>>>> >>>>>>>> class Foo { >>>>>>>> int ImportantNumber { get; set; } >>>>>>>> IEnumerable<Bar> Bars { get; set; } >>>>>>>> } >>>>>>>> >>>>>>>> class FooDTO { >>>>>>>> Foo Foo { get; set; } >>>>>>>> int BarCount { get; set; } >>>>>>>> } >>>>>>>> >>>>>>>> How can I efficiently sum up the number of Bars per Foo in a DTO >>>>>>>> using >>>>>>>> a single query, preferrably only with the Criteria interface. >>>>>>>> I have tried any number of ways to get the original entity out of a >>>>>>>> query with ´SetProjection´ but no luck. The current theory is to do >>>>>>>> something like >>>>>>>> >>>>>>>> SELECT >>>>>>>> Foo.*, BarCounts.counts >>>>>>>> FROM >>>>>>>> Foo LEFT JOIN >>>>>>>> ( SELECT fooId, COUNT(*) as counts FROM Bar ON >>>>>>>> Foo.id=BarCounts.fooId >>>>>>>> >>>>>>>> but with Criterias, and I just can't seem to figure out how. >>>>>>>> >>>>>>>> I posted the same question over at stackoverflow.com where I got >>>>>>>> the >>>>>>>> following suggestion, using HQL, >>>>>>>> >>>>>>>> var fooDTOs = session.CreateQuery(@" >>>>>>>> select foo as Foo, >>>>>>>> count(elements(foo.Bars)) as BarCount >>>>>>>> from Foo foo >>>>>>>> group by foo.id, foo.ImportantNumber") >>>>>>>> .SetResultTransformer(Transformers.AliasToBean<FooDTO>()) >>>>>>>> .List<FooDTO>(); >>>>>>>> >>>>>>>> My problem with this is that my actual Foo has a lot of properties >>>>>>>> and >>>>>>>> maintaining that is a bit of a worry. Can I somehow write something >>>>>>>> closer to my original SQL query? >>>>>>>> >>>>>>>> Any help would be appreciated. >>>>>>>> >>>>>>>> -- >>>>>>>> You received this message because you are subscribed to the Google >>>>>>>> Groups "nhusers" group. >>>>>>>> To post to this group, send email to [email protected]. >>>>>>>> To unsubscribe from this group, send email to >>>>>>>> [email protected]<nhusers%[email protected]> >>>>>>>> . >>>>>>>> For more options, visit this group at >>>>>>>> http://groups.google.com/group/nhusers?hl=en. >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> --- >>>>>>> James Crowley >>>>>>> CEO, developerFusion - the global developer community - >>>>>>> http://www.developerfusion.com/ >>>>>>> CTO, TechEye - all the technology news unfit for print - >>>>>>> http://www.techeye.net/ >>>>>>> >>>>>>> linkedin: http://linkedin.com/in/jamescrowley >>>>>>> twitter: http://twitter.com/jamescrowley >>>>>>> >>>>>>> -- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "nhusers" group. >>>>>>> To post to this group, send email to [email protected]. >>>>>>> To unsubscribe from this group, send email to >>>>>>> [email protected]<nhusers%[email protected]> >>>>>>> . >>>>>>> For more options, visit this group at >>>>>>> http://groups.google.com/group/nhusers?hl=en. >>>>>>> >>>>>> >>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "nhusers" group. >>>>>> To post to this group, send email to [email protected]. >>>>>> To unsubscribe from this group, send email to >>>>>> [email protected]<nhusers%[email protected]> >>>>>> . >>>>>> For more options, visit this group at >>>>>> http://groups.google.com/group/nhusers?hl=en. >>>>>> >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "nhusers" group. >>>>> To post to this group, send email to [email protected]. >>>>> To unsubscribe from this group, send email to >>>>> [email protected]<nhusers%[email protected]> >>>>> . >>>>> For more options, visit this group at >>>>> http://groups.google.com/group/nhusers?hl=en. >>>>> >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "nhusers" group. >>>> To post to this group, send email to [email protected]. >>>> To unsubscribe from this group, send email to >>>> [email protected]<nhusers%[email protected]> >>>> . >>>> For more options, visit this group at >>>> http://groups.google.com/group/nhusers?hl=en. >>>> >>> >>> >>> >>> -- >>> >>> --- >>> James Crowley >>> CEO, developerFusion - the global developer community - >>> http://www.developerfusion.com/ >>> CTO, TechEye - all the technology news unfit for print - >>> http://www.techeye.net/ >>> >>> linkedin: http://linkedin.com/in/jamescrowley >>> twitter: http://twitter.com/jamescrowley >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "nhusers" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]<nhusers%[email protected]> >>> . >>> For more options, visit this group at >>> http://groups.google.com/group/nhusers?hl=en. >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "nhusers" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]<nhusers%[email protected]> >> . >> For more options, visit this group at >> http://groups.google.com/group/nhusers?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
