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