using LINQTest.Common;
using LINQTest.HR;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LINQTest
{
class Program
{
static void Main(string[] args)
{
List<Course> courses = Course.GetCourseList();
List<Student> students = Student.GetStudentList();
List<ClassInstructor> mappings = ClassInstructor.GetMappingList();
// All details
var studentCourses = mappings.
Join(students,
m => m.StudentID,
s => s.ID,
(m, s) => new { id = s.ID, name = s.Name, m.CourseID })
.Join(courses,
m => m.CourseID,
c => c.ID,
(m, c) => new { id = m.id, name = m.name, c.Name });
foreach (var item in studentCourses)
{
Console.WriteLine(item);
}
Console.WriteLine();
// How many people chosen of each course.
var eachCourseCount = courses.
GroupJoin(mappings,
c => c.ID,
m => m.CourseID,
(c, ms) => new { id = c.ID, name = c.Name, student_count = ms.Count()});
foreach (var item in eachCourseCount)
{
Console.WriteLine(item);
}
Console.WriteLine();
// How many courses chosen by each student.
var eachStudentCount = students.
GroupJoin(mappings,
s => s.ID,
m => m.StudentID,
(s, ms) => new { id = s.ID, name = s.Name, course_count = ms.Count()});
foreach (var item in eachStudentCount)
{
Console.WriteLine(item);
}
Console.WriteLine();
// Average score of each student
var scores = students.GroupJoin(mappings,
s => s.ID,
m => m.StudentID,
(s, ms) => new { id = s.ID, name = s.Name, average = ms.Average(m => m.Score) });
foreach (var item in scores)
{
Console.WriteLine(item);
}
Console.ReadKey();
}
}
public class Course
{
public string ID;
public string Name;
public static List<Course> GetCourseList()
{
List<Course> courseList = new List<Course>();
courseList.Add(new Course() { ID = "0001", Name = "Calculus" });
courseList.Add(new Course() { ID = "0002", Name = "Chemistry" });
courseList.Add(new Course() { ID = "0003", Name = "Physics" });
courseList.Add(new Course() { ID = "0004", Name = "Composition" });
courseList.Add(new Course() { ID = "0005", Name = "Poetry" });
courseList.Add(new Course() { ID = "0006", Name = "Literature" });
courseList.Add(new Course() { ID = "0007", Name = "Trigonometry" });
courseList.Add(new Course() { ID = "0008", Name = "Microeconomics" });
return courseList;
}
}
public class Student
{
public string ID;
public string Name;
public int Age;
public static List<Student> GetStudentList()
{
List<Student> studentList = new List<Student>();
studentList.Add(new Student() { ID = "03040001", Name = "Santa Claus", Age = 1743 });
studentList.Add(new Student() { ID = "03040002", Name = "Macheal Jaskson", Age = 55 });
studentList.Add(new Student() { ID = "03040003", Name = "John Lenon", Age = 73 });
studentList.Add(new Student() { ID = "03040004", Name = "Kurt Cobain", Age = 46 });
return studentList;
}
}
public class ClassInstructor
{
public string CourseID;
public string StudentID;
public int Score;
public static List<ClassInstructor> GetMappingList()
{
List<ClassInstructor> mappingList = new List<ClassInstructor>();
mappingList.Add(new ClassInstructor() { CourseID = "0001", StudentID = "03040001", Score = 96 });
mappingList.Add(new ClassInstructor() { CourseID = "0004", StudentID = "03040001", Score = 86 });
mappingList.Add(new ClassInstructor() { CourseID = "0005", StudentID = "03040001", Score = 67 });
mappingList.Add(new ClassInstructor() { CourseID = "0008", StudentID = "03040001", Score = 78 });
mappingList.Add(new ClassInstructor() { CourseID = "0007", StudentID = "03040001", Score = 82 });
mappingList.Add(new ClassInstructor() { CourseID = "0002", StudentID = "03040002", Score = 59 });
mappingList.Add(new ClassInstructor() { CourseID = "0003", StudentID = "03040002", Score = 58 });
mappingList.Add(new ClassInstructor() { CourseID = "0004", StudentID = "03040002", Score = 61 });
mappingList.Add(new ClassInstructor() { CourseID = "0005", StudentID = "03040002", Score = 61 });
mappingList.Add(new ClassInstructor() { CourseID = "0006", StudentID = "03040002", Score = 66 });
mappingList.Add(new ClassInstructor() { CourseID = "0001", StudentID = "03040003", Score = 78 });
mappingList.Add(new ClassInstructor() { CourseID = "0002", StudentID = "03040003", Score = 67 });
mappingList.Add(new ClassInstructor() { CourseID = "0003", StudentID = "03040003", Score = 55 });
mappingList.Add(new ClassInstructor() { CourseID = "0004", StudentID = "03040003", Score = 99 });
mappingList.Add(new ClassInstructor() { CourseID = "0005", StudentID = "03040003", Score = 67 });
mappingList.Add(new ClassInstructor() { CourseID = "0001", StudentID = "03040004", Score = 45 });
mappingList.Add(new ClassInstructor() { CourseID = "0002", StudentID = "03040004", Score = 99 });
mappingList.Add(new ClassInstructor() { CourseID = "0003", StudentID = "03040004", Score = 99 });
mappingList.Add(new ClassInstructor() { CourseID = "0004", StudentID = "03040004", Score = 100 });
mappingList.Add(new ClassInstructor() { CourseID = "0005", StudentID = "03040004", Score = 100 });
mappingList.Add(new ClassInstructor() { CourseID = "0006", StudentID = "03040004", Score = 67 });
mappingList.Add(new ClassInstructor() { CourseID = "0007", StudentID = "03040004", Score = 100 });
return mappingList;
}
}
}
Left join:
var result = from a in claims
join b in treeTaskDetails on a.ID equals b.ID into gj
from sub in gj.DefaultIfEmpty()
select new ResultModel
{
ReviewID = a.ReviewID,
ID = a.ID,
Number = a.Number,
CheckTime = sub.LastUpdated
}