Calling database upon ToList() method call

Question

From my repository I return different List using ToList() method.

The problem is when I run some more LINQ on this returned result (i.e. of type List) it generates a database call for this too. Apparently this second linq calll is pure LINQ to Entity and database should not be called. See below what I am doing.

List<User> us = userRepository.GetMany(u => filterStatusIds.Contains(u.UserStatus.Id));
if (!string.IsNullOrEmpty(name))
us = (from u in us
      where u.DisplayName.Contains(name)
      select u).ToList(); // this ToList should not call database

Any help or idea to stop this additional database calls?

Why do you think it executes database query again? Also why do you execute filter in your application instead of the database? — Ladislav Mrnka, May 07 '12 at 09:29
@abatishchev: The first call to `ToList` will switch to linq-to-objects and there is no way back because the result is materialized and expression tree is gone. — Ladislav Mrnka, May 07 '12 at 09:32
@Ladislav: Sure, you're right, have misread your initial comment. — abatishchev, May 07 '12 at 09:34

score 1 · Answer 1 · answered May 07 '12 at 09:28

1

Return IEnumerable<User> or IQueryable<T> from repository, it will not query the database.

Only second call will do this, after ToList().

answered May 07 '12 at 09:28

abatishchev

98,240
88
296
433

out of interest - does it mean that in the first query the return type of `List` forces an enumeration of the results ? I.e. it's not just some sort of a cast between `IEnumerable` and `List` but it involves the materialization of the results (the `ToList()` is implicit) ? That should probably be obvious to me, but I would be happy if you can confirm. – Joanna Derks May 07 '12 at 12:16
I tried a simple example with List which I filtered and without the call to `ToList` the compiler just told me that IEnumerable cannot be converted to a list implicitly. Is that different in this example ? How does it work in the first line of the example ? – Joanna Derks May 07 '12 at 12:22
@Joanna: Yes, calling ToArray()/ToList() causes materialization. So if method returns just IEnumerable/IQueryable "as is", no materialization occurs. After first m-z you switch from LINQ to Entities to LINQ to Objects, i.e. load first filtering result into server memory and enumerate it into memory in every next filter. – abatishchev May 07 '12 at 15:38
@Joanna: So if you want to return non-materialized data, don't call m-z methods on return/query, and "simplify" return type, to match. – abatishchev May 07 '12 at 15:39
@Joanna: imo, that's understandable and useful in case of LINQ to Database, but much less in LINQ to XML. I've fallen into a trap with it once. – abatishchev May 07 '12 at 15:44

Calling database upon ToList() method call

1 Answers1