MVC and repository pattern data efficiency

Question

My project is structured as follows:

DAL

public IQueryable<Post> GetPosts()
{
        var posts = from p in context.Post select p;

        return posts;
}

Service

public IList<Post> GetPosts()
{
        var posts = repository.GetPosts().ToList();

        return posts;
}

//Returns a list of the latest feeds, restricted by the count.
public IList<PostFeed> GetPostFeeds(int latestCount)
{
       List<Post> post - GetPosts();

       //CODE TO CREATE FEEDS HERE

       return feeds;

}

Lets say the GetPostFeeds(5) is supposed to return the 5 latest feeds. By going up the list, doesn't it pull down every single post from the database from GetPosts(), just to extract 5 from it?

If each post is say 5kb from the database, and there is 1 million records. Wont that be 5GB of ram being used per call to GetPostFeeds()?

Is this the way it happens? Should I go back to my DAL and write queries that return only what I need?

Answer 1

You get lazy execution until you execute ToList(). At that point, yes, the whole kit and caboodle is pulled down.

If you want more intelligent execution, consider eliminating the ToList() call. Using IQueryables throughout the call chain will allow your repository to retrieve only the five records you need.

Once you have only the five records you need, then you can call ToList().

Answer 2

As long as you're working with IQueryable, query execution can be deferred. The query is executed once you call ToList() or do something else that requires data to be fetched (e.g. iterating over a child collection).

I don't think you need to worry about your DAL too much as I like to keep them fairly lean. You could take advantage of using deferred execution by rewriting your GetPostFeeds method though, e.g.:

//Returns a list of the latest feeds, restricted by the count.
public IList<PostFeed> GetPostFeeds(int latestCount)
{
   var posts = repository.GetPosts();

   // Perform additional filtering here e.g:
   posts = posts.Take(5);

   return posts.ToList();
}

Answer 3

Agreed that by leveraging IQueryable you can avoid returning the entire table. If you don't want IQueryable to leak out of your DAL, you might consider moving the take() call into the DAL by expanding the contract so that the DAL exposes a method that accepts a "top n" parameter.