Can't think of query that solves this many to

问题:

I've got this complex situation.
Created EF with "Model first" and here are my EF entities:

-Course Lecturer many to many relationship-

  Course               Lecturer  
------------      -------------------           Column IS_PROFESOR is bool      
  +ID                 +ID                       value that makes Lecturer:
  +NAME               +FIRSTNAME                true: Course Profesor 
-----------           +LASTNAME                 false: Course Asistent
-LECTURERS            +IS_PROFESOR
-----------       -------------------
                      -COURSES                  
                  -------------------  

Now -LECTURERS and -COURSES are navigation properties. From this tables in database i need to query data and put them in some Model so i can get following Course Index View

---------------------------------------------------------------------------------
    Course     |   Professor      |    Assistants     |          Actions
---------------------------------------------------------------------------------
    course 1   | course professor |assistant 1      |     edit update delete
               |                  |assistant 2      |     edit update delete
---------------------------------------------------------------------------------
    course 2   | course professor |assistant 1      |     edit update delete
---------------------------------------------------------------------------------
    course 3   | course professor |assistant 1      |     edit update delete
               |                  |assistant 2      |     edit update delete
               |                  |assistant 3      |     edit update delete
---------------------------------------------------------------------------------

As you can see i need query that will populate some new View Model in that way that it can distinct Course Professor from Course Assistants list. Model will be (right?):

public class CourseView
{
public int CourseID { get; set; } public string CourseName { get; set; }
public string ProfessorName { get; set; }
public List AssistantNames { get; set; } // Or it should be List of Assistants
}

No way i can think of some rational solution for this problem with my own skills, any help, reference or suggestion will mean a lot for me,thx for every second of looking at this.

回答1:

You might want to redo your EF entities. Right now you are saying that each course has multiple lecturers and a lecturer is either a professor or an assistant. But your course index view seems to indicate that each course should have one professor and multiple assistants. You also are indicating that a lecturer can only be a professor or an assistant. I would first make sure that a professor of one course cannot be the assistant of another first. If that is the case then I would create a separate table (or entity) for professors and assistants. Make each course have one professor and multiple assistants. On the other hand if professors can be assistants then you need to remove the professor designation from the lecturer and build it into the relationship to the courses by having each course have one lecturer that is the professor and a list of lecturers that are the assistants.

However if you want to continue with your current setup you should be able to populate your desired view with something like this

var courseViews = from c in db.Courses
                  select new CourseView()
                  {
                      CourseID = c.ID,
                      ProfessorName = (from l in c.Leturers 
                                       where l.Is_Professor 
                                       select l.Name).FirstOrDefault(),
                      AssistantNames = (from l in c.Leturers 
                                        where !l.Is_Professor 
                                        select l.Name).ToList()
                  };