When is the right time to make ToList on an entity framework query

Question

This is called in my service. Now I have asked myself when should I do the .ToList() on the query to materialize the entities?

Do I have to do it at all? Because my application layer will anyway convert the entities to a json array and finally then the enumerator must be enumerated...

Should I follow this thinking? Or is it better to only return a materialized collection to my application layer?

IEnumerable<BrowseSchoolyearDTO> ISchoolyearService.GetAll(int userId)
{
   return _context.Schoolyears
        .Where(s => s.UserId == userId)
        .Select(s => s.ToBrowseSchoolyearDto())
        .ToList();
}

Never. Enumerate the enumerable when producing the response and that's it. — Remus Rusanu, Dec 10 '13 at 07:52
probable duplicate of http://stackoverflow.com/questions/4844660/differences-between-iqueryable-list-ienumerator worth doing a search on the difference between `Iqueryable`, `iEnumerable` and `ToList()` — phil soady, Dec 10 '13 at 08:06
@philsoady just read about IQueryable again and I want that my where clause/filter is done on the server so I will choose IQueryable right? — Elisabeth, Dec 10 '13 at 08:12
Chained queries use Iqueryable to get passed to "server" eg DB. Once it has been enumerated, any subsequent where is done on the memory object. here is another classic post on the subject http://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet — phil soady, Dec 10 '13 at 09:03
"subsequent where..." you mean when I append a ToList() to the IQueryable object and after this I do a .Where it is done inmemory? That sounds clear. ToList() or a foreach trigger the query and materialize the entities to the local context. — Elisabeth, Dec 10 '13 at 15:31

Zaphod · Accepted Answer · 2013-12-10T09:39:01.187

0

You don't need to do a .ToList at all, as long as all your operations on the DTO is done while the db connection is active. You only need to do a .ToList if you need the list to be enumerated and returned outside of the db transaction.

So if your code looks something like this:
Edit: changed repository to service as you don't use repositories
using(var rep = new service()) { var list = rep.GetAll(1); return list.Select(x => new DTOViewModel(x)).ToList(); } you don't need .ToList

If your code looks something like this:

using(var rep = new service())
    {
      var list = rep.GetAll(1);

    }
      return list.Select(x => new DTOViewModel(x));

Then you do need a .ToList

Edit to show more of method

public void DoSomeOperationCalledFromWeb(int id)
{
  IEnumerable<DTO> list;
  using(var serv = new Service())
  {
    list = rep.GetAll(1).ToList();
    /** OR if you need additional filtering **/
    list = rep.GetAll(1).Where(/**some filtering**/).ToList();
  }
  /** Do operations on list **/
}

edited Dec 10 '13 at 09:39

answered Dec 10 '13 at 07:48

Zaphod

1,412
2
13
27

I'm pretty sure the first code example will throw an `ObjectDisposedException`... – RePierre Dec 10 '13 at 07:50
hmmm.. I thought it would enumerate the list to be able to do the select part. I'll test it. – Zaphod Dec 10 '13 at 07:52
My concern is that when I do not ToList() then my application layer will "go" to the database for me doing a foreach on the returned data... – Elisabeth Dec 10 '13 at 07:55
Re-Pierre -> you're right. It doesn't do the enumeration on select. Updated the code. Thanks – Zaphod Dec 10 '13 at 08:07
Elisa -> Yes it will. I wouldn't recommend to do a .ToList in your repository, but there might be other concerns that validates the use of .ToList, so it's hard to say one or the other without knowing your application. If possible leave the enumeration for when you actually need the response. Why is it a problem for the application layer to initiate the .ToList? If your concern is for security (someone piggybacking the sql) I don't think you have to worry, but haven't done research on it. – Zaphod Dec 10 '13 at 08:11
@Zaphod I do not use a repository/using statement. My context is created finally by a web request for its lifetime so the connection should be closed when the response to the client is returned. Therefore your code samples does not fit to my scenario or please adapt them. – Elisabeth Dec 10 '13 at 08:17
@Elisa Just switch repository with service in my comments and code and it should be pretty much the same. I'm so used to repositories that I automatically used that term. If you still want me to give you an example, please post sample code for where you call the service. It will be faster for me to give you an example if I have your code. – Zaphod Dec 10 '13 at 08:40

When is the right time to make ToList on an entity framework query

1 Answers1