C#•2y ago

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

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

        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.

14 Replies

Saber•2y ago

seems to me like you should really be starting this query from the Posts table not votes, considering you want to get the post

_context.Posts.Where(conditions)
  .OrderByDescending(p => p.Votes.Sum(v => v.body == true ? 1 : -1))
  .Take(n)
  .ToListAsync();

_context.Posts.Where(conditions)
  .OrderByDescending(p => p.Votes.Sum(v => v.body == true ? 1 : -1))
  .Take(n)
  .ToListAsync();

İrşatOP•2y ago

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.

Saber•2y ago

EF can't translate the g.First().targetPost into sql more than likely

İrşatOP•2y ago

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 If I write ToList(), the result is what I expected. It must be something about Iqueryable or something, no idea why

Saber•2y ago

Not too sure without seeing what kind of sql its generating for the case that works either way, id rewrite the query to make a bit more sense

İrşatOP•2y ago

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

Saber•2y ago

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

İrşatOP•2y ago

Ouch Lemme rewrite it first

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

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. Let me know if you reviewed it.

Saber•2y ago

you can probably remove both of the selects by just ordering by the votes, but keeping it doesn't really affect much

İrşatOP•2y ago

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

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 readability is very important after all Thank you kind stranger. You saved my day. Or morning anyway. It's 3am again. 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();

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

Saber•2y ago

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

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

İrşatOP•2y ago

Yeah, a single function seems like an obvious choice Btw, what's with the ProjectTo? Never seen this use before.

Saber•2y ago

extension method from automapper, i think it looks nicer than having to wrap the entire query in _mapper.ProjectTo<T>(query)

İrşatOP•2y ago

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. Thank you again

Gaming

Programming

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