Returning IQueryable or Enumerated Object

Question

Hello everyone,

I was wondering about the performance difference between these two scenarios and what could the disadvantages be over each other?

First scenario :

public class Helper //returns IQueryable
{
   public IQueryable<Customer> CurrentCustomer
   {
      get{return new DataContext().Where(t=>t.CustomerId == 1);
   }
}

public class SomeClass
{
    public void Main()
    {
       Console.WriteLine(new Helper().CurrentCustomer.First().Name;
    }
}

The second scenario :

public class Helper //returns Enumerated result
{
   public Customer CurrentCustomer
   {
      get{return new DataContext().First(t=>t.CustomerId == 1);
   }
}

public class SomeClass
{
    public void Main()
    {
       Console.WriteLine(new Helper().CurrentCustomer.Name;
    }
}

Thanks in advance.

Answer 1

In short, using IQueryable is far better and allows you further filter the returned IQueryable down the path, without actually having the object or collection loaded into the memory. In this case, the return type is a simple Customer class and impact would be minimal, but in case of collections, you are strongly advised to use IQueryable. Chris Sells shows the problem in more depth here

Answer 2

Well, the main difference that I can see is when the query is executed and what else you can do with the query.

For example, suppose your Customer object has some large fields. Using the second approach, you will always fetch them. Using the first approach you could write:

string name = helper.CurrentCustomer.Select(x => x.Name).First();

That would then only need to query the single field in the database. In terms of timing, the query will only be executed when you actually request the data (which is how it's able to wait until after you've used Select to work out what to put in the query in the above case). That has pros and cons - it can make it harder to reason about, but it can save some work too. In terms of the "reasoning about" side, you know that once you've got a customer, you've got an object you can just work with. If you use the same queryable twice though, you need to know whether your LINQ query provider is going to cache the result... if you write:

IQueryable<Customer> currentCustomerQuery = helper.CurrentCustomer;
Customer x = currentCustomerQuery.First();
Customer y = currentCustomerQuery.First();

will that issue the query once or twice? I suspect it very much depends on the provider, but I wouldn't like to make any guesses about specific ones.

The other thing to think about is how easy it is to use the API you're building. Personally I'd normally find it easier to use an API which gives me the data I want rather than a query I can fetch that data from. On the other hand, it is slightly less flexible.

One option would be to allow both - have a GetCurrentCustomerQuery() and a GetCurrentCustomer() method. (I probably wouldn't make them properties myself, but that's merely a matter of personal preference.) That way you can get the flexibility you want when you really need it, but have a simple way of just getting the current customer as an object.

Answer 3

The difference between the methods is that the first one returns an expression that can return the object, whlie the second one has already executed the expression and returns the object.

In this exacty scenario the difference isn't very useful, and returning a single object as an expression is not very intuitive.

A scenario where the difference is more useful is if you have a method that returns several objects. The deferred execution of the expression means that you will only load the objects that you actually use. In the case that you only need the first few objects, the rest of the objects will not be created.