C
C#2y ago
İrşat

✅ 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
Cisien2y ago
Where/select
İrşat
İrşatOP2y ago
I am not sure what to write there 😄 My linq is pretty poor
Cisien
Cisien2y ago
Where (a=> savedids.indecof(a)!= 0) should work Or use contains instead of indexof
İrşat
İrşatOP2y 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
Cisien2y 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
İrşatOP2y ago
Is this to filter posts to get saved posts?
Cisien
Cisien2y 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
İrşatOP2y ago
I am sorry I am not sure why this is relevant to my problem 😄
Cisien
Cisien2y 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
İrşatOP2y 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
Cisien2y ago
why not sort by p.id? another option is to include the date in your select
İrşat
İrşatOP2y ago
I don't wan't 2, 3, 5.
Cisien
Cisien2y 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
İrşatOP2y ago
You mean an entity framework relationship? Which is like "Posts.SavedPosts" and "SavedPosts.Posts" ?
Cisien
Cisien2y 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
İrşatOP2y ago
Yeah I do have it as a collection
Cisien
Cisien2y ago
but if you can do it in a single query with joins (.Include/navigation properties) it will likely be easier/better
İrşat
İrşatOP2y 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.
Want results from more Discord servers?
Add your server