想不到能解决很多问题的查询 [英] Can't think of query that solves this many to many
问题描述
我遇到了这种复杂的情况。
使用模型优先创建EF，这是我的EF实体：
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
-------------------
现在 -LECTURERS 和 -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?):
公共课程CourseView
{
public int CourseID {get;组; }
公共字符串CourseName {get;组; }
公共字符串ProfessorName {get;组; }
公共列表AssistantNames {get;组; } // 或者应该是助手列表
}
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.
推荐答案
您可能想重做EF实体。现在，您说的是每门课程都有多位讲师，讲师是教授或助理。但是您的课程索引视图似乎表明每门课程都应该有一位教授和多位助手。您还表示讲师只能是教授或助理。我首先要确保一个课程的教授不能成为另一个课程的助手。如果是这种情况，那么我将为教授和助手创建一个单独的表（或实体）。使每门课程有一名教授和多名助手。另一方面，如果教授可以担任助理，那么您需要从讲师中删除教授的职务，并通过让每门课程都有一名讲师（即教授）和一组讲师作为助理来将其与课程建立联系。
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()
};
这篇关于想不到能解决很多问题的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
