Linq to SQL using Repository Pattern: Object has no supported translation to SQL

Answer 1

Try adding another repository method that looks like this:

public int CommunitiesCount()
{
    get { return _ctx.Communities.Count(); }
}

This will allow you to return a count without exposing the entire object tree to the user, which is what I think you're trying to do anyway.

As you may have already guessed, I suspect that what you are calling the anonymous types are at fault (they're not really anonymous types; they are actual objects, which you are apparently partially populating in an effort to hide some of the fields from the end user).

Answer 2

These are non-hydrated queries, not fully-hydrated collections.

The Communities query differs from the other two because it calls methods as objects are hydrated. These method calls are not translatable to SQL.

---

Normally this isn't a problem. For example: if you say Communities.ToList(), it will work and the methods will be called from the objects as they are hydrated.

If you modify the query such that the objects aren't hydrated, for example: when you say Communities.Count(), linq to sql attempts to send the method calls into the database and throws since it cannot. It does this even though those method calls ultimately would not affect the resulting count.

---

The simplest fix (if you truly expect fully hydrated collections) is to add ToList to the community query, hydrating it.