Should we use table functions instead of LINQ to SQL in this case?

Question

SQL

Hello!
In your project, you often have to work with sql queries.
At the moment I'm using LINQ to SQL to get the data I need from the database, but the code structure leaves a lot to be desired. Below is a fragment of one of these requests.

var query = (from table1 in (
                                (from Students in db.Students
                                 group Students by new
                                 {
                                     Students.GroupNumber
                                 } into g
                                 select new
                                 {
                                     Grp = g.Key.GroupNumber,
                                     Kol_dolgnikov = g.Count(p => p.RecordBookNumber != null)
                                 }))
                             join table2 in (
                                 (from t1 in (
                                     (from Students in db.Students
                                      from StudentsSubjects in db.StudentsSubjects
                                      where
                                      Students.RecordBookNumber == StudentsSubjects.StId.ToString()
                                      group new { Students, StudentsSubjects } by new
                                      {
                                          Students.RecordBookNumber,
                                          Students.GroupNumber
                                      } into g
                                      where g.Count(p => p.StudentsSubjects.SubjId != null) > 2
                                      select new
                                      {
                                          Grp = g.Key.GroupNumber,
                                          Rbn = g.Key.RecordBookNumber,
                                          Kol_dolg = g.Count(p => p.StudentsSubjects.SubjId != null)
                                      }))
                                  group t1 by new
                                  {
                                      t1.Grp
                                  } into g
                                  select new
                                  {
                                      g.Key.Grp,
                                      Kol_dol_bol2 = g.Count(p => p.Rbn != null)
                                  })) on table1.Grp equals table2.Grp into table2_join
                             from table2 in table2_join.DefaultIfEmpty()
                             select new DebtorsReportModel
                             {
                                 StudentGroupNumber = table1.Grp,
                                 SoftDebtorsCount = (int?)table1.Kol_dolgnikov ?? (int?)0,
                                 HardDebtorsCount = (int?)table2.Kol_dol_bol2 ?? (int?)0
                             }).ToList();

In addition, there are some pretty good ones in my opinion.

var query = (from Students in db.Students
                             from StudentsSubjects in db.StudentsSubjects
                             from Subjects in db.Subjects
                             from ControlPeriods in db.ControlPeriods
                             from ControlTypes in db.ControlTypes

                             where
                               Students.RecordBookNumber == StudentsSubjects.StId.ToString()
                             where
                             Subjects.SubjectId == StudentsSubjects.SubjId
                             where
                             ControlPeriods.ControlPeriodId == StudentsSubjects.CtrlPeriodId
                             where
                             ControlTypes.ControlTypeId == StudentsSubjects.CtrlTypeId
                             select new SubjectsReportModel
                             {
                                 StudentGroupNumber = Students.GroupNumber,
                                 StudentFullName = string.Concat(Students.LastName, " ", Students.FirstName, " ", Students.MiddleName),
                                 SubjectName = Subjects.Name,
                                 ControlPeriodName = ControlPeriods.Name,
                                 ControlTypeName = ControlTypes.Name,
                                 Date = StudentsSubjects.FileDate
                             }).ToList();

Is it worth replacing huge incomprehensible pieces of code with table functions, or is it better to leave everything in its place?

Answer 1

As already noted, linq2sql in this case is like a guinea pig (not in the sea and does not grunt).
And most importantly, it arrives in sql in such a way that the scheduler / optimizer is in difficulty.
As a result - loss of productivity.
Therefore, the correct way is to build stored procedures and linq-operating with the results of the procedures already selected in an optimal way.
This may not seem like a book and not according to Feng Shui theoretical fabrications, but it is effective.

Answer 2

From experience - sql notation in linq is a big evil. Scary, incomprehensible, not a code and not a sql query.
This is prohibited by many coding rules.
Plus, I would generally rewrite the first request to storage in the case of more than 100k records in the database.