Method x has no supported translation to SQL

Question

I want to write a query which should get an user object and the amount of messages the user has posted already. I did this the following way:

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

I'm using a method because some messages should be filtered out (in a dynamic way).

To keep things simple I'll post the function without sorting logic (which still produces the same error).

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

The error returned is:

Method 'x' has no supported translation to SQL.

Any ideas on how to fix this issue?

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