1

I know that IQueryable has the advantage of running filters on server rather loading all records in memory like IEnumerable, but how does exactly this look like in code, for example, if I have this code:

var data = context.Books.Where(x => x.Id > 930);

if I have 1000 records then it will only load 70 records, and to iterate over them, I convert data to List:

var list = data.ToList(); //has only 70 records

but what are the cases that IEnumerable load all records if it has to pass through IQueryable first i.e. to get the IEnumerable version of the example above:

var eData = context.Books.Where(x => x.Id > 930).ToList();

Aren't they the same? the last code is no more than combining the above two lines of code?

mshwf
  • 7,009
  • 12
  • 59
  • 133

3 Answers3

6

There is no IEnumerable example in your code. Both

var data = context.Books.Where(x => x.Id >930); // Queryable.Where(Expression)

and 

var eData = context.Books.Where(x => x.Id >930).ToList(); // Queryable.Where(Expression)

use IQueryable extensions to convert expression into SQL query and run it on server-side. The only difference between data and eData is that you process query results immediately in second case (i.e. enumerate them and put into list). If you want to use IEnumerable:

var eData = context.Books.AsEnumerable().Where(x => x.Id >930); // Enumerable.Where(Lambda)

And difference here is that you can use any code in filter predicate to filter collection. Because this predicate will not be converted into SQL to run on server side. E.g. you can run your custom method:

var eData = context.Books.AsEnumerable().Where(x => _someObject.CheckBook(x));

but all books should be downloaed from server to client in order to run IEnumerable query.

Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
  • Is this the same as defining the variable as `IQueryable` or `IEnumerable`: `IEnumerable list = context.Books.Where(x => x.Id >930);` `IQueryable data = context.Books.Where(x => x.Id >930);` – mshwf Apr 17 '17 at 11:36
  • @MohamedAhmed if you'll use `IEnumerable list = context.Books.Where(x => x.Id >930);` then you will store `IQueryable` which has some query into `IEnumerable` variable. That means - all *further* actions will use `IEnumerable` extensions. E.g. `list.Where(x => x.Title != "")` will be executed on client side. But first part will be executed on database side – Sergey Berezovskiy Apr 17 '17 at 12:01
  • So the assumption here is wrong: http://haroldrv.com/2015/02/what-is-the-difference-between-ienumerable-and-iqueryable/ (see the last example) – mshwf Apr 17 '17 at 12:10
  • @MohamedAhmed yes, that example is wrong. When you apply LINQ extension it matters which object you pass to that extension. `db.Clients` is `IQueryable`. So `Take(5)` will be applied to queryable. SQL query will be executed on `ToList()` call and only results of this call will be referenced by `clients` variable. There are some other mistakes in this article too – Sergey Berezovskiy Apr 17 '17 at 12:35
  • Last question: Is your way the only way (most common way?) of getting `IEnumerable` list that fetch all records in contrast the `IQueryable`? (I couldn't have thought that all the fuss around the `IEnumerable` interface is about a method that I never used before or have seen other do!) – mshwf Apr 17 '17 at 12:43
  • @MohamedAhmed are you talking about `AsEnumerable()` extension? – Sergey Berezovskiy Apr 17 '17 at 12:44
  • at Sergey Berezovskiy, Yes – mshwf Apr 17 '17 at 12:45
  • 1
    @MohamedAhmed it's not the only way, but it's most common, because you'll be able to continue query construction without declaring separate `IEnumerabl` variable or putting queryable results into some in-memory collection like list. – Sergey Berezovskiy Apr 17 '17 at 12:47
  • 1
    Thanks very much, your answers are very useful – mshwf Apr 17 '17 at 12:48
2

The following line of code wouldn't fetch the records in memory, until you iterate over it or materialize it using ToList():

var data = context.Books.Where(x => x.Id > 930); // this does not immediately executes query

When you put ToList() in that case only the query will get executed on the server and result will be brought in to memory, which is the main difference your first and last code example.

The only thing to remember is making it IEnumerable will cause the query to be executed and all the results will be loaded in memory.

Ehsan Sajjad
  • 61,834
  • 16
  • 105
  • 160
1

IEnumerable is best suitable for In-Memory collection,that means data will be in you list already,you can query on that.

IQueryable is best suitable for Api calls with OData queries.It will prepare Sql or command.when we try to use result from query then only it will hit the database to fetch data.

SuryaBhaskar
  • 317
  • 1
  • 7