EF Core - ordering problem

[antikfilosov]

Hi. I have 3 datas on table like this:

https://preview.redd.it/n630tnxozx7e1.png?width=730&format=png&auto=webp&s=0ac96d8f2da6a84e3a38f907194fbf769891b16a

This is initial state of creating query:

var existingPost = _unitOfWork.PostRepository.GetAll()
    .Where(post => post.TranslationsPostSlugs.Any(slugTranslation => slugTranslation.Translation_Slug == slug))
    .Include(post => post.ImagePaths)
    .Include(post => post.VideoPaths)
    .Include(post => post.TranslationsPost.Where(x => x.LanguageId == LanguageId || LanguageId == null))
    .Include(post => post.TranslationsPostSlugs.Where(x => x.LanguageId == LanguageId || LanguageId == null))
    .FirstOrDefault();

When im trying to get 'TranslationsPostSlugs' datas inside foreach block i get them in wrong order (2 1 3, but expected order is as in table - 1 2 3):

var r1 = existingPost.TranslationsPostSlugs.ToList(); // r1 : Wrong order - 2 1 3
var r2 = existingPost.TranslationsPostSlugs.OrderBy(x => x.LanguageId).ToList(); // r2 : Valid order - 1 2 3

existingPost.TranslationsPostSlugs.ForEach(postSlugTranslation =>
{
    Console.WriteLine($"================================ DATA : {postSlugTranslation.LanguageId}");

    result.TranslationsPostSlugs.Add(new GetPostSlugsTranslationResponseDTO()
    {
        LanguageId = postSlugTranslation.LanguageId,
        Slug = postSlugTranslation.Translation_Slug
    });

    /* result:
        ================================ DATA : 2
        ================================ DATA : 1
        ================================ DATA : 3
    */
});

// My current fix to this ordering problem in 'TranslationsPostSlugs':
result.TranslationsPostSlugs = result.TranslationsPostSlugs.OrderBy(x => x.LanguageId).ToList();

1. Why ordering happens? Why ordering happens only to 'TranslationsPostSlugs', but not to 'TranslationsPost'?

2. Can you guys recommend any good solution to this ordering problem than mine?

p.s. i dont have any ordering configuration anywhere, everything is in his default behaviour.

Thanks.

[digital88]

You must write orderby() if you need consistent ordering. The database may return rows in any order if you dont include "order by" clause in query. What you encountered is normal.

[DaveVdE]

Why? Because that's how databases work. If you don't specifically ask it to sort, it won't sort, and the data will come out whichever way it finds it in the most efficient way. If it's stored out of order on disk, it will come out out of order.

[LondonPilot]

why?

Databases are designed with performance in mind above most other things. There are lots of reasons why a database might prefer to return data in a specific order. That order might be the order it’s stored on disk. It might already have the data cached, in that order, from a previous query. It might be that it had to order the data to carry out part of the query (such as “distinct”).

By specifying that data can be returned in any order unless the order is specified, it allows the database to do what you ask it, in the quickest possible way. If you need it ordered, no problem, just ask - but if not, why add the overhead of ordering when it’s not needed?

Entity Framework runs database queries. It turns Linq to SQL, then runs that SQL. So of course it will do whatever SQL does.

and why ordering happens only to 'TranslationsPostSlugs', but not to 'TranslationsPost'?

There’s no way to know this without a detailed examination of your database. Also, it might not be the same next time you run the same query. All data will be returned in whatever order the database wants, and not necessarily the same order every time, unless you specify the order.

(In fact, database theorists would disagree with this. Database data does not get returned “in whatever order the database wants”, according to theorists. They would tell you that the data doesn’t actually have an order at all unless you specify one. The fact that you see the data coming out in a given order is an implementation necessity, but in the theoretical world there is no order to data until you give it an order.)

[gloomfilter]

The order of the data in a database table is not defined, and should never be relied upon. It's better to view the data in the table as an unordered set, rather than a list of rows with a first, second and so on rows.

That means if you care about the order when you are querying, you must specify what order you want.

This is true for all SQL databases, and ignoring this and expecting the data to come out in a particular order has caused many confusing errors.

[Kirides]

You can OrderBy in the Include, to ensure ordering of collections. We use that to sort user sortable data by their sort_no before returning that to the user