SQL -> LINQ (C#)

Question

I have an SQL query (below) that essentially takes a student from tbStudents, and then gets the most recent term (a number, but stored as a string) in tbTerms.

There is a one-to-many relationship with a student in tbStudents to a term record in tbTerms. Example:

tbStudents:

StudentId FirstName LastName 12345 John Smith 12346 Jane Doe

tbTerms:

StudentId Term 12345 1234 12345 1235 12345 1236 12346 1233 12346 1234

Desired:

StudentId FirstName LastName Term 12345 John Smith 1236 12346 Jane Doe 1234

SQL Query:

select tbStudents.student_id, tbStudents.user_id, tbStudents.firstname, tbStudents.lastname, v.rTerm from tbStudents inner join ( select tbTerms.student_id, MAX(tbTerms.term) as rTerm from tbTerms group by tbTerms.student_id ) v on v.student_id = tbStudents.student_id

I've been trying to get this all into one LINQ statement, but I'm having trouble. Is there anyway to do this in one statement? Or must it be done in multiple statements. Thanks in advanced.

Edit: C# code of what I have tried.

var students = (from s in dockDb.tbStudents join t in dockDb.tbTerms on s.student_id equals t.student_id into pairs from p in pairs select new { UserId = s.user_id, StudentId = s.student_id, Term = p.term } ).ToList();

Output is similar to:

StudentId FirstName LastName Term 12345 John Smith 1234 12345 John Smith 1235 12345 John Smith 1236 12346 Jane Doe 1233 12346 Jane Doe 1234

Edit #2: I'm using Entity Framework for data. I'm not sure if this affects anything, but most of the solutions are 'Syntactically incorrect' when I attempt them.

"syntatically incorrect". Call the extension AsEnumerable(). Then they will not be "syntatically incorrect". — ΩmegaMan
– ΩmegaMan, Commented Mar 29, 2012 at 2:32

MarcinJuraszek · Accepted Answer · 2012-03-28 20:37:21Z

You should try something like that:

from s in dockDb.tbStudents join t in dockDb.tbTerms on t.student_id equals s.student_id into st group st by New {StudentID = st.student_id, Term = t.term} into g select New { g.Key, g.Max(i => i.Term) }

KeithS · Accepted Answer · 2012-03-28 20:41:35Z

Should be possible. Try this:

from s in Students join t in Terms on t.StudentId = s.StudentId select new {Student = s, Term = t} group by s into g select new {g.Key.StudentId, g.Key.FirstName, g.Key.LastName, g.Max(x=>x.Term.term)};

Breaking it down, the first two lines define the domain in which to search; namely, the inner-joined combination of Students and Terms, linked by StudentId. Each unique combination of Student and Term are put into an anonymous type, and then those instances are grouped by each unique Student into a Lookup (a read-only collection of IEnumerables referenced by key). The, we can select the key's (Student's) vital info, and in the list of terms, find the one with the highest value (which will be the most recent).

I had to modify some things in your code to make it run, but otherwise your structure was pretty much what I needed. Thanks!

Nikola Anusev · Accepted Answer · 2012-03-28 20:41:08Z

Somehow, I've always considered syntax with extension methods to be more readable and natural to write. So, here goes:

context.Students.Select(s => new { s.StudentId, s.FirstName, s.LastName, Term = context.Terms.Where(t => t.StudentId == s.StudentId) .Select(t => t.TermNumber) .OrderByDescending(t => t) .First() });

Basically, for each student, it selects all his/her terms, orders them from most recent to least recent and includes first one (the most recent) in result.

ΩmegaMan · Accepted Answer · 2012-03-28 20:56:23Z

Groupby is your friend. Let us simply group the data by student id, then project it to a new form. This is a linqpad example:

void Main() { var students = new List<Student>() { new Student() { StudentID = 12345, Name = "Smith" }, new Student() { StudentID = 12346, Name = "Jones" } }; var terms = new List<Term>() { new Term() { StudentID=12345, TermID = 1234 }, new Term() { StudentID=12345, TermID = 1235 }, new Term() { StudentID=12345, TermID = 1236 }, new Term() { StudentID=12345, TermID = 1237 }, new Term() { StudentID=12346, TermID = 1233 }, new Term() { StudentID=12346, TermID = 1234 }, }; var result = terms.GroupBy (tm => tm.StudentID) .Select (gr => new { Name = students.First (s => s.StudentID == gr.Key).Name, Terms = gr.Select (g => g.TermID) }); result.Dump(); } // Define other methods and classes here public class Student { public int StudentID { get; set; } public string Name { get; set; } } public class Term { public int StudentID { get; set; } public int TermID { get; set; } }

Here is the output which can be used:

enter image description here

Collectives™ on Stack Overflow

SQL -> LINQ (C#)

4 Answers 4

Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Related