Why does this LINQ expression break my loop & conversion logic?

Question

Background

ArticleService is a class that provides methods for front-end layers to facilitate business with the back-end.

Two of its base responsibilities are to convert ViewModels (ArticleViewModel) to the appropriate Models (Article) when persisting data, and the reverse, convert Models to ViewModels when fetching data... so often that I created a private method building ViewModel objects:

private ArticleViewModel BuildViewModel(Article a)
{
    return new ArticleViewModel { Title = a.Title /* all properties */ }
}

Moving along, the ArticleService provides a method to fetch all articles from the data store, returning them as ViewModels: public IEnumerable<ArticleViewModel> All()

A calling class uses it like this: var articleViewModels = _articleService.All();

Simple, right?

Problem:

I originally wrote All() lazily with a classic foreach loop:

private IEnumerable<ArticleViewModel> All()
{
    var viewModels = new List<ArticleViewModel>();
    foreach (var article in _db.Articles)
        viewModels.Add(BuildViewModel(article));
    return viewModels;
}

It worked fine - articleViewModels was an instantiate list of all view models.

Next, I used ReSharper to convert this loop to a LINQ statement for performance and prettiness, and then combined the assignment statement with the return statement. Result:

private IEnumerable<ArticleViewModel> All()
{
    return _db.Articles.Select(article => BuildViewModel(article)).ToList();
}

I debugged the LINQ statement and the beast awakened:

LINQ to Entities does not recognize the method 'ArticleViewModel BuildViewModel(Article)' and this method cannot be translated into a store expression.

Question - Why does this LINQ statement break my code?

Note: Stepping back to an explicit declaration, assignment, return worked with the LINQ statement, so I'm almost certain it's something to do with the lambda logic.

Answer 1

Question - Why does this LINQ statement break my code?

Because LINQ to Entities is trying to translate BuildViewModel into SQL. It doesn't know how, so it dies miserably.

In your original version, you were streaming the entity from the database to your local box, and then doing the projection using BuildViewModel client-side. That's fine.

so I'm almost certain it's something to do with the lambda logic.

Nope. It's because LINQ to Entities can't translate BuildViewModel into SQL. It doesn't matter if you use a lambda expression to express the projection or not.

You can rewrite the code like this:

return _db.Articles.
          .AsEnumerable()
          .Select(article => BuildViewModel(article)).ToList();

This causes _db.Articles to be treated as a plain old enumerable, and then does the projection client side. Now LINQ to Entities doesn't have to figure out what to do with BuildViewModel.