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