C#•2y ago

✅ Ordering IQueryable by the indexes of another list

        List<int> savedPostIds = await _db.SavedPosts
                .Where(a => a.account.username == targetUsername)
                .OrderByDescending(a => a.saveDate)
                .Select(x => x.targetPostId)
                .ToListAsync();

        var posts = await _mapper.ProjectTo<PostDtoRead_1>(_db.Posts
            .Where(p => savedPostIds.Contains(p.id))
            .OrderBy(p => savedPostIds.IndexOf(p.id)))
            .ToListAsync();

        List<int> savedPostIds = await _db.SavedPosts
                .Where(a => a.account.username == targetUsername)
                .OrderByDescending(a => a.saveDate)
                .Select(x => x.targetPostId)
                .ToListAsync();

        var posts = await _mapper.ProjectTo<PostDtoRead_1>(_db.Posts
            .Where(p => savedPostIds.Contains(p.id))
            .OrderBy(p => savedPostIds.IndexOf(p.id)))
            .ToListAsync();

IndexOf method doesn't work on IQueryable it seems.

posts = posts.OrderBy(p => savedPostIds.IndexOf(p.id)).ToList();

posts = posts.OrderBy(p => savedPostIds.IndexOf(p.id)).ToList();

I can do that but that makes it look bad, idk. Is it even good practice?

18 Replies

Cisien•2y ago

Where/select

İrşat•2y ago

I am not sure what to write there 😄 My linq is pretty poor

Cisien•2y ago

Where (a=> savedids.indecof(a)!= 0) should work Or use contains instead of indexof

İrşat•2y ago

Contains? I am trying to sort the posts. savedPostIds are ordered. Post ids should be the same order.

var posts = await _mapper.ProjectTo<PostDtoRead_1>(_db.Posts
            .Where(p =>
                savedPostIds.Contains(p.id) &&
                savedPostIds.IndexOf(p.id) != 0))   <-- ??
            .ToListAsync();

var posts = await _mapper.ProjectTo<PostDtoRead_1>(_db.Posts
            .Where(p =>
                savedPostIds.Contains(p.id) &&
                savedPostIds.IndexOf(p.id) != 0))   <-- ??
            .ToListAsync();

Cisien•2y ago

one or the other you only need one Contains will do the same thing as indexof != -1 (sorry, typo in the first suggestion)

İrşat•2y ago

Is this to filter posts to get saved posts?

Cisien•2y ago

other way around Where is a filter, it expects an expression that returns true if it should include the item, or false if not. So what this is doing is checking to see if the post's id is contained in the savedpostIds list, if it is, include it

İrşat•2y ago

I am sorry I am not sure why this is relevant to my problem 😄

Cisien•2y ago

well, in reality it's generating sql that look similar to SELECT * From Posts WHERE id IN (1,2,3,4,5 /*whatever saved posts ids contains*/)

İrşat•2y ago

I have a savedPostIds list. This table has save date inside it. So the order can be 5, 2, 3 because it's sorted by date. Let's say I have 5 posts. This code brings me the saved posts as 2, 3 ,5. I want to sort it just like in the savedPostIds. Which is 5, 2, 3. But I don't have the save dates while fetching posts. So I need to use the index of List<int> savedPostIds.

.OrderBy(p => savedPostIds.IndexOf(p.id))

.OrderBy(p => savedPostIds.IndexOf(p.id))

is the perfect candidate for the job. But for some reason it throws an error at runtime.

Cisien•2y ago

why not sort by p.id? another option is to include the date in your select

İrşat•2y ago

I don't wan't 2, 3, 5.

Cisien•2y ago

do you have a navigation property between posts and saved posts? that's probably the best way to get at the posts

İrşat•2y ago

You mean an entity framework relationship? Which is like "Posts.SavedPosts" and "SavedPosts.Posts" ?

Cisien•2y ago

var posts = await _db.SavedPosts.Where(a => a.account.username == targetUsername) .OrderByDescending(a => a.saveDate) .Select(x => x.Post) .ToListAsync(); yeah something like that should let you skip the intermediary step of collecting ids and then doing another query of course the specifics aren't exactly what i posted, you'll probably have to tweak them

İrşat•2y ago

Yeah I do have it as a collection

Cisien•2y ago

but if you can do it in a single query with joins (.Include/navigation properties) it will likely be easier/better

İrşat•2y ago

Worked like magic thank you magic man Webapi doesn't take the seconds for some reason. I had to wait a minute to test it 😄 Completely removed savedPostIds list too. Just like you showed.

Gaming

Programming

✅ Ordering IQueryable by the indexes of another list