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

İrşat · 2022-12-26T23:27:29.217Z

```cs 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.

S

Saber•12/26/22, 11:33 PM

seems to me like you should really be starting this query from the

Posts

Posts

Posts

Posts table not votes, considering you want to get the post

S

Saber•12/26/22, 11:35 PM

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

_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•12/26/22, 11:36 PM

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.

S

Saber•12/26/22, 11:36 PM

EF can't translate the

g.First().targetPost

g.First().targetPost

g.First().targetPost

g.First().targetPost into sql more than likely

İ

İrşatOP•12/26/22, 11:37 PM

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

İ

İrşatOP•12/26/22, 11:38 PM

If I write ToList(), the result is what I expected. It must be something about Iqueryable or something, no idea why

S

Saber•12/26/22, 11:39 PM

Not too sure without seeing what kind of sql its generating for the case that works

S

Saber•12/26/22, 11:39 PM

either way, id rewrite the query to make a bit more sense

İ

İrşatOP•12/26/22, 11:40 PM

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

S

Saber•12/26/22, 11:40 PM

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•12/26/22, 11:41 PM

Ouch

İ

İrşatOP•12/26/22, 11:41 PM

Lemme rewrite it first

İ

İrşatOP•12/26/22, 11:46 PM

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

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.

İ

İrşatOP•12/26/22, 11:46 PM

Let me know if you reviewed it.

S

Saber•12/26/22, 11:48 PM

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

İ

İrşatOP•12/26/22, 11:50 PM

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

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

İ

İrşatOP•12/26/22, 11:50 PM

readability is very important after all

İ

İrşatOP•12/26/22, 11:52 PM

Thank you kind stranger. You saved my day.

İ

İrşatOP•12/26/22, 11:52 PM

Or morning anyway. It's 3am again.

İ

İrşatOP•12/26/22, 11:54 PM

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

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

S

Saber•12/27/22, 12:01 AM

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

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•12/27/22, 12:01 AM

Yeah, a single function seems like an obvious choice

İ

İrşatOP•12/27/22, 12:04 AM

Btw, what's with the ProjectTo? Never seen this use before.

S

Saber•12/27/22, 12:05 AM

extension method from automapper, i think it looks nicer than having to wrap the entire query in

_mapper.ProjectTo<T>(query)

_mapper.ProjectTo<T>(query)

_mapper.ProjectTo<T>(query)

_mapper.ProjectTo<T>(query)

İ

İrşatOP•12/27/22, 12:07 AM

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.

İ

İrşatOP•12/27/22, 12:11 AM

Thank you again

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

Similar Threads

Similar Threads

Similar Threads