NotSupportedException: LINQ to Entities does not recognize the method

Question

I'm trying to make this query work with EF but it throws an exception:

var c = ac.Communities.OrderBy(o => o.Posts.Count())
        .Skip(page*limit)
        .Take(limit)
        .Select(o => o.ToViewModel()).ToArray();

The ToViewModel() method from the Community model looks like this:

public CommunityModel ToViewModel()
{
    return new CommunityModel()
    {
        category = Category.Name,
        created = CreationTime,
        description = Description,
        id = Id,
        name = Name,
        ownerId = Owner.Id,
        postsCount = Posts.Count(),
        score = Posts.Sum(o => o.Likes - o.Unlikes),
        shortDescription = ShortDescription,
        subscribersCount = Subscribers.Count(),
    };
}

What am I doing wrong?

To rule out your `ToViewModel` method, pop a `.ToList()` in front of the `Select` and see if you still have the issue. — Stuart, Jan 03 '17 at 00:52
@Stuart That works, thanks! So, basically, the `List` is no longer an `IQueryable`so it doesn't try to translate this into SQL Queries for the database, right? That means it does all that in the memory, treating the `Community` class as an actual object, not a db entity, right? — Reynevan, Jan 03 '17 at 00:56
That's correct, you are forcing an enumeration of the `IQueryable` at the point to use `ToList`, then the follow Linq methods are acting on an in memory collection and don't have to worry about translating to SQL — Stuart, Jan 03 '17 at 00:59

score 3 · Accepted Answer · answered Jan 03 '17 at 01:03

Entity Framework cannot translate the methods you are using inside ToViewModel. Use ToList() to eagerly load the results, then map those instead, avoiding the need for EF to try to translate the SQL:

var c = ac.Communities.OrderBy(o => o.Posts.Count())
    .Skip(page*limit)
    .Take(limit)
    .ToList()
    .Select(o => o.ToViewModel()).ToArray();

score 1 · Answer 2 · answered Jan 03 '17 at 01:00

This is because LINQ to Entities, try to convert .ToViewModel to an SQL query, which isn't recognized by the converter.

If there are not a lot of results being returned by .Take, then your best solution will be converting the IQueryable to List by changing it to the following.

var c = ac.Communities.OrderBy(o => o.Posts.Count())
        .Skip(page*limit)
        .Take(limit)
        .ToArray() // This will return a Community array
        .Select(o => o.ToViewModel()) // This is a IEnumerable<CommunityModel>
        .ToArray(); // This will cast // This is a cast for Community array

Yes, that's what I'm gonna use. Thanks. Just out of curiosity, though: accept This is because LINQ to Entities, try to convert .ToViewModel to an SQL query, which isn't recognized by the converter. If there are not a lot of results being returned by .Take, then your best solution will be converting the IQueryable to List by changing it to the following.... otherwise... ? — Reynevan, Jan 03 '17 at 02:07

NotSupportedException: LINQ to Entities does not recognize the method

2 Answers2

Linked