C#C
C#•2y ago
Jeth

EFC -> Nested Property Sorting

So I have a Queryable:
filterQueryable = filterQueryable.Select(k => new Storing
{
    LaatsteStoringActie = k.StoringActies
        .OrderByDescending(s => s.Creatiedatum)
        .Take(1)
        .Select(s => new StoringActie
        {
            Id = s.Id,
            StoringId = s.StoringId,
            TypeId = s.TypeId,
            Type = s.Type,
            Creatiedatum = s.Creatiedatum,
            Omschrijving = s.Omschrijving
        })
        .First(),
}).AsQueryable()


After this I'm using this:
(ToSort = LaatsteStoringActie.Omschrijving) <- Nested Property which is not available from getting the Storingen, only available through the select because it needs the StoringActies list to retrieve the last StoringActie 
if (filter.SortType != null)
{
    if (filter.SortType.Type.StartsWith("Asc"))
    {
        result = OrderByAscending(result, filter.SortType.ToSort);
    }
    else if (filter.SortType.Type.EndsWith("Desc"))
    {
        result = OrderByDescending(result, filter.SortType.ToSort);
    }
}


Now SQL Profiler shows this: 
    ORDER BY (
        SELECT TOP(1) [t].[Omschrijving]
        FROM (
            SELECT TOP(1) [s0].[Omschrijving], [s0].[ID], [s0].[Creatiedatum]
            FROM [StoringActie] AS [s0]
            WHERE [s].[ID] = [s0].[StoringID]
            ORDER BY [s0].[Creatiedatum] DESC
        ) AS [t]
        ORDER BY [t].[Creatiedatum] DESC)

Which causes it to take ages because it goes through every "Creatiedatum" to get the last and "Omschrijving" to get it ordered

Do any of you have advice on this? If I don't use a nested property in the ToSort it works instantly, but if I use a nested property, it takes 20-30 seconds to load.
Was this page helpful?