Why EF Core LINQ query works okay for one child and does not work for another one?

Question

So I have a query to get a list of chats with 2 users (including their pictures) and the last message.

An exception is thrown because of that last message sub-query:

"The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY p5.P2pChatId ORDER BY p5.CreatedOn DESC)' 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."

LastMessage = x.P2pChatMessages
             .OrderByDescending(y => y.CreatedOn)
             .Select(y => new P2pChatMessageViewModel()
             {
                 Id = y.Id,
                 ChatId = y.P2pChatId,
                 UserId = y.UserId,
                 CreatedOn = y.CreatedOn.ToString("s") + "+00:00",
                 Text = y.Text,
             })
             .FirstOrDefault()

My question is why this sub-query fails, while the same sub-query right above passes ok:

Person2 = x.Person2 == null ? null : new UserViewModel()
             {
                 Id = x.Person2.Id,
                 Username = x.Person2.UserName,
                 Email = x.Person2.Email,
                 ProfilePicture = new ImageViewModel()
                 {
                     UserId = x.Person2.Id,
                     Url = x.Person2.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Url).FirstOrDefault(),
                     Id = x.Person2.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Id).FirstOrDefault(),
                 },
             },

Full query:

 var list = await _db.P2pChats
     .OrderByDescending(x => x.P2pChatMessages.Any(y => !y.IsRead))
     .Select(x => new P2pChatViewModel
     {
         Id = x.Id,
         Person1 = x.Person1 == null ? null : new UserViewModel()
         {
             Id = x.Person1.Id,
             Username = x.Person1.UserName,
             Email = x.Person1.Email,
             ProfilePicture = new ImageViewModel()
             {
                 UserId = x.Person1.Id,
                 Url = x.Person1.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Url).FirstOrDefault(),
                 Id = x.Person1.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Id).FirstOrDefault(),
             },
         },
         Person2 = x.Person2 == null ? null : new UserViewModel()
         {
             Id = x.Person2.Id,
             Username = x.Person2.UserName,
             Email = x.Person2.Email,
             ProfilePicture = new ImageViewModel()
             {
                 UserId = x.Person2.Id,
                 Url = x.Person2.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Url).FirstOrDefault(),
                 Id = x.Person2.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Id).FirstOrDefault(),
             },
         },
         LastMessage = x.P2pChatMessages
             .OrderByDescending(y => y.CreatedOn)
             .Select(y => new P2pChatMessageViewModel()
                 {
                     Id = y.Id,
                     ChatId = y.P2pChatId,
                     UserId = y.UserId,
                     CreatedOn = y.CreatedOn.ToString("s") + "+00:00",
                     Text = y.Text,
                 })
             .FirstOrDefault()
         })
     .Take(50)
     .ToListAsync();

Answer 1

The problem is that you have a client-side evaluation in CreatedOn, and this is being placed before the ROW_NUMBER transform.

You can solve this by either changing to a server-side evaluation. For this you would need to add the CONVERT function to your model as a DbFunction, then call it:

CreatedOn = MyFunctions.Convert(y.CreatedOn, 126) + "+00:00",

Or push the row-number to earlier in the query before client-side evaluation, by using an intermediate variable

var list = await _db.P2pChats
     .OrderByDescending(x => x.P2pChatMessages.Any(y => !y.IsRead))
     .Select(x => new {
         x,
         LastMessage = x.P2pChatMessages.OrderByDescending(y => y.CreatedOn).FirstOrDefault()
     })
     .Select(x => new P2pChatViewModel
     {
         Id = x.x.Id,
         Person1 = x.x.Person1 == null ? null : new UserViewModel()
         {
             Id = x.x.Person1.Id,
             Username = x.x.Person1.UserName,
             Email = x.x.Person1.Email,
             ProfilePicture = new ImageViewModel()
             {
                 UserId = x.x.Person1.Id,
                 Url = x.x.Person1.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Url).FirstOrDefault(),
                 Id = x.x.Person1.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Id).FirstOrDefault(),
             },
         },
         Person2 = x.x.Person2 == null ? null : new UserViewModel()
         {
             Id = x.x.Person2.Id,
             Username = x.x.Person2.UserName,
             Email = x.x.Person2.Email,
             ProfilePicture = new ImageViewModel()
             {
                 UserId = x.x.Person2.Id,
                 Url = x.x.Person2.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.x.Image.Url).FirstOrDefault(),
                 Id = x.x.Person2.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image.Id).FirstOrDefault(),
             },
         },
         LastMessage = new P2pChatMessageViewModel
         {
             Id = x.LastMessage.Id,
             ChatId = x.LastMessage.P2pChatId,
             UserId = x.LastMessage.UserId,
             CreatedOn = x.LastMessage.CreatedOn.ToString("s") + "+00:00",
             Text = x.LastMessage.Text,
          })
     })
     .Take(50)
     .ToListAsync();

---

However, I strongly suspect that this query can be written much better anyway, by combining and moving the duplicate subqueries into variables. This is often easier in Query Syntax rather than Method Syntax, because you can use let.

var list = await
    from c in _db.P2pChats
    let cm = c.P2pChatMessages
        .GroupBy(c => 1)  // group by empty set
        .Select(g => new {
            AnyUnread = g.Where(y => !y.IsRead).Any(),
            LastMessage = g.OrderByDescending(y => y.CreatedOn).FirstOrDefault(),
        })
    let ppImage1 = c.Person1?.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image).FirstOrDefault()
    let ppImage2 = c.Person2?.ProfilePictures.OrderByDescending(y => y.CreatedOn).Select(y => y.Image).FirstOrDefault()
    orderby cm.AnyUnread descending
    select new P2pChatViewModel
    {
        Id = c.Id,
        Person1 = c.Person1 == null ? null : new UserViewModel()
        {
            Id = c.Person1.Id,
            Username = c.Person1.UserName,
            Email = c.Person1.Email,
            ProfilePicture = new ImageViewModel()
            {
                UserId = c.Person1.Id,
                Url = ppImage1.Url,
                Id = ppImage1.Id,
            },
        },
        Person2 = c.Person2 == null ? null : new UserViewModel()
        {
            Id = c.Person2.Id,
            Username = c.Person2.UserName,
            Email = c.Person2.Email,
            ProfilePicture = new ImageViewModel()
            {
                UserId = c.Person2.Id,
                Url = ppImage2.Url,
                Id = ppImage2.Id,
            },
        },
        LastMessage = cm.LastMessage,
     .Take(50)
     .ToListAsync();