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]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
