C#•10mo ago

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

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

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)

    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.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

EFC -> Nested Property Sorting