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