-4

I am converting an asp.net app to .net core 6. An SQL query needs to be converted into LINQ for Entity Framework. I am new to LINQ.

Here are the tables:

Registration:

  regID,
  userID,
  courseID,
  numOfClass,     (num of class/hours registered) 
(A student, userID, could register multiple different courses)

TeacherStudent:

  tsID,
  regID,
(The registered student is assigned to an instructor)

Schedule:

  scheduleID,
  tsID,
  startDT,        (a class's starting date/time)
  endDT,          (a class's ending date/time)
  statusID,       (a class's status: 2200 for newly scheduled classes, 2210 for done classes, 2260 for student absent classes)
(The instructor schedules the classes into this table for this student. For each registered course, up to numOfClass classes can be scheduled. For example, if a student registered a course for 30 classes/hours, up to 30 records can be created in the Schedule table for this registered course.)

The desired query is: For a given student (userId), gets the statistics data for each course this student has taken so far in the following format:

courseId, course start date/time, course end date/time (for ongoing course, it is the latest class date/time), number of classes registered, number of classes scheduled, number of classes done, number of classes student is absent

In SQL Server, the statistics data can be returned by this query:

SELECT 
    R.regID, R.courseID, 
    MIN(S.startDT) AS startDT, 
    MAX(S.endDT) AS endDT, 
    AVG(R.numOfClass) AS allCourseHours, 
    SUM(CASE WHEN S.statusID = '2200' THEN 1 ELSE 0 END) AS newCourseHours, 
    SUM(CASE WHEN S.statusID = '2210' THEN 1 ELSE 0 END) AS doneCourseHours, 
    SUM(CASE WHEN S.statusID = '2260' THEN 1 ELSE 0 END) AS studentAbsentCourseHours
FROM 
    Registration AS R 
LEFT OUTER JOIN 
    TeacherStudent AS T ON R.regID = T.regID 
LEFT OUTER JOIN 
    Schedule AS S ON S.tsID = T.tsID
WHERE 
    R.UserId = 100
GROUP BY 
    R.regId, R.courseId

Now, in Entity Framework, how can we get the same statistics data with a Linq query? Thanks!

VBird
  • 1
  • 3
  • 1
    What you have tried so far? Query is simple, just google LINQ GroupBy Min Max. – Svyatoslav Danyliv Oct 20 '22 at 04:45
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Oct 20 '22 at 15:09

1 Answers1

0

Please try Linqer http://www.sqltolinq.com (an SQL to LINQ converter) or try LINQPad http://www.linqpad.net/

skoley
  • 311
  • 2
  • 7