To Return Iqueryable<T> or Not Return Iqueryable<T>

To return IQueryableT or not return IQueryableT

The pros; composability:

• callers can add filters
• callers can add paging
• callers can add sorting
• etc

The cons; non-testability:

• Your repository is no longer properly unit testable; you can't rely on a: it working, b: what it does;
  
  
  • the caller could add a non-translatable function (i.e. no TSQL mapping; breaks at runtime)
  • the caller could add a filter/sort that makes it perform like a dog
• Since callers expect IQueryable<T> to be composable, it rules out non-composable implementations - or it forces you to write your own query provider for them
• it means you can't optimize / profile the DAL

For stability, I've taken to not exposing IQueryable<T> or Expression<...> on my repositories. This means I know how the repository behaves, and my upper layers can use mocks without worrying "does the actual repository support this?" (forcing integration tests).

I still use IQueryable<T> etc inside the repository - but not over the boundary. I posted some more thoughts on this theme here. It is just as easy to put paging parameters on the repository interface. You can even use extension methods (on the interface) to add optional paging parameters, so that the concrete classes only have 1 method to implement, but there may be 2 or 3 overloads available to the caller.

Return IQueryableT from my method? Is it the right way to do it?

As it stand it makes no sense. If you want to use Queryable methods to remote queries into the database you must use expression trees. Using Compile converts a tree to a delegate which destroys this opportunity.

_entities must be an IQueryable<T> in order to target Queryable methods.

AsQueryable is a code smell that usually indicates the mistakes describe above. This is a fake queryable. It is in-memory (except if the source is really IQueryable; then it does a cast).

Make Sure Application Layer does not Return IQueryable

Asnwers are based on questions without Minimal, Reproducible Example and may not be accurate.

We have a Data Layer, Repository, and then Application layer. Is there
way to prevent an Application layer project from returning any
IQueryable?

Assuming application layer refers to ASP.NET Core controllers and the problem is that queryable source is disposed before actual query is executed.

Option 1:

Do not return IQueryable<T> from repository. IMHO, repository suppose to return materialized query, IEnumerable<T>.

In case you are dealing with large project and repository might become huge mess, you may consider different approach.

Option 2:

Inherit ControllerBase and/or Controller and override every single method accepting model and make sure passed model is not IQueryable, otherwise execute query and pass result to base method. 20 methods in ControllerBase and 4 methods in Controller class.

API example:

[Route("api/[controller]")]
[ApiController]
public class HomeController : ControllerBase
{
    public IActionResult Get() {
        using (var context = new MaterializeQueryContext()) {
            return Ok(context
                .MaterializeQuery
                .Where(x=> x.Id > 1));
        }
    }

    public override OkObjectResult Ok(object model) {
        if (model is IQueryable queryable) {
            var toList = typeof(Enumerable)
                .GetMethod(nameof(Enumerable.ToList))
                .MakeGenericMethod(new[] { queryable.ElementType });

            var result = toList.Invoke(null, new[] { queryable });

            return base.Ok(result);
        }

        return base.Ok(model);
    }
}

MVC example:

public class HomeController : Controller {
    public IActionResult Index() {
        using (var context = new MaterializeQueryContext()) {
            return View(context
                .MaterializeQuery
                .Where(x => x.Id > 1));
        }
    }

    public override ViewResult View(object model) {
        if (model is IQueryable queryable) {
            var toList = typeof(Enumerable)
                .GetMethod(nameof(Enumerable.ToList))
                .MakeGenericMethod(new[] { queryable.ElementType });

            var result = toList.Invoke(null, new[] { queryable });

            return base.View(result);
        }

        return base.View(model);
    }
}

Downsides:

• Reflection will add some overhead
• It may be buggy in more complex scenarios
• ToList method invocation will be blocking thread
• Adding async support is (probably) impossible or it will be too complex
• Non covered scenarios (example below)

For example returning result directly.

public IActionResult Get() {
    using (var context = new MaterializeQueryContext()) {
        return new OkObjectResult(context
            .MaterializeQuery
            .Where(x=> x.Id > 1));
    }
}

Currently we code review all return methods, review proper
architecture, and ensure they tag ToList() at end when needed.

I would suggest instead of reviewing code to sit down and write custom Roslyn Analyzers to avoid repeated tasks that can be solved once. Also you can write code fixes for those scenarios to make it even easier to fix.

You can find already made analyzers and code fixes on GitHub.

.NET Analyzers

awesome-analyzers

Is there an option in Visual studio Builds, Xunit option, or Post
execute Design method to ensure all calls are not IQueryable, but
converted to IEnumerable, IList, etc?

Yes, it is possible to use Roslyn Syntax Transformation.

Hope it helps!

How to return IQueryableT for further querying

You need to "move up" the scope of your data context:

public IQueryable<T> MyProductDataSource.FindAllProducts(MyCtx context)
{
    return context.MyList().Where( .... );
}

Then create the context at the larger scope:

using (var ctx = new MyCtx())
{
    var products = MyProductDataSource.FindAllProducts(ctx);

    var pageNumber = page ?? 1;
    var onePageOfProducts = products.ToPagedList(pageNumber, 25);
}

Return IQueryableT as IEnumerableT will cause in database call?

Not if you don't do anything with it, no.

However, if you try to iterate over the results (or call Count() etc) then it will try to make a database call... and I'd expect it to then fail, because you've disposed of the context at that point.

Is it possible to return IQueryableSomeClass to a IQueryableT method?

Agree with the comment on design principles by Adrian, but if you want to do it this way

public IQueryable<T> QueryableEntities
{
    get
    {
        if (typeof(T).Equals(typeof(Model.Competition)))
        {
            return this.ConvertToCompetitionList(
                  this.GetTable<Storage.Competition>()).AsQueryable() as IQueryable<T>;
        }
         else if(typeof(T).Equals(typeof(Model.Submission)))
        {
            return this.ConvertToSubmissionList(
                   this.GetTable<Storage.CompetitionEntry>()).AsQueryable() as IQueryable<T>;
        }
    }
}

will compile and run fine. Here the x as IQueryable<T> gives x cast to IQueryable<T> if it can be cast and null if it can't.

Note: this means that when you construct your generic class if it has type Model.Competition it will return IQueryable<Model.Competition>, if it has type Model.Submission it will return IQueryable<Model.Submission>, and will return null for any other type.

How to return IQueryable?

Fundamentally, you've got limited options here;

1. get hold of a full query provider - something like EF - and use that:

   public IQueryable<TUSer> Users => _myDbContext.Users;
   

2. load all the users into something like a List<T>, and use AsQueryable() - this will spoof the query API using LINQ-to-Objects, and will force it to load all the users into memory, but is simple:

   public IQueryable<TUser> Users => LoadThemAll().AsQueryable();
   

3. roll your own query provider that knows how to convert expression trees into executable queries for your RDBMS; this is a huge amount of work, where just the overview and explanations (not the work itself) would fill a modest sized book

Order of usual preference is order listed. Alternatively, limit yourself to IUserStore<T> (not IQueryableUserStore<T>), and get the list of users in some other manner.

---

Related Topics