Method x does not support SQL translation

Question

I want to write a query that the user object should receive and the number of messages that the user has already posted. I did it as follows:

var query = (from u in _db.Repository<User>()
             where u.IsDeleted != true
             select new UserWithMessagecount()
             {
                 User = u
                 MessageCount = GetUserMessageCount(u.Documents).Count(),
             });

I use the method because some messages need to be filtered out (in a dynamic way) .

To keep things simple, I will post a function without sorting logic (which still causes the same error).

    private EntitySet<Document> GetUserMessageCount(EntitySet<Document> set)
    {
        return set;
    }

Error returned:

The 'x' method does not support SQL translation.

Any ideas on how to fix this?

Answer 1

use this syntax instead:

 var query = (from u in _db.Repository<User>()
             let MessageCount = GetUserMessageCount(u.Documents).Count()
             where u.IsDeleted != true
             select new UserWithMessagecount()
             {
                 User = u,
                 MessageCount = MessageCount
             });

Answer 2

Linq-to-SQL SQL, , GetUserMessageCount().

SQL-, , #.

Answer 3

What you need to do is use the grouping in your projection.

var query = from u in _db.Repository<User>()
            where !u.IsDeleted
            group u by u.UserId into g
            select new UserWithMessageCount {
               User = g.First(x => x.UserId == g.Key),
               MessageCount = g.Sum(x => x.Messages.Count())
            }

That should work.