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