Freeze a linq IQueryable (as a ToList().AsQueryable() would do)

Question

Is there a way to freeze an IQueryable so that no additional joins will be added to the query when hitting the database? For example, I could do a .ToList() to freeze the query, but that has performance impacts because any filtering I do is on the middle layer and I don't have any performance gains from pre-filtering on the db server?

---

Edit for clarity:

I have an OData service which returns an IQueryable that the client can filter/sort/project as needed. I just want to prevent them from pulling more data out. I could do that by doing ToList().AsQueryable(), but that loses the advantage of lazyLoading, and with it, the whole purpose of allowing the client to filter the request.

One option that I looked at was to set: EnableQueryAttribute.AllowedQueryOptions to exclude Expand, however even if my initial Query had been expanded, the client is still prevented from selecting those parts.

Answer 1

Im assuming that you actually have an IQueryable<T> instead of a IQueryable.

If you don't want your client having access to all IQueryable<T> methods, don't return an IQueryable<T>. Since you want them to be able to only filter/sort/project, create a object witch contains an IQueryable<T> but only expose the desired methods, and use that:

public interface IDataResult<T>
{
    IDataResult<T> FilterBy(Expression<Func<T, bool>> predicate);

    IDataResult<TResult> ProjectTo<TResult>(Expression<Func<T, TResult>> predicate);

    IDataResult<T> SortBy<TKey>(Expression<Func<T, TKey>> keySelector);

    IDataResult<T> SortByDescending<TKey>(Expression<Func<T, TKey>> keySelector);

    List<T> ToList();

    IEnumerable<T> AsEnumerable();
}

public class DataResult<T> : IDataResult<T>
{
    private IQueryable<T> Query { get; set; }

    public DataResult(IQueryable<T> originalQuery)
    {
        this.Query = originalQuery;
    }

    public IDataResult<T> FilterBy(Expression<Func<T, bool>> predicate)
    {
        return new DataResult<T>(this.Query.Where(predicate));
    }

    public IDataResult<T> SortBy<TKey>(Expression<Func<T, TKey>> keySelector)
    {
        return new DataResult<T>(this.Query.OrderBy(keySelector));
    }

    public IDataResult<T> SortByDescending<TKey>(Expression<Func<T, TKey>> keySelector)
    {
        return new DataResult<T>(this.Query.OrderByDescending(keySelector));
    }

    public IDataResult<TResult> ProjectTo<TResult>(Expression<Func<T, TResult>> predicate)
    {
        return new DataResult<TResult>(this.Query.Select(predicate));
    }

    public List<T> ToList()
    {
        return this.Query.ToList();
    }

    public IEnumerable<T> AsEnumerable()
    {
        return this.Query.AsEnumerable();
    }
} 

That way you can also prevent EF and DB related dependencies creeping up on your application. Any change on IQueryable<T> methods will be contained within this class, instead of spread all over the place.

Answer 2

So I (think) tried the same and the only solution I found is using TVF in SQL Server Manager. I am assuming EF 6.1.1 and Web API. Here are some steps to take:

(1) Create a Procedure (you can add parameters to it if you like):

CREATE PROCEDURE [dbo].[GetArticleApiKey] @a nvarchar(max) AS
SELECT  a.*
FROM    [dbo].[Blogs] b, [dbo].[Articles] a
WHERE   b.ApiKey = @a AND b.Id = a.Blog_Id

If you need this in Code-First you can use a a Initalizer with Database.SetInitializer in your DbContext constructor.

---

(2) Download this nuget package. It enables Stored Procedures that are queryable. Here is the project base.

---

(3) Add the following to your DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Conventions.Add(new FunctionsConvention<YourContext>("dbo"));
}

---

(4) Add the stored procedure to your context:

public ObjectResult<Article> GetArticleApiKey(string apiKey)
{
 var apikeyParameter = new ObjectParameter(ApiKeyParameter, apiKey); // Make sure to validate this, because of sql injection
 ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Article>("GetArticleApiKey", apikeyParameter);
}

---

(5) You can now use this function in your controller and do a pre-filtering. Afterwards all Odata queries are still possible but only on the result of the sql-procedure.

context.GetArticleApiKey("MyApiKey").AsQueryable();

---

Hope I got right what you asking for.

Answer 3

It seems to me that the simplest way to do this is to turn the IQueryable<T> into an Func<List<T>>. You then don't lose the lazy aspect, but you certainly remove the ability to do further joins on the database.

This is how easy it is:

public static class FreezeEx
{

    public static Func<List<R>> Freeze<T, R>(this IQueryable<T> queryable, Expression<Func<T, R>> projection)
    {
        return () => queryable.Select(projection).ToList();
    }

    public static Func<List<R>> Freeze<T, R>(this IQueryable<T> queryable, Expression<Func<T, bool>> predicate, Expression<Func<T, R>> projection)
    {
        return () => queryable.Where(predicate).Select(projection).ToList();
    }
}

Then you can do this:

IQueryable<int> query = ...;

Func<List<int>> frozen = query.Freeze(t => t > 10, t => t);

List<int> results = frozen.Invoke();

---

Here's some basic code so that this can be tested:

public IEnumerable<int> Test()
{
    Console.WriteLine(1);
    yield return 1;
    Console.WriteLine(2);
    yield return 2;
}

Now I call this:

IQueryable<int> query = Test().AsQueryable();

Console.WriteLine("Before Freeze");
Func<List<int>> frozen = query.Freeze(t => t > 10, t => t);
Console.WriteLine("After Freeze");

Console.WriteLine("Before Invokes");
List<int> results1 = frozen.Invoke();
List<int> results2 = frozen.Invoke();
Console.WriteLine("After Invokes");

On the console I get this:

Before Freeze
After Freeze
Before Invokes
1
2
1
2
After Invokes

You can see it is lazy and that it only runs when invoked.