sql-server - SQL Server LINQ selectman 和 orderby 超时

问题描述

在 SQL Server 2017 Express 中，我有两个级别的主/详细信息关系表，如下所示

CREATE TABLE ADHERANTS(
    [ID_ADHERANT] int IDENTITY(1,1) NOT NULL primary key,
    [ADH_NOM] [varchar](50) NULL,
    [ADH_PRENOM [varchar](50) NULL,
)

CREATE TABLE  INSCRIPTIONS(
    [ID_INSCRIPTION] [int] IDENTITY(1,1) NOT NULL primary key,
    [INS_ID_ADHERANT] [int] NOT NULL /* foreign key for ADHERANTS*/,
    [INS_DATE_DEBUT] [date] NULL,
    [INS_DUREE] [int] NULL,
    [INS_DATE] [date] NULL
);

CREATE TABLE SEANCES(
    [ID_SEANCE] [int] IDENTITY(1,1) NOT NULL primary key,
    [INS_ID_INSCRIPTION] [int] NULL /* foreign key for INSCRIPTIONS*/,
    [SEA_DEBUT] [datetime2](7) NULL,
    [SEA_FIN] [datetime2](7) NULL,
    [SEA_MANUAL] [int] NULL,
    [SEA_MONTANT] [decimal](14, 2) NULL,
    [SEA_NOM] [varchar](50) NULL,
 );

请注意，外键 inSEANCEs可以为空。

我想要的是ADHERANTS根据日期为每个最后的详细记录获取。

在 EF Core 3.1 LINQ 我有这样的事情：

   var res = (from adh in _context.ADHERANTS
                        
                       .Include(x => x.Inscriptions)
                       .ThenInclude(x => x.Seances)
                       select new  
                       {

                           ID_ADHERANT = adh.ID_ADHERANT,
                           ADH_NOM = adh.ADH_NOM,
                           ADH_PRENOM = adh.ADH_PRENOM,
                          

                           LastInscription = (from nn in adh.Inscriptions.OrderByDescending(a => a.INS_DATE_DEBUT)

                                              select new LastInscription()
                                              {
                                                  Date = nn.INS_DATE_DEBUT,
                                                  Duree = nn.INS_DUREE.Value
                                              }).FirstOrDefault(),
                           
                           LastSeance = (from mm in adh.Inscriptions.SelectMany(a => a.Seances).OrderByDescending(a => a.SEA_DEBUT)
                                         select mm.SEA_DEBUT).FirstOrDefault()

                         });

  IncrementalItemsSource.LoadItems(res.Skip(baseIndex).Take(10));

哪个有效并产生此 SQL 查询：

exec sp_executesql N'SELECT [a].[ID_ADHERANT], [a].[ADH_NOM], [a].[ADH_PRENOM], [t0].[INS_DATE_DEBUT], [t0].[INS_DUREE], [t0].[c], (
    SELECT TOP(1) [s].[SEA_DEBUT]
    FROM [INSCRIPTIONS] AS [i]
    INNER JOIN [SEANCES] AS [s] ON [i].[ID_INSCRIPTION] = [s].[INS_ID_INSCRIPTION]
    WHERE [a].[ID_ADHERANT] = [i].[INS_ID_ADHERANT]
    ORDER BY [s].[SEA_DEBUT] DESC)
FROM [ADHERANTS] AS [a]
LEFT JOIN (
    SELECT [t].[INS_DATE_DEBUT], [t].[INS_DUREE], [t].[c], [t].[ID_INSCRIPTION], [t].[INS_ID_ADHERANT]
    FROM (
        SELECT [i0].[INS_DATE_DEBUT], [i0].[INS_DUREE], 1 AS [c], [i0].[ID_INSCRIPTION], [i0].[INS_ID_ADHERANT], ROW_NUMBER() OVER(PARTITION BY [i0].[INS_ID_ADHERANT] ORDER BY [i0].[INS_DATE_DEBUT] DESC) AS [row]
        FROM [INSCRIPTIONS] AS [i0]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [a].[ID_ADHERANT] = [t0].[INS_ID_ADHERANT]
ORDER BY (SELECT 1) desc
OFFSET @__p_0 ROWS FETCH NEXT @__p_0 ROWS ONLY',N'@__p_0 int',@__p_0=10

但是，如果我尝试在此orderby之上添加一个，则会出现超时异常：

   var res = (from adh in _context.ADHERANTS
                        
                       .Include(x => x.Inscriptions)
                       .ThenInclude(x => x.Seances)
                       select new  
                       {

                           ID_ADHERANT = adh.ID_ADHERANT,
                           ADH_NOM = adh.ADH_NOM,
                           ADH_PRENOM = adh.ADH_PRENOM,
                          

                           LastInscription = (from nn in adh.Inscriptions.OrderByDescending(a => a.INS_DATE_DEBUT)

                                              select new LastInscription()
                                              {
                                                  Date = nn.INS_DATE_DEBUT,
                                                  Duree = nn.INS_DUREE.Value
                                              }).FirstOrDefault(),
                           
                           LastSeance = (from mm in adh.Inscriptions.SelectMany(a => a.Seances).OrderByDescending(a => a.SEA_DEBUT)
                                         select mm.SEA_DEBUT).FirstOrDefault()

                         });
  res = res.OrderByDescending(x => x.LastInscription.Date);
  IncrementalItemsSource.LoadItems(res.Skip(baseIndex).Take(10));

作为一种解决方法，我尝试修改生成的 SQL 查询并将订单放在那里。

它适用于所有列，除了lastseance执行时需要永远的列。

exec sp_executesql N'SELECT [a].[ID_ADHERANT], [a].[ADH_NOM], [a].[ADH_PRENOM], [t0].[INS_DATE_DEBUT], [t0].[INS_DUREE], [t0].[c], (
    SELECT TOP(1) [s].[SEA_DEBUT]
    FROM [INSCRIPTIONS] AS [i]
    INNER JOIN [SEANCES] AS [s] ON [i].[ID_INSCRIPTION] = [s].[INS_ID_INSCRIPTION]
    WHERE [a].[ID_ADHERANT] = [i].[INS_ID_ADHERANT]
    ORDER BY [s].[SEA_DEBUT] DESC)
FROM [ADHERANTS] AS [a]
LEFT JOIN (
    SELECT [t].[INS_DATE_DEBUT], [t].[INS_DUREE], [t].[c], [t].[ID_INSCRIPTION], [t].[INS_ID_ADHERANT]
    FROM (
        SELECT [i0].[INS_DATE_DEBUT], [i0].[INS_DUREE], 1 AS [c], [i0].[ID_INSCRIPTION], [i0].[INS_ID_ADHERANT], ROW_NUMBER() OVER(PARTITION BY [i0].[INS_ID_ADHERANT] ORDER BY [i0].[INS_DATE_DEBUT] DESC) AS [row]
        FROM [INSCRIPTIONS] AS [i0]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [a].[ID_ADHERANT] = [t0].[INS_ID_ADHERANT]
ORDER BY 7 desc /* order by the last seance*/
OFFSET @__p_0 ROWS FETCH NEXT @__p_0 ROWS ONLY',N'@__p_0 int',@__p_0=10

感谢您的任何帮助或建议。

标签： sql-serverentity-framework-core

sql-server - SQL Server LINQ selectman 和 orderby 超时

问题描述

解决方案

推荐阅读