Entity Frame Performance Gotcha

Entity Frame Performance Gotcha:
I was doing some profiling of our Entity Framework stuff via EFProfiler the other day and noticed something really odd.  When I was watching the profiler I noticed that I had a query returning back over 1300 results and that was NOT right, I had expected only 1 result.  I looked the resulting sql and immediately I ‘thought’ I knew the issue.  I thought the issue was that I had not setup my where clause correctly in my EF… Turns out I was both right and wrong at the same time.
Below is a sample of my EF code
** note that I replaced the dynamic variables w/ static for testing inside of LinqPad **

When I run these statements above and look at the resulting sql in LinqPad I would expect to see 2 queries.  Sure enough I did have 2 of them.  What shocked me was that the 2nd query ONLY did a where clause filter on ClientID (see below)

This had a resulting output of below, which is the expected result

But if you run the sql generated above (2nd statement) you actually get 1327 results.  This tells me that indeed the ENTIRE result for a client was being pull out of the DB, pulled over the wire and filtered down on the client…This is not good.
Now I tried a few different things to see if I could get different results such as doing a .FirstOrDefault rather than a .Where… No dice
Finally one of my coworkers found this Stack Overflow link which talks directly to this issue.
Turns out this behavior is by design and I had never noticed it before.  I (and 3 of my coworkers mind you) had just assumed that when EF did a lazy load off of a previously fetched IQueryable it would include any filter statements and pass those to the SERVER.  But because the .Images is NOT a IQuerable but rather an EntitySet<T> it did not work as expected.
To solve this issue I simply just changed my statement to go directly at my EF context for the ONE row I wanted w/ both keys (client and type).  This solved my issue as well as give me a pretty significant performance enhancement.

Long story short is that if you are going to lazy gather data in EF make sure you are doing your filtering on the server rather than the client and make sure you understand that an EntitySet does NOT do this only an IQuerable does this.
Till next time,

DIGITAL JUICE