0

Lets say I have these three methods:

public Customer GetCustomerByCustomerGuid(Guid customerGuid)
{
    return GetCustomers().FirstOrDefault(c => c.CustomerGuid.Equals(customerGuid));
}

public Customer GetCustomerByEmailAddress(string emailAddress)
{
    return GetCustomers().FirstOrDefault(c => c.EmailAddress.Equals(emailAddress, StringComparison.OrdinalIgnoreCase));
}

public IEnumerable<Customer> GetCustomers()
{
    return from r in _customerRepository.Table select r;
}

//_customerRepository.Table is this:
public IQueryable<T> Table
{
    get { return Entities; }
}

Would this cause a query to the database each time I make a call to GetCustomerByEmailAddress() / GetCustomerByCustomerGuid() or would EF cache the results of GetCustomer() and query that for my information?

On the other hand, would it just cache the result of each call to GetCustomerByEmailAddress() / GetCustomerByCustomerGuid()

I am trying to establish the level of manual caching I should go to, I really dislike running more SQL queries than are absolutely necessary.

webnoob
  • 15,747
  • 13
  • 83
  • 165

2 Answers2

4

Yes, it'll query the database every time.

Your concern should be to optimize your code so that it only queries for and returns the record you need. Right now it's pulling back the entire table and then you're filtering it with FirstOrDefault().

Change public IEnumerable<Customer> GetCustomers() to public IQueryable<Customer> GetCustomers() to make it more efficient.

Anthony Chu
  • 37,170
  • 10
  • 81
  • 71
  • 1
    That's not how LINQ to EF works. The LINQ Provider is smart enough to translate `FirstOrDefault()` into `select top 1`. Basically, any query methods used for a LINQ to EF query that have equivalent SQL functions will be executed as SQL in favor of operating on the result set in C#. – evanmcdonnal Feb 11 '14 at 22:46
  • It would if it were returning an IQueryable rather than IEnumerable. – Anthony Chu Feb 11 '14 at 22:47
  • @evanmcdonnal - That was my understanding as well. The query is generated based on the full LINQ query. – webnoob Feb 11 '14 at 22:48
  • @AnthonyChu - In my case `_customerRepository.Table` is `IQueryable<>` but the poster would not of known that. – webnoob Feb 11 '14 at 22:48
  • @AnthonyChu how do you know the type returned by `GetCustomers()`? – evanmcdonnal Feb 11 '14 at 22:48
  • It's in the code listing above. It needs to be changed to `public IQueryable GetCustomers()` – Anthony Chu Feb 11 '14 at 22:50
  • @AnthonyChu - Would the call to `.Table` (see my edit) make this Ok as it returns `IQueryable` or do both need to return it? – webnoob Feb 11 '14 at 22:52
  • @AnthonyChu oh I see what you're saying and you're actually right. However, I wouldn't suggest changing the `GetCusomters()` method, I would suggest getting rid of it because it's completely useless and worst case, ensures you have bad performance by causing only the code within that method to be run in SQL while the other methods that operate on it are just using LINQ to Objects on the initial result. – evanmcdonnal Feb 11 '14 at 22:52
  • Details here on IEnumerable vs IQueryable... http://stackoverflow.com/a/2876655/3199781 – Anthony Chu Feb 11 '14 at 22:53
  • Interesting. You are not only ignoring Anthony's advice but someone even downvoted his correct answer. – Stilgar Feb 11 '14 at 22:54
  • Also, to my point. Why create a helper method that is only 1 line? How is that helpful? – evanmcdonnal Feb 11 '14 at 22:54
  • @Stilgar that was my fault but I cannot unvote unless he edits :( – evanmcdonnal Feb 11 '14 at 22:54
  • @Stilgar - Querying advice isn't ignoring it (and I am unsure why you thought I was), I just won't jump at every answer straight away. Based on the comments here though, it all makes sense. – webnoob Feb 11 '14 at 22:56
  • @evanmcdonnal - You are right, the `GetCustomers()` is kinda useless at the moment. The reason I made it was to become a wrapper for getting cached customers as well (I have a comment in my local code to do so but ommitted from the question) – webnoob Feb 11 '14 at 22:57
  • Sorry, it's my bad. I should have provided more details. – Anthony Chu Feb 11 '14 at 22:57
  • @webnoob if your intent is to cache the result I suggest adding a property to the class, in the getter you can check if the reference is null and execute the query if it is (lazy loading) otherwise, return it. Then everywhere in your code just use that property as the starting point of your query and get rid of `GetCustomers()`. If you want to get results every time, get rid of `GetCusomers()` and begin every query with the code in `GetCustomers()`. – evanmcdonnal Feb 11 '14 at 22:59
1

It will result in a call to the database every time. I actually asked a similar question earlier today and you can see more here: Why does Entity Framework 6.x not cache results?

Community
  • 1
  • 1
Adam Modlin
  • 2,994
  • 2
  • 22
  • 39