c# - Creating objects from stored procedure result -

we have existing sql server stored procedure following query. need create collection of student objects in following class design, result of query.

what best way create objects sqldatareader using linq?

note: using sqldatareader only; no orm

query

select      s.studentid, s.studentname, e.examid, e.examname, se.mark       studentexam se inner join      student s on s.studentid = se.studentid inner join      exam e on e.examid = se.examid  

class

public class exammark {     public int examid { get; set; }     public string examname { get; set; }     public int mark { get; set; } }  public class student {     public int studentid { get; set; }     public string studentname { get; set; }     public list<exammark> examresults { get; set; } } 

sqldatareader

   sqldatareader reader = command.executereader();    if (reader.hasrows)    {       while (reader.read())       {        }    }  

references

1. linq: fill objects left join
2. complex group on datatable

well, wouldn't that,

i'd have 2 statements

-- student statement select              s.studentid,              s.studentname                  student s                  exists (               select * studentexam se se.studentid = s.student.id);  -- exam statement select             se.studentid,             e.examid,             e.examname,             se.mark                  studentexam se         join             exam e                 on e.examid = se.examid; 

then, i'd have function this,

private ienumerable<tuple<int, exammark>> getmarks() {     ... setup exam command here     var reader = examcommand.executereader();     while (reader.read())     {         yield return tuple.create(             reader.getint32(0),             new exammark                 {                     reader.getint32(1),                     reader.getstring(2),                     reader.getint32(3)                 });     } } 

then i'd have function call,

private ienumerable<student> getstudents() {     var resultlookup = getmarks().tolookup(t => t.item1, t => t.item2);      ... setup student command here     var reader = studentcommand.executereader();     while (reader.read())     {         var studentid = reader.getint32(0);         yield return new student                 {                     studentid,                     reader.getstring(1),                     resultlookup[studentid].tolist()                 });     } } 

---

if wanted, in 1 stored procedure , return multiple result sets.