How to "Select" with nhibernate queryover

Question

I want to use query over to give me back an object

 public class TaskMap : ClassMap<Task>
    {
        public TaskMap()
        {
            Table("Tasks");
            Id(x => x.TaskId);
            Map(x => x.TaskName).NvarcharWithMaxSize().Not.Nullable();
            Map(x => x.Description).NvarcharWithMaxSize();
            Map(x => x.DueDate).Not.Nullable();
            HasMany(x => x.PersonalTaskReminders).Inverse();
            HasMany(x => x.TaskReminders).Inverse();
            References(x => x.Course).Not.Nullable();
            HasMany(x => x.CompletedTasks);

        }
    }



[Serializable()]
public class Task
{
    public virtual int TaskId { get; private set; }
    public virtual string TaskName { get;  set; }
    public virtual string Description { get; set; }
    public virtual DateTime DueDate { get; set; }
    public virtual IList<PersonalTaskReminder> PersonalTaskReminders { get; set; }
    public virtual IList<TaskReminder> TaskReminders { get; set; }
    public virtual IList<CompletedTask> CompletedTasks { get; set; }
    public virtual Course Course { get; set; }

    public Task()
    {
        PersonalTaskReminders = new List<PersonalTaskReminder>();
        TaskReminders = new List<TaskReminder>();
        CompletedTasks = new List<CompletedTask>();
    }

}
     public class PlannerTask
{
    public int TaskId { get; set; }
    public string TaskName { get; set; }
    public string Description { get; set; }
    public DateTime DueDate { get; set; }
    public List<PersonalTaskReminder> PersonalTaskReminders { get; set; }
    public List<TaskReminder> TaskReminders { get; set; }
    public Course Course { get; set; }
    public bool IsCompleted { get; set; }


}

Try 1

Task tAlias = null;
        PlannerTask plannerTask = null;
        List<PlannerTask> result = session.QueryOver<Task>(() => tAlias)
            .Select(x => x.Course).TransformUsing(Transformers.AliasToBean<PlannerTask>())               
            .List<PlannerTask>().ToList();

result: 188 records with all null for course object.

Try 2

Task tAlias = null;
            PlannerTask plannerTask = null;
            List<PlannerTask> result = session.QueryOver<Task>(() => tAlias)
                         .Select(Projections.Property(() => tAlias.TaskId).WithAlias(() => plannerTask.TaskId),
                         Projections.Property(() => tAlias.TaskName).WithAlias(() => plannerTask.TaskName),
                         Projections.Property(() => tAlias.DueDate).WithAlias(() => plannerTask.DueDate),
                         Projections.Property(() => tAlias.Description).WithAlias(() => plannerTask.Description),
                         Projections.Property(() => tAlias.PersonalTaskReminders).WithAlias(() => plannerTask.PersonalTaskReminders),
                         Projections.Property(() => tAlias.TaskReminders).WithAlias(() => plannerTask.PersonalTaskReminders),
                         Projections.Property(() => tAlias.Course).WithAlias(() => plannerTask.Course))
                .TransformUsing(Transformers.AliasToBean<PlannerTask>())               
                .List<PlannerTask>().ToList();

Result: 188 courses with all properties having pretty much the same error

'((new System.Collections.Generic.Mscorlib_CollectionDebugView<Domain.PlannerTask>(result)).Items[0].Course).CoursePermissions' threw an exception of type 'NHibernate.ObjectNotFoundException'

Try 3

 Task tAlias = null;
        PlannerTask plannerTask = null;
        List<PlannerTask> result = session.QueryOver<Task>(() => tAlias)
             .Select(Projections.Property(() => tAlias.TaskId).WithAlias(() => plannerTask.TaskId),
             Projections.Property(() => tAlias.TaskName).WithAlias(() => plannerTask.TaskName),
             Projections.Property(() => tAlias.DueDate).WithAlias(() => plannerTask.DueDate),
             Projections.Property(() => tAlias.Description).WithAlias(() => plannerTask.Description),
             Projections.ProjectionList().Add(Projections.Property(()=> tAlias.PersonalTaskReminders).WithAlias(() => plannerTask.PersonalTaskReminders)),
             Projections.ProjectionList().Add(Projections.Property(()=> tAlias.TaskReminders).WithAlias(() => plannerTask.PersonalTaskReminders)),
             Projections.Property(() => tAlias.Course).WithAlias(() => plannerTask.Course))
            .TransformUsing(Transformers.AliasToBean<PlannerTask>())               
            .List<PlannerTask>().ToList();


SELECT this_.TaskId      as y0_,
       this_.TaskName    as y1_,
       this_.DueDate     as y2_,
       this_.Description as y3_,
       this_.TaskId      as y4_,
       this_.TaskId      as y4_,
       this_.CourseId    as y4_
FROM   Tasks this_

Result:

CoursePermissions = '((Castle.Proxies.CourseProxy)((new System.Collections.Generic.Mscorlib_CollectionDebugView<PlannerTask>(result)).Items[0].Course)).CoursePermissions' threw an exception of type 'NHibernate.ObjectNotFoundException'

try 4

Try 4

 Task tAlias = null;
        PlannerTask plannerTask = null;
        List<PlannerTask> result = session.QueryOver<Task>(() => tAlias)
             .Select(Projections.Property(() => tAlias.TaskId).WithAlias(() => plannerTask.TaskId),
             Projections.Property(() => tAlias.TaskName).WithAlias(() => plannerTask.TaskName),
             Projections.Property(() => tAlias.DueDate).WithAlias(() => plannerTask.DueDate),
             Projections.Property(() => tAlias.Description).WithAlias(() => plannerTask.Description),
             Projections.Property(() => tAlias.Course).WithAlias(() => plannerTask.Course))
            .TransformUsing(Transformers.AliasToBean<PlannerTask>())               
            .List<PlannerTask>().ToList();


SELECT this_.TaskId      as y0_,
       this_.TaskName    as y1_,
       this_.DueDate     as y2_,
       this_.Description as y3_,
       this_.CourseId    as y4_
FROM   Tasks this_

Works. I can access everything in the course object.

It seems to have a problem with

tAlias.PersonalTaskReminders and tAlias.TaskReminders. If I remove these Course will render fine.

I don't understand why.

@Mauricio Scheffer - I am trying to do a select which puts the results in a Collection of PlannerTask objects but the Course Object in each of the PlannerTask objects throws and exception. — chobo2, May 08 '11 at 02:42
I started writing an answer, but I'm questioning why this is even a requirement. Why can you not get regular Task objects and then map them to PlannerTask objects using either a custom solution or something like AutoMapper or ValueInjecter? From the SQL you posted it looks like it's not doing any joins and therefore not creating the proper collections. — Vadim, May 13 '11 at 19:26
@Vadim - I thought this would be faster to do it all in one go. — chobo2, May 13 '11 at 19:51
@chobo you can still do it in one go, by using eager fetching. `session.QueryOver().Fetch(t => t.PersonalTaskReminders).Eager...` Any eagerly fetched entities will be fetched in one query. — Vadim, May 13 '11 at 21:04
@Vadmin - Yes I am doing that already(not shown in my code) but I meant that it still would be faster to do a select and stick in then a select get the tasks then automap it. — chobo2, May 13 '11 at 22:24

score 26 · Accepted Answer · answered May 12 '11 at 01:20

26

I think the problem is you are attempting to project Course proxy objects. Projections are for flat data transfer objects. You can declare the Course properties you need for this view.

public class PlannerTask
{
    public int TaskId { get; set; }
    public string TaskName { get; set; }
    public string Description { get; set; }
    public DateTime DueDate { get; set; }
    //flatten Course object
    public int CourseId { get; set; }
    public int CourseName { get; set; }
    public bool IsCompleted { get; set; }
}

The select list can be simplified a little...

PlannerTask plannerTask = null;
Course courseAlias = null;

List<PlannerTask> result = session.QueryOver<Task>()
    .JoinAlias(x => x.Course, () => courseAlias)
    .SelectList(list => list
        .Select(x => x.TaskId).WithAlias(() => plannerTask.TaskId)
        .Select(x => x.TaskName).WithAlias(() => plannerTask.TaskName)
        .Select(x => x.DueDate).WithAlias(() => plannerTask.DueDate)
        .Select(x => x.Description).WithAlias(() => plannerTask.Description)
        .Select(x => x.Course.Id).WithAlias(() => plannerTask.CourseId))
        .Select(x => courseAlias.Name).WithAlias(() => plannerTask.CourseName))
    .TransformUsing(Transformers.AliasToBean<PlannerTask>())               
    .List<PlannerTask>();

answered May 12 '11 at 01:20

dotjoe

26,242
5
63
77

I am not sure what the proxy object is but why would it project propertly if I just have "Course" and not " PersonalTaskReminders / TaskReminders but when I add those 2 it fails? – chobo2 May 12 '11 at 16:04
1

You can't project a mapped object like `Course`. When you try to do that you're left with this sort of zombied proxy object that throws `NHibernate.ObjectNotFoundException` when you access the lazy loaded properties. – dotjoe May 12 '11 at 18:05
I would understand that if it was not for the fact that "Try 4" works. So why can it do it when I leave out the those 2 other mapped objects(PersonalTaskReminders/TaskReminders). If "Try 4" did not work then it would make sense but since it works it confuses me. – chobo2 May 12 '11 at 18:08
oh, that does work? That's kind of cool, I never tried it before. Does it only select the course id and then it lazy loads the rest of the course object? So, those errors are only caused when you project a collection? – dotjoe May 12 '11 at 18:17
I will have to check but nothing was throwing errors. I only get those errors when I have protecting of those collections. Somehow they effect the Course object. They seem to always be just null as well. – chobo2 May 12 '11 at 18:44

How to "Select" with nhibernate queryover

1 Answers1

Linked