C#•2y 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.

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

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)

EFC -> Nested Property Sorting

Similar Threads

EFC -> Nested Property Sorting

Similar Threads

Similar Threads

Similar Threads