return Queryable<T> or List<T> in a Repository<T>

Question

Currently I'm building an windows application using sqlite. In the data base there is a table say User, and in my code there is a Repository<User> and a UserManager. I think it's a very common design. In the repository there is a List method:

//Repository<User> class
public List<User> List(where, orderby, topN parameters and etc)
{
    //query and return
}

This brings a problem, if I want to do something complex in UserManager.cs:

//UserManager.cs
public List<User> ListUsersWithBankAccounts()
{
    var userRep = new UserRepository();
    var bankRep = new BankAccountRepository();
    var result = //do something complex, say "I want the users live in NY 
                 //and have at least two bank accounts in the system
}

You can see, returning List<User> brings performance issue, becuase the query is executed earlier than expected. Now I need to change it to something like a IQueryable<T>:

//Repository<User> class
public TableQuery<User> List(where, orderby, topN parameters and etc)
{
    //query and return
}

TableQuery<T> is part of the sqlite driver, which is almost equals to IQueryable<T> in EF, which provides a query and won't execute it immediately. But now the problem is: in UserManager.cs, it doesn't know what is a TableQuery<T>, I need to add new reference and import namespaces like using SQLite.Query in the business layer project. It really brings bad code feeling. Why should my business layer know the details of the database? why should the business layer know what's SQLite? What's the correct design then?

Reservist

Could you not "just" add a new layer of abstractions (interfaces) between the business layer and data access layer? Yes, that would require a new set of objects representing the same data in the business layer, and mapping between these and those in the DA layer - in a sense, code duplication - but it should give you a cleaner cut between the layers, if I understand your situation correctly. – Chaille

I agree with @Kjartan, what's you really wanted is IQueryable<T>, it's a pity SQLite haven't one, it's worth to create a another layers to tranfer to correct type. – Gros

@Chaille @ivenxu: I didn't say TableQuery<T> is incorrect, nor IQueryable<T> is correct. Even I added a new transfer interface, like IQuery<T>, it is still base on TableQuery<T>, and when I use IQuery<T> in my business layer, everything is the same. It just looks unrelated with SQLite but in fact, it's wearing a mask. – Reservist

It's not entirely clear to me what you mean by base on here. They may look and work the same, but if you do the separation by interfaces wisely, there should be no explicit reliance on SQLite from your business layer. It should just be able to make a call GetTheStuffIWant() to the DA layer, and not have to know anything about it's implementation (or am I missing something?). On the other hand, @DennisTraub may be right in recommending some loading strategy in the repository instead, or some kind of caching, if you don't absolutely need to call the DB every time. – Chaille

I think the best fix is to improve sqlite-net so that TableQuery implements IQueryable so you can then use it. But there may also be a reason they skimped and only implemented IEnumerable instead. – Ricci

@DannyChen how did you end up handling this? I am running into this same issue now, and hoping to find a way to query multiple repositories, but without having to load the entire tables into memory first. – Occidental

@dubstylee I think TableQuery<T> implements IEnumerable<T> so you can use the interface in the method signature. See the answers below. – Reservist

Answer 1

I'd recommend you to use IEnumerable<T> rather than IQueryable<T>, which allows lazy loading too. IEnumerable does not, however, imply you can query the data in any way. Your DB LINQ Provider will probably have a reduced feature set.

Tyner

Answer 2

Typically, in a clean architecture, data query logic is encapsulated in repositories. Use of Pipes and Filters can help reuse query logic. Wrapping these with methods in data-layer/repositories will be more readable and maintainable plus re-usable.

For instance, Pipes and filters to query users:

/// Pipes/Filters for user queries.
public static class UserExtensions
{
    public static IQueryable<User> Active(this IQueryable<User> query)
    {
        return query.Where(user => user.Active == true);
    }
}

public class UserRepository : IRepository<User>, IUserRepository
{
    /// Retrieve all users
    public List<User> List()
    {
        // Logic to query all users in the database.
    }
    public List<User> ListActive()
    {
        // Logic to query all active users in the database.
        return context.Users.Active().ToList();
    }
}

Complex queries requires the understanding of it's purpose and responsibilities to abstract the query logic to it's repositories. For instance, 'Get all accounts belongs to this user" can be written in AccountRepository class as List<Account> ListForUser(int userId) { }.

Edit: Based on the comments, here is the scenarios to write a search query that retrieves Users lives in LA who have at least 2 accounts.

public class UserRepository : IRepository<User>, IUserRepository
{
    // other queries.

    public List<User> List(ISearchQuery query)
    {
        // Logic to query all active users in the database.
        return context.Users.Active().LivesIn(query.Country).WithAccounts(query.AccountsAtLeast).ToList();
    }
}

public static class UserExtensions
{
    // other pipes and filters.

    public static IQueryable<User> LivesIn(this IQueryable<User> query, string country)
    {
        return query.Where(user => user.Country.Name == country);
    }
    public static IQueryable<User> WithAccounts(this IQueryable<User> query, int count)
    {
        return query.Where(user => user.Accounts.Count() >= count);
    }
}

Kimon

Where will you put the logic for the requirement: "query the users live in NY and have at least two bank accounts in the system". – Reservist

Those queries are written in Repository and ofcoz you can refactor accordingly. And yes, there is a limitation too since not all query scenarios can be parsed by a single sql query in EF. In such cases we will have to come up with alternative approaches. – Elisabetta

Answer 3

Since TableQuery<T> implements IEnumerable<T>, and not IQueryable<T>, the best solution would be to simply change your repository interface to return IEnumerable<T> instead of TableQuery<T>. This not only breaks the explicit client dependency with your SqlLite library, but it is also a better design to use an abstraction (IEnumerable<T>) instead of an implementation (TableQuery<T>) in your interfaces.

Your example method should look like this:

//Repository<User> class
public IEnumerable<User> List(where, orderby, topN parameters and etc)
{
    //query and return
}

Ariellearies

Answer 4

Lazy loading can be a PITA and I'd rather try to inject a loading strategy into the repository instead of trying to fiddle with the query object in my application or business layer. Especially when the query object forces me to tightly couple the layers.

Gearard

What do you mean by "loading strategy"? – Benoit