Should a Repository return IEnumerable<T> , IQueryable<T> or List<T>? [closed]

Question

I'd like to make my application as flexible as possible, but not dig myself into a hole by making my Interface too specific.

What is the best object type for a repository? IEnumerable, IQueryable, or List?

The technologies I'm considering using are

• Azure App Fabric Caching

• Entity Framework 4.1

• Possibly Windows Server AppFabric

Damiano

Answer 1

I would say build your DAL using IQueryable, and pass it around, make sure your object contexts lifetime is the request. This way you will get benefit of delayed execution, but are exposed to the risk of inefficient querying of database.

Then make sure you performance test your application( or at least the parts that are most likely to get traffic) and see the data access patterns. Create specialized methods in your DAL to retrieve fully materialized objects and make these queries as pre compiled queries.

a sample of the repository interface would be like

  public interface IDataContext
  {
        void Add<T>(T entity) where T : BaseEntity;
        void Delete<T>(T entity) where T : BaseEntity;
        IQueryable<T> Find<T>(Expression<Func<T, bool>> where) where T : BaseEntity;
   }

where BaseEntity is the base class to all our classes, it looks like, this class is not mapped to any table in DB

public abstract class BaseEntity
{
        public int Id { get; set; }
        public DateTime CreateDateTime { get; set; }
        public string CreateUser { get; set; }
        public DateTime ModDateTime { get; set; }
        public string ModUser { get; set; }
        public byte[] RowVersion { get; set; }
}

Expression<Func<T, bool>> would pass the whole expression to your repository instead of just Func, since EF works on expression to generate SQL query, a typical use would be

ICollection<WFGroup> wgGroups = this.dataContext.Find<WFGroup>((w) => true).ToList();

where WFGroup is a class derived from BaseEntity, I typically use lazy loading and proxy and don't detach/attach objects to a context.

Dumortierite

I'm using a caching service that requires a materialized query. IEnumerable<T> is the best common denominator I can find. Does this mean I have a layer on top of my IQueryable<T> DAL that flattens everything to IEunmerable<T>? If so what is <T>? Entity model, view, or viewmodel? – Damiano

IEnumerable is no guarantee of materialized object IQueryable is also IEnumerable. T in my case is Entity,we have an interface IDataContext that the repository implements and every method is generic T where T is constrained to our base entity. – Dumortierite

Interesting, I see how it can work for a 1:1 table to entity mapping, but I'm curious how you handle more complex things like 1:Many relationships... https://mcmap.net/q/585919/-what-does-a-repository-look-like-when-using-many-1-many-or-many-many-tables/328397 – Damiano

i updated the answer with a sample interface, 1:n is handled in the entity model. This was a little hard to implement in EF 4 with a modified t4 but with EF 4.2 its a breeze – Dumortierite

Thanks, that looks like the direction I should go. I've only read about Expression<Func<T, bool>> but not sure how to use it. Can you add one more sample? Also, just so I understand BaseEntity could be something like Students or EnrolledClasses right? – Damiano

I think this answer is now outdated and wrong advice. IQueryable should not be just passed around for a number of reasons, one that it is dangerous in terms of potential DB abuse, but more to do with SoC. I've posted an answer that explains what I mean, with reference to a recent article on the matter. – Outstand

Answer 2

It depends on whether you wish to have any future queries performed on the entity and whether these should be in memory or not:

• If there are to be future queries and the DB should do the work return IQueryable.
• If there are to be future queries and it is to be done in memory return IEnumerable.
• If there are to be no further queries and all the data will need to be read return an IList, ICollection etc.

Outburst

Where is it OK to issue queries based on this past the DAL? Should I pass IQueryable to the controller? The View? – Damiano

You can still cause queries to run in the DB if you use IEnumerable – the documentation for IQueryable says it's intended for implementing query providers, not to make this distinction. I prefer not using IEnumerable once querying the database is finished, because using a collection type makes sure you're returning materialised results and not the unevaluated query by mistake. – Heavyhearted

This sort of question either relates to performance tuning, in which case performance should be measured with all considered options (and the most performant chosen) or down to architectural considerations. In the case of the latter I'd suggest exposing an IQueryable to the controller is acceptable (since controller knowledge of domain objects is okay), but the view / view model should be dumb and not have knowledge of such things... But this is only my opinion! – Uproarious

@makerofthings7: In a strict layered architecture, it's not OK to issue DB-backed queries anywhere outside the DAL. It's OK to do in-memory queries to transform data structures anywhere and you can't prevent it anyway since all collections can be queried in memory. – Heavyhearted

I don't think IQueryable should ever be returned, and I've posted an answer that covers why. – Outstand

Answer 3

There is a very good recent article here that covers this, namely under the heading "Repositories that return IQueryable". This is what it says:

One of the reasons we use the repository pattern is to encapsulate fat queries. These queries make it hard to read, understand and test actions in ASP.NET MVC controllers. Also, as your application grows, the chances of you repeating a fat query in multiple places increases. With the repository pattern, we encapsulate these queries inside repository classes. The result is slimmer, cleaner, more maintainable and easier-to-test actions. Consider this example:

var orders = context.Orders
    .Include(o => o.Details)
    .ThenInclude(d => d.Product)
    .Where(o => o.CustomerId == 1234);

Here we are directly using a DbContext without the repository pattern. When your repository methods return IQueryable, someone else is going to get that IQueryable and compose a query on top of it. Here’s the result:

var orders = repository.GetOrders()
    .Include(o => o.Details)
    .ThenInclude(d => d.Product)
    .Where(o => o.CustomerId == 1234);

Can you see the difference between these two code snippets? The only difference is in the first line. In the first example, we use context.Orders, in the second we use repository.GetOrders(). So, what problem is this repository solving? Nothing!

Your repositories should return domain objects. So, the GetOrders() method should return an IEnumerable. With this, the second example can be re-written as:

var orders = repository.GetOrders(1234);

See the difference?

As a result of this, I've added the following coding convention within my team:

For repository class methods, never return a IQueryable object. Always enumerate or convert it first (e.g., ToArray, ToList, AsEnumerable).

The reasoning being that IQueryable would allow the caller to build on this and ultimately modify the SQL query that is executed on the database. This can potentially be dangerous in terms of DB performance, but it is more about SoC. The caller doesn’t care about the data source; it just wants the data.

Outstand

Now you can use compiled queries for this. – Ester

Answer 4

How likely are you to ever need to return a custom implementation of IEnumerable (not a collection) from your DAL? (To answer this question, look at your previous projects and count how many of those or of yield returns you have around.)

If the answer is "not very", I'd just return ICollection or even arrays (if you want to prevent the query results from being inadvertently modified.) In a pinch, if you ever need to change a query to "stream" results with a custom IEnumerable, you can always have the old method call the new one and materialise the results to keep compatibility with older clients.

Heavyhearted