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?

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.

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);
    }
}

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
}