Count between range of date bind in gridview. LINQ to SQL Visual Basic

Question

Hello, may someone could help me on one problem in query LINQ to SQL Visual Basic.

My database has three tables,

* Students(StudentID, StudentName),
* Class(ClassID, ClassDate),
* Attendance(StudentID, ClassID) which Attendance tables is the junction table between Students and Class.

For example:

Students

StudentsID.............StudentName

1001......................Smith

1002......................John

1003......................Bob

Class

ClassID............ClassDate

1......................1/1/2009

2......................1/3/2009

3 .....................1/7/2009

Attendance

StudentID............ClassID

1001 ...................1

1002 ...................1

1003 ...................1

1001 ...................2

1003 ...................2

1001 ...................3

1002 ...................3

1003 ...................3

I have try the query Visual Basic LINQ to SQL below to list all the student and the count number of attendance in a range of date.

1 Dim db = New AttendanceControllerDataContext
2
3 Dim result = From std In db.Students _
4 Select std.StudentID, _
5 std.StudentName, _
6 Attendance = std.Attendances.Count()
7
8 GridView1.DataSource = result
9 GridView1.DataBind()

When I try this, it gives me:

StudentID........StudentName.....Attendance

1001...............Smith.....................3

1002...............John......................2

1003...............Bob.......................3

In line 6, Attendance I want to count according to the range of date in the Class Table, For example if I selected a range of date between 1/2/2009 and 1/7/2009, then I have tried the following code:

 1   Dim db = AttendanceControllerDataContext

 2   Dim result = From std In db.Students, atd In db.Attendances _
 3                Where atd.studentID = std.studentID _
 4                Select std.studentID, _
 5                std.studentName, _
 6                Attendance = atd.Class.date >= 1/2/2009 _
 7                And atd.Class.date <= 1/7/2009  std.Attendances.Count()
 8   GridView1.DataSource = result
 9   GridView1.DataBind()

The result is:

StudentID........StudentName.....Attendance

1001...............Smith.....................2

1001...............Smith.....................2

1002...............John......................1

1003...............Bob.......................2

1003...............Bob.......................2

However, I just wanted a list that look like below only:

StudentID........StudentName.....Attendance

1001...............Smith.....................2

1002...............John......................1

1003...............Bob.......................2

How can I do this? Any suggestion? Thank in advance..

Answer 1

Instead of

Attendance = std.Attendances.Count()

in your first code sample use

Attendance = std.Attendances.Where(Function(att) att.Class.ClassDate >= "1/2/2009" And att.Class.ClassDate <= "1/7/2009").Count()