0

this my sql query that i want to convert into linq

  SELECT 
  s.Studentname ,
  COUNT(c.StudentId) as Counts
  FROM  Students s,  StudentCourses c

  where s.StudentId=c.StudentId

  group by s.Studentname

 GO

here is what i have tried i specificaly wanted to store data into StudentDto Model

        var students = (from s in DbContext.students
                                join sc in DbContext.studentCourses on s.StudentId equals 
                                sc.StudentId
                                group s by s.Studentname into grouped
                                select new StudentDto()
                                {
                                    Studentname = s.StudentName,
                                    StudentEmail = s.StudentEmail,
                                    PhoneNumber = s.PhoneNumber,
                                    DateOfBirth = s.DateOfBirth,
                                    Password = s.Password,
                                    ConfirmPawword = s.ConfirmPawword,
                                    CourseCount = sc.count()
                                }
                                ).ToList();
Hamza Aslam
  • 1
  • 1
  • 1
    What is your question? From the text above I can't conclude what is the issue you want to resolve. – Zoran Stankovic Oct 05 '20 at 07:15
  • Tip of today: Switch to modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read an maintain, and easier to convert to outer join if needed! – jarlh Oct 05 '20 at 07:22
  • When you say you want to 'store to model', it sounds like you want to store the student and studentCourse data to the database. But since you've just queried the database to get this data - so the data is already in the database. If, however, you want to display the data, the StudentDto class is a good for that, because you can combine information from both tables into it. In this case, you should think of StudentDto as a class for display, in that there is no StudentDto table that you store to. – trajekolus Oct 05 '20 at 09:22
  • 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. PS NOT `sc.Count()` but rather `grouped.Count()`. – NetMage Oct 05 '20 at 21:58

0 Answers0