C

C#

help

Join Server

✅ EF two Select in a query problem when using ToList().

LLary12/26/2022
        var monthsTopPosts = _db.Votes
            .Where(v =>
                v.targetPostId != null &&
                v.targetPost!.isPublished == true &&
                v.targetPost!.deletedStatus != null &&
                v.targetPost!.deletedStatus.body == "Default" &&
                v.targetPost!.publishDate > DateTime.Now.AddDays(-30))
            .GroupBy(v => v.targetPostId)
            .Select(g => new
            {
                post = g.First().targetPost,
                karma = g.Sum(v => v.body == true ? 1 : -1)
            })
            .OrderByDescending(k => k.karma)
            .Take(1)
            .ToList()
            .Select(x => x.post)
            .ToList();


I want to reduce two ToList() to one. But it gives me error.

System.InvalidOperationException: The LINQ expression 'ProjectionBindingExpression: 0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
DDeluxe12/26/2022
seems to me like you should really be starting this query from the Posts table not votes, considering you want to get the post
DDeluxe12/26/2022
_context.Posts.Where(conditions)
  .OrderByDescending(p => p.Votes.Sum(v => v.body == true ? 1 : -1))
  .Take(n)
  .ToListAsync();
LLary12/26/2022
Yeah, I should do that. But I want to know why it gives me a run time error. Which part was wrong I still don't understand.
DDeluxe12/26/2022
EF can't translate the g.First().targetPost into sql more than likely
LLary12/26/2022
Nah, the code I gave works. In the second select, I am able to choose between post and karma but it throws an error if I don't write ToList() before it
LLary12/26/2022
If I write ToList(), the result is what I expected. It must be something about Iqueryable or something, no idea why
DDeluxe12/26/2022
Not too sure without seeing what kind of sql its generating for the case that works
DDeluxe12/26/2022
either way, id rewrite the query to make a bit more sense
LLary12/26/2022
of course. It's the second time I am facing this select problem. It gets more frustrating because I don't know why. And it doesn't tell me anything
DDeluxe12/26/2022
any time you run into that its because ef cannot translate the query into sql, so something you are doing does not make sense in the context of sql
LLary12/26/2022
Ouch
LLary12/26/2022
Lemme rewrite it first
LLary12/26/2022
var monthsTopPosts = _db.Posts
            .Where(p =>
                p.isPublished == true &&
                p.deletedStatus != null &&
                p.deletedStatus.body == "Default" &&
                p.publishDate > DateTime.Now.AddDays(-30))
            .Select(g => new
            {
                post = g,
                karma = g.Votes.Sum(v => v.body == true ? 1 : -1)
            })
            .OrderByDescending(k => k.karma)
            .Take(1)
            .Select(x => x.post)
            .ToList();

This worked for some reason. Obviously I will improve it. This is too raw.
LLary12/26/2022
Let me know if you reviewed it.
DDeluxe12/26/2022
you can probably remove both of the selects by just ordering by the votes, but keeping it doesn't really affect much
LLary12/26/2022
var monthsTopPosts = _db.Posts
            .Where(p =>
                p.isPublished == true &&
                p.deletedStatus != null &&
                p.deletedStatus.body == "Default" &&
                p.publishDate > DateTime.Now.AddDays(-30))
            .OrderByDescending(k => k.Votes.Sum(v => v.body == true ? 1 : -1))
            .Take(1)
            .ToList();

"Doesn't really affect much"... this is huge lol
LLary12/26/2022
readability is very important after all
LLary12/26/2022
Thank you kind stranger. You saved my day.
LLary12/26/2022
Or morning anyway. It's 3am again.
LLary12/26/2022
Btw, do you have any idea to make this better;
var todaysTopPosts = await _mapper.ProjectTo<PostDtoRead_1>(_db.Posts
            .Where(p =>
                p.isPublished == true &&
                p.deletedStatus != null &&
                p.deletedStatus.body == "Default" &&
                p.publishDate > DateTime.Now.AddDays(-1))
            .OrderByDescending(v => v.Votes.Sum(v => v.body == true ? 1 : -1))
            .Take(4))
            .ToListAsync();

        var monthsTopPosts = await _mapper.ProjectTo<PostDtoRead_1>(_db.Posts
            .Where(p =>
                p.isPublished == true &&
                p.deletedStatus != null &&
                p.deletedStatus.body == "Default" &&
                p.publishDate > DateTime.Now.AddDays(-30))
            .OrderByDescending(v => v.Votes.Sum(v => v.body == true ? 1 : -1))
            .Take(4))
            .ToListAsync();
DDeluxe12/27/2022
maybe
async Task<List<PostDtoRead_1>> GetTopPosts(int days) {
  return await _db.Posts
    .Where(p =>
       p.isPublished == true &&
       p.deletedStatus.body == "Default" &&
       p.publishDate > DateTime.Now.AddDays(-days))
    .OrderByDescending(v => v.Votes.Sum(v => v.body == true ? 1 : -1))
    .Take(4)
    .ProjectTo<PostDtoRead_1>(_mapper.ConfigurationProvider)
    .ToListAsync();
}

var todaysTopPosts = await GetTopPosts(1);
var monthsTopPosts = await GetTopPosts(30);
LLary12/27/2022
Yeah, a single function seems like an obvious choice
LLary12/27/2022
Btw, what's with the ProjectTo? Never seen this use before.
DDeluxe12/27/2022
extension method from automapper, i think it looks nicer than having to wrap the entire query in _mapper.ProjectTo<T>(query)
LLary12/27/2022
As long as it doesn't affect speed. It looks cool tho. I will use it from now on. I hope the extension isn't trying to be smart and be dead weight.
LLary12/27/2022
Thank you again