NHibernate QueryOver C#, selecting a specific property of a joined Entity to List field within a Dto

63 Views Asked by At

I was wondering how to do the following in QueryOver (NHibernate 4.0.4)

Say I have this set of classes

class Student 
{
    public virtual long Id { get; set; }
    public virtual IList<Exam> Exams { get; set; }
}

class Exam 
{
    public virtual string Subject { get; set; }
    public virtual int Score { get; set; }
    //Many more unneeded properties
}

class StudentDto 
{
    public long Id { get; set; }
    public IList<string> Subjects { get; set; }
    public IList<int> Scores { get; set; }
}

How would I go about getting all students along with their Subjects and Scores without fetching the whole entities?

The functionality I am going for is:


foreach(var exam in student.Exams)
{
    dto.Subjects.Add(exam.Subject);
    dto.Scores.Add(exam.Score);
}

But hopefully using some of NHibernate's functionality like SelectList

Basically what I am trying to do is something along these lines:

StudentDto dto = null;
Student student = null;
Exam exam = null;
QueryOver<Student>(() => student)
    .SelectList(list => list
        .Select(st => st.Exams.Select(x => x.Subject)).WithAlias(() => dto.Subjects)
        .Select(st => st.Exams.Select(x => x.Score)).WithAlias(() => dto.Scores)
    )

I've tried the above, I get an exception that the object is out of scope

QueryOver<Student>(() => student)
    .SelectList(list => list
        .Select(() => student.Exams.Select(x => x.Subject)).WithAlias(() => dto.Subjects)
        .Select(() => student.Exams.Select(x => x.Score)).WithAlias(() => dto.Scores)
    )

I've tried this, throws a null reference exception, I've also tried the above with a ternary operator to check for whether or not the lists are null and pass an empty list if so, didn't work

EDIT

I would like to return a list of StudentDtos with every one of them containing a list of the respective student's Scores and Subjects.

So a structure somewhat resembling this

StudentDto
    -> long ID
    -> List<string> subjects -> Math
                             -> Physics
                             -> etc..
    -> List<int> scores      -> 100
                             -> 94
                             -> etc..
1

There are 1 best solutions below

2
Firo On

That's what JoinAlias is for. If the goal is to get students by some filter then this might look like this

Exam exam = null;
var data = session.QueryOver<Student>()
    //.Where( whatever)
    .JoinAlias(s => s.Exams, () => exam)
    .Select(s => s.Id, s => exam.Subject, s => exam.Score)
    .OrderBy(s => s.Id).Asc
    .List<object[]>();

var studentDtos = new List<StudentDto>();
foreach (var item in data)
{
    var id = (long)item[0];
    StudentDto current = studentDtos.LastOrDefault();
    if (studentDtos.Count == 0 || current.Id != id)
    {
        studentDtos.Add(current = new StudentDto { Id = id });
    }
    current.Subjects.Add((string)item[1]);
    current.Scores.Add((int)item[2]);
}