Method x has no supported translation to SQL

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 ho开发者_JS百科w to fix this issue?

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

Linq-to-SQL will be trying to convert your entire statment into SQL, an of course there is no GetUserMessageCount() available.

You will need to take the results of the SQL query by enumerating it -- then apply the C# side logic.

What you need to do is to use 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())
            }

This should work.