How to use NHibernate Projections to retrieve a collection

Question

I am lazy loading the collections, and also because there are so many fields within the person table, I am writing a projection function to retrieve only certain properties. It works with properties, just not collections of other entities. I would be fine if they were loaded in as proxies and i could get them later, but right now it just loads in null.

public IList<Person> ListTop40()
        {
            var list = _session.CreateCriteria(typeof(Person))
                   .SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("FirstName"))
                   .Add(Projections.Property("LastName"))
                   .Add(Projections.Property("Jersey"))
                   .Add(Projections.Property("FortyYard"))
                   .Add(Projections.Property("BenchReps"))
                   .Add(Projections.Property("VertJump"))
                   .Add(Projections.Property("ProShuttle"))
                   .Add(Projections.Property("LongJump"))
                   .Add(Projections.Property("PersonSchoolCollection"))
                    )
                    .List<IList>()
                    .Select(l => new Person() { FirstName = (string)l[0], LastName = (string)l[1], Jersey = (Decimal)l[2], FortyYard = (Decimal)l[3], BenchReps = (Decimal)l[4], VertJump = (Decimal)l[5], ProShuttle = (Decimal)l[6], LongJump = (Decimal)l[7], PersonSchoolCollection = (IList<Person_School>)l[8]});

            IList<Person> s = list.ToList();
            return s;
        }

score 3 · Answer 1 · answered Dec 23 '10 at 12:33

try using AliasToBeanResultTransformer:

var list = _session.CreateCriteria(typeof(Person))
               .SetProjection(Projections.ProjectionList()
               .Add(Projections.Property("FirstName"))
               .Add(Projections.Property("LastName"))
               .Add(Projections.Property("Jersey"))
               .Add(Projections.Property("FortyYard"))
               .Add(Projections.Property("BenchReps"))
               .Add(Projections.Property("VertJump"))
               .Add(Projections.Property("ProShuttle"))
               .Add(Projections.Property("LongJump"))
               .Add(Projections.Property("PersonSchoolCollection"))
                )
               .SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(Person)))
               .List<Person>();

score 1 · Answer 2 · answered Jul 30 '09 at 04:08

1

How many properties do you have ? I have around 30 maybe more on a Client entity and there's no problem when loading it in NH.

You might be worrying about performance when it's not really the case. (the old : premature optimization is the the root of all evil" :) )

Having that said - I doubt something like this is supported.

answered Jul 30 '09 at 04:08

sirrocco

7,975
4
59
81

I have around 80 properties, so it really is necessary in this situation. I'm pretty sure I can't retrieve it with this code, but i think there is some way with projections to be able to retrieve only 1 of many collections. – luke Jul 30 '09 at 13:05
in that case , you might have more luck in the nhusers group on google groups – sirrocco Jul 30 '09 at 13:11
I have it posted there. If i get an answer, ill post it on here. Thanks. – luke Jul 30 '09 at 13:23
For the record my table has 50 props , so yeah you beat me :) - but it still isn't a problem. Unless you have some TEXT columns in there, I wouldn't worry about it. – sirrocco Jul 30 '09 at 13:53
Well for some reason, the queries are taking about 3 seconds to load about 8 records, even though i have set up all the collections as lazy loaded. – luke Jul 30 '09 at 14:45
Is a "select * from BigTable" taking long in management studio ? (or whatever you're using) . If you're using mssql then install http://sqlprofiler.googlepages.com/ and see exactly the query being that reaches the database. Maybe it's doing something you don't expect. – sirrocco Jul 30 '09 at 14:53
No it doesnt take long at all. I just downloaded that profiler and ill run some test queries from nhibernate and see what its doing. Thanks for the tip – luke Jul 30 '09 at 15:15
Well it is showing up in the profiler, but i cant tell the time that it is taking to run the query. StartTime and EndTime dont have seconds properties, and the Duration says 0 or 976 every time i run it. – luke Jul 30 '09 at 15:27
And it's the exact same query , no more queries appear (as if it were loading up some other stuff) - and it takes 3 seconds in nh and verry fast in management studio ? Now this is just weird :) I think the time is in milliseconds. – sirrocco Jul 30 '09 at 17:34
It is only showing the one query, plus a bunch of NT AUTHORITY queries. The time might be in milliseconds, but its not accurate, because it is either 0 or 976 every time i try it, lol. I seriously doubt that its zero, and i also doubt that its 976 exactly every time. I'm not sure whats going on, but maybe i can build an hql query to solve this problem. Though i dont know anything about hql. – luke Jul 30 '09 at 18:00
I've never had a query work great in management studio and perform badly in NH . So I still think that there's something going on that you don't expect - or it's not immediately obvious. Having said that - I'm out of ideas. Good luck in search for the answer. :) – sirrocco Jul 30 '09 at 18:14

How to use NHibernate Projections to retrieve a collection

2 Answers2

Linked