"The most common scenario" based on which stats? Anyway, yeah, it's annoying. It's worth opening a JIRA.
Diego On Tue, May 18, 2010 at 13:38, nadav s <[email protected]> wrote: > 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]<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.
