A Student can be in one or more Classes
A Class can have one or more Students
A Teacher may teach one or more Classes
A Class may be taught by one or more Teachers
The relations are represented by the following Join tables:
The problem: I would like that when a Teacher logs in to update the StudentAttendanceRegister the Class filter must have only the classes she teaches and the list of students must belong to the selected class.
Create PROC StudentAttendanceRegister
(
@TeacherId INT
)
As
BEGIN
SELECT S.StudentName, C.ClassName
FROM Student AS S
JOIN Class AS C On C.ClassId = S.ClassId
JOIN Teacher AS T ON T.ClassId = C.ClassId
Where T.TeacherId = @TeacherId
END
I have written sproc for above requirement. You can use that and call that sproc in Entity framework.
Hi Martin I just reply to your e-mail :)
the easiest way to do this would be to use Domain Service (this has some drawbacks such as no support for Many to Many relationships) however I normally create a special custom filter and populate it myself this big issue here is that you need to supply a list
of all Id’s for the ALL filter entry.
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
CREATE VIEW [dbo].[TeachersStudents]
AS
SELECT T.teacherId, TC.classId, S.studentId, S.studentFirstName, S.studentLastName,
C.className, CS.classstudentAcademicYear, T.teacherFirstName,
T.teacherLastName
FROM dbo.Teachers AS T INNER JOIN
dbo.TeachersClasses AS TC ON T.teacherId = TC.teacherId LEFT OUTER JOIN
dbo.ClassesStudents AS CS ON TC.classId = CS.classId LEFT OUTER JOIN
dbo.Classes AS C ON TC.classId = C.classId LEFT OUTER JOIN
dbo.Students AS S ON CS.studentId = S.studentId
TeachersClasses -> TeachersStudents by teacherId and classId
[MetadataType(typeof(TeachersStudents_MD))]
public partial class TeachersStudents
{
public class TeachersStudents_MD
{
[FilterUIHint("Cascade")]
[nacDA.Cascade("Teachers")]
public object TeachersClasses { get; set; }
}
}
[MetadataType(typeof(TeachersClasses_MD))]
public partial class TeachersClasses
{
public string classCall
{
get
{
return this.Classes.className;
}
}
[DisplayColumn("classCall")]
public class TeachersClasses_MD
{
}
}
Member
18 Points
187 Posts
Filter Problem
May 28, 2013 02:21 AM|mr41971|LINK
In my Dynamic Data Entity Framework project I have the following entities:
Student: ID, FirstName, LastName
Class: ID, ClassName
Teacher: ID, FirstName, LastName
They have the following relationships:
A Student can be in one or more Classes
A Class can have one or more Students
A Teacher may teach one or more Classes
A Class may be taught by one or more Teachers
The relations are represented by the following Join tables:
ClassStudent: ID, ClassID, StudentID, AcademicYear
TeacherClass: ID, TeacherID, ClassID
The problem: I would like that when a Teacher logs in to update the StudentAttendanceRegister the Class filter must have only the classes she teaches and the list of students must belong to the selected class.
StudentAttendanceRegister: ID, Date, StudentID, Present
Please help been at this for about a month.
Member
270 Points
88 Posts
Re: Filter Problem
May 28, 2013 09:04 AM|Thulasiram|LINK
I have written sproc for above requirement. You can use that and call that sproc in Entity framework.
Member
18 Points
187 Posts
Re: Filter Problem
May 28, 2013 04:45 PM|mr41971|LINK
Thank you very much. I am relatively new to Dynamic Data so please explain how would I use your suggestion to return an ObjectContext e.g.:
return this.ObjectContext.StudentAttendances.Include("Student").OrderBy(s => s.ID);All-Star
17916 Points
5681 Posts
MVP
Re: Filter Problem
May 29, 2013 10:33 AM|sjnaughton|LINK
Hi Martin I just reply to your e-mail :) the easiest way to do this would be to use Domain Service (this has some drawbacks such as no support for Many to Many relationships) however I normally create a special custom filter and populate it myself this big issue here is that you need to supply a list of all Id’s for the ALL filter entry.
Always seeking an elegant solution.
Member
71 Points
48 Posts
Re: Filter Problem
May 31, 2013 05:31 AM|valZ|LINK
CREATE VIEW [dbo].[TeachersStudents] AS SELECT T.teacherId, TC.classId, S.studentId, S.studentFirstName, S.studentLastName, C.className, CS.classstudentAcademicYear, T.teacherFirstName, T.teacherLastName FROM dbo.Teachers AS T INNER JOIN dbo.TeachersClasses AS TC ON T.teacherId = TC.teacherId LEFT OUTER JOIN dbo.ClassesStudents AS CS ON TC.classId = CS.classId LEFT OUTER JOIN dbo.Classes AS C ON TC.classId = C.classId LEFT OUTER JOIN dbo.Students AS S ON CS.studentId = S.studentIdTeachersClasses -> TeachersStudents by teacherId and classId
[MetadataType(typeof(TeachersStudents_MD))] public partial class TeachersStudents { public class TeachersStudents_MD { [FilterUIHint("Cascade")] [nacDA.Cascade("Teachers")] public object TeachersClasses { get; set; } } } [MetadataType(typeof(TeachersClasses_MD))] public partial class TeachersClasses { public string classCall { get { return this.Classes.className; } } [DisplayColumn("classCall")] public class TeachersClasses_MD { } }If I understand correctly.
All-Star
17916 Points
5681 Posts
MVP
Re: Filter Problem
May 31, 2013 05:56 AM|sjnaughton|LINK
Are you using a DomainService in your DD application?
Always seeking an elegant solution.
Member
18 Points
187 Posts
Re: Filter Problem
May 31, 2013 03:49 PM|mr41971|LINK
Yes I am steve.
All-Star
17916 Points
5681 Posts
MVP
Re: Filter Problem
Jun 01, 2013 07:08 AM|sjnaughton|LINK
OK so the Linq code would be something like this
return this.ObjectContext.StudentAttendance.Where(s => s. ClassStudent. TeacherClass.TeacherId == TeacherId);
that may not be exactly what you would do but without the actual model (and the help of intelisense) I can’t more exact.
Always seeking an elegant solution.