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