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

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.

Answer 2

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 3

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.