Commit linq IQueryable (as ToList (). AsQueryable () will do)

Question

Is there a way to freeze IQueryable so that no additional connections are added to the query when it gets into the database? For example, I could do .ToList() to freeze the request, but it affects performance because any filtering I do is average and I don’t have any benefit from pre-filtering on the db server?

---

Edit for clarity:

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

One of the options I was considering was setting: EnableQueryAttribute.AllowedQueryOptions to exclude Expand , however, even if my initial request was expanded, the client still could not select these parts.

Answer 1

Suppose you have an IQueryable<T> instead of an IQueryable .

If you do not want your client to have access to all IQueryable<T> methods, do not return IQueryable<T> . Since you want them to only be able to filter / sort / design, create an object that contains an IQueryable<T> , but only expose the necessary methods and use this:

 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 dependencies from hanging depending on your application. Any changes in the methods of IQueryable<T> will be contained in this class, and not spread everywhere.

Answer 2

So, I (think) tried the same thing, and the only solution I found is using TVF in SQL Server Manager. I accept EF 6.1.1 and Web API. Here are a few steps:

(1) Create a procedure (you can add parameters to it if you want):

 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 Initalizer with Database.SetInitializer in your DbContext constructor.

---

(2) Download this nuget package. It allows you to query stored procedures. Here is the basis of the project.

---

(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) Now you can use this function in your controller and perform pre-filtering. Subsequently, all Odata queries are still possible, but only based on the results of the sql procedure.

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

---

I hope that I understood correctly what you are asking.

Answer 3

It seems to me that the easiest way to do this is to turn IQueryable<T> into Func<List<T>> . Then you do not lose the lazy aspect, but you certainly remove the ability to do further joins in the database.

This is easy:

 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 is 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 it:

 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 the following:

  Before freeze
 After freeze
 Before invokes
 1
 2
 1
 2
 After invokes

You can see that it is lazy and that it only starts when called.