Стоит ли в данном случае использовать табличные функции вместо LINQ to SQL?

176

07 августа 2018, 23:50

В своем проекте часто приходится работать с sql-запросами. На данный момент я использую LINQ to SQL, чтобы получить необходимые данные из БД, но структура кода оставляет желать лучшего. Ниже приведен фрагмент одного из таких запрсоов.

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();

Кроме того, есть и довольно неплохие на мой взгляд.

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();

Стоит ли заменить огромные непонятные куски кода на табличные функции или лучше оставить всё на своих местах?

Пример sql-функции, которая используется в проекте.

CREATE FUNCTION [dbo].[GetCoursesReport]
(
    @startDate nvarchar(12),
    @endDate nvarchar(12)
)
RETURNS @returntable TABLE
(
    Course int,
    StartDateDebtors int,
    EndDateDebtors int
)
AS
BEGIN
INSERT @returntable
select table1.Course as COURSE, ISNULL(table1.Dolgnik,0) as DOLGNIKI1, ISNULL(table2.Dolgnik,0) as DOLGNIKI2
from
(select t1.Course, count(t1.Course) as Dolgnik
from 
(select  substring(convert(nvarchar(5), StudentsSubjects.GrpNum),1,1) as Course
from StudentsSubjects, Students
where StudentsSubjects.FileDate = @startDate and Students.RecordBookNumber = StudentsSubjects.StId
group by StudentsSubjects.GrpNum) as t1
group by t1.Course) as table1
left outer join
(select t1.Course, count(t1.Course) as Dolgnik
from 
(select  substring(convert(nvarchar(5), StudentsSubjects.GrpNum),1,1) as Course
from StudentsSubjects, Students
where StudentsSubjects.FileDate = @endDate and Students.RecordBookNumber = StudentsSubjects.StId
group by StudentsSubjects.GrpNum) as t1
group by t1.Course) as table2
on table1.Course = table2.Course
order by 1
RETURN
END

Answer 1

Судя по вашим запросам, вы используете LINQ как способ сгенерировать нужный вам SQL. Возможно, даже сначала пишите SQL, а потом пытаетесь натянуть его на LINQ...

Конечно же в таком режиме никакой LINQ не нужен, и хранимки - лучшее решение.

Но LINQ можно использовать и по-другому!

Для начала, нужно начать пользоваться навигационными свойствами. Вместо вот таких join "старого образца"

// плохо
from Students in db.Students
from StudentsSubjects in db.StudentsSubjects
where Students.RecordBookNumber == StudentsSubjects.StId.ToString()

нужно использовать навигационные свойства:

// хорошо
from student in db.Students
from studentSubjects in student.Subjects

Далее, частенько можно забыть про left outer join и использовать вместо него вложенные запросы.

К примеру, если написать вот так:

from student in db.Students
let dolgs = student.Subjects.Count(s => s.SubjId != null)

то сразу же становится не нужной группировка по RecordBookNumber...

И вообще, весь ваш первый запрос можно сократить до чего-то вроде вот такого:

from student in db.Students
group student by student.Grp into g
select new DebtorsReportModel
{
  StudentGroupNumber = g.Key,
  SoftDebtorsCount = g.Count(s => s.RecordBookNumber != null),
  HardDebtorsCount = g.Count(s => s.Subjects.Count(x => x.SubjId != null) > 2),
}

Кстати, RecordBookNumber и SubjId подозрительно похожи на ключевые колонки. Откуда в них вообще NULL?

from student in db.Students
group student by student.Grp into g
select new DebtorsReportModel
{
  StudentGroupNumber = g.Key,
  SoftDebtorsCount = g.Count(),
  HardDebtorsCount = g.Count(s => s.Subjects.Count() > 2),
}