SQL Server LINQ selectmany and orderby timeout

In SQL Server 2017 Express I have two level of master / details relations tables like this

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

Please note that the foreign key in SEANCEs is nullable.

What I want is to get for each ADHERANTS the last detail record based on date.

In EF Core 3.1 LINQ I have something like this:

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

Which works and produces this SQL query:

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

But if I try to add an orderby on top of that I get a timeout exception:

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

As a workaround I have tried to modify the generated SQL query and put the order by there.

It worked for all the columns except for the lastseance one which take forever when executed.

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

Thanks for any help or suggestions.