Issue with materialized query result - LINQ

Question

I have a List that includes IQueryAble inside as a property. I'm passing List to my View in MVC project. I iterate through the List using foreach. Inside the foreach:

Albums in type of List

<% foreach(var x in Albums){%>

 <h1><%= x.Title %></h1>
<p><%= x.Photos.Count() %> </p>

<%}%>

Displaying the Title is not an issue, but it throws an error as soon it hits the Count(): "This method is not supported against a materialized query result."

what does this mean? I cannot have collection inside a collection? as soon as I iterate through the collection, the other collection inside that class is not useable anymore.

thanks

Answer 1

I think you will have to include Photos in your Albums query. Something like.

var Albums = from a in context.Ablums.Include("Photos") select a;

Answer 2

Use a presentation model:

public class AlbumPresentation
{
    public string Title { get; set; }
    public int PhotoCount { get; set; }
}

Then project onto the model in your controller:

var model = (from a in Context.Albums // or, more likely, via a repository
             select new AlbumPresentation
             {
                 Title = a.Title,
                 PhotoCount = a.Photos.Count()
             }).ToList();
return View(model);

The type of your View is now ViewPage<IEnumerable<AlbumPresentation>>.

Note that unlike using eager loading (Include()), you are no longer required to load all of the Photo records from the database just to get the count. You can load that information if you need it, but only if you need it.