62

I'm getting into using the Repository Pattern for data access with the Entity Framework and LINQ as the underpinning of implementation of the non-Test Repository. Most samples I see return AsQueryable() when the call returns N records instead of List<T>. What is the advantage of doing this?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Keith Adler
  • 20,880
  • 28
  • 119
  • 189

5 Answers5

106

AsQueryable just creates a query, the instructions needed to get a list. You can make futher changes to the query later such as adding new Where clauses that get sent all the way down to the database level.

AsList returns an actual list with all the items in memory. If you add a new Where cluse to it, you don't get the fast filtering the database provides. Instead you get all the information in the list and then filter out what you don't need in the application.

So basically it comes down to waiting until the last possible momment before committing yourself.

Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
  • 8
    agree, but remember that beyond the "fast filtering", there is also the hit of sending all that info through the network. – eglasius Jul 09 '09 at 22:45
  • 2
    Also, its not the only way to do it, as u could receive the filters to apply instead of returning something that can be filtered. The first is easier to use if u later move from using a db to something else i.e. a web service or anything else. – eglasius Jul 09 '09 at 22:47
  • 17
    Another point: if you return an IQueryable you must find a good way to ensure that 1.) the context is open until the query gets executed and 2.) that the context will correctly disposed. Returning a list has the advantage that you can control the lifetime of the context inside a method. What fits better depends on the actual requirements. – Daniel Brückner Jul 09 '09 at 23:09
  • 2
    WRT @eglasius You can very easily take a predicate as a param to a repository method like `public GetMyEntities (Func pred)` or `public GetMyEntities (Func pred)` and then append that to the query like `return context.MyEntities.Where(pred).ToList();`. You should check that it's not null first, but you get the idea. Then when you consume it, you can apply the predicate in the call, rather than after: `repo.GetMyEntities(x => x.ThatProperty == false)'. This can make it more likely for non-Linq2Sql-compatible methods to toss exceptions, but each has tradeofs – JoeBrockhaus Mar 04 '15 at 20:32
  • 1
    @JoeBrockhaus what if you use `GetMyEntities (Expression> pred)` instead of `GetMyEntities(Func pred)`? – Jonathan Allen Aug 07 '18 at 03:51
26

Returning IQueryable<T> has the advantage, that the execution is defferer until you really start enumerating the result and you can compose the query with other queries and still get server side execution.

The problem is that you cannot control the lifetime of the database context in this method - you need an open context and must ensure that it stays open until the query gets executed. And then you must ensure that the context will be disposed. If you return the result as a List<T>, T[], or something similar, you loose deffered execution and server side execution of composed queries, but you win control over the lifetime of the database context.

What fits best, of course, depends on the actual requirements. It's another question without a single truth.

Daniel Brückner
  • 59,031
  • 16
  • 99
  • 143
13

AsQueryable is an extension method for IEnumerable<T> that could do two things:

  • If the IEnumerable<T> implements IQueryable<T> justs casts, doing nothing.
  • Otherwise creates a 'fake' IEnumerable<T> (EnumerableQuery<T>) that implements every method compiling the lambdas and calling to Enumerable extension methods.

So in most of the cases using AsQueryable is useless, unless u are forced to pass a IQueryable to a method and u have a IEnumerable instead, it's a hack.

NOTE: AsQueryable is a hack, IQueryable of course is not!

Olmo
  • 4,257
  • 3
  • 31
  • 35
  • 3
    AsQueryable is not useless, as the usecase you state "forced to pass as IQueryable" is a valid one, it allows you to use what IQueryable has to offer, and that is more than IEnumerable. I would not consider it a hack, and it has its uses, see for example here: http://wcf.codeplex.com/wikipage?title=Getting%20started:%20Building%20a%20simple%20web%20api . But I guess what you want to say is that it cannot be used to go deeper and influence how the Enumerable data is being produced (e.g. optimizing a query), it will only work on top of what gets/got created. – Evgeniy Berezovsky Sep 06 '11 at 04:58
4

Returning IQueryable<T> will defer execution of the query until its results are actually used. Until then, you can also perform additional database query operations on the IQueryable<T>; on a List you're restricted to generally less-efficient in-memory operations.

dahlbyk
  • 75,175
  • 8
  • 100
  • 122
-1
  • IQueryable: This is kind of deferred execution - lazy loading so your query is evaluated and hit to database. If we add any additional clause it will hit the Db with the required query with filters.
  • IEnumerable: Is eager loading thus records will be loaded first in-memory operation and then the operation will be performed.

So depending on use case like while paging on a huge number of records we should use IQueryable<T>, if short operation and doesn't create huge memory storage use IEnumerable.

Tim
  • 5,435
  • 7
  • 42
  • 62
Gsanto
  • 1
  • 4
  • First: eager and lazy has nothing to do with evaluating. Second: eager loading does not mean that records will be loaded first in-memory operation and then the operation will be performed. Every data has to be loaded into memory before operation will be performed. – Sebastian Xawery Wiśniowiecki Aug 22 '20 at 20:40