Derik Whittaker

Syndication

News


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 **

image

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)

image

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

image

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.

image

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,


Posted 02-12-2013 1:44 PM by Derik Whittaker
Filed under: , ,

[Advertisement]

Comments

Mickaël Derriey wrote re: Entity Frame Performance Gotcha
on 02-13-2013 11:09 AM

Hey Derik,

Nice catch!

Are you using ObjectSet/EntitySet API? I think so because Images would rather be an ICollection<T> if you were using the DbContext API.

If you use the latter in the future, there's another, yet very similar way to do this if you have access to the DbContext:

var images = myDbContext.Entry(client).Collection(x => x.Images).Query().Where(x => x.ImageTypeCode == 7020);

I like your posts, I thought I would bring something to this one.

Keep up the great work,

Mickaël

terry wrote re: Entity Frame Performance Gotcha
on 02-13-2013 8:10 PM

IQuerable  was spelled wrong throughout the post and should have been IQueryable but good post though.

Quinton wrote re: Entity Frame Performance Gotcha
on 02-14-2013 12:20 AM

Is the foreign key relationship correct between the client and images table, that could also cause it to return all the records...

Quinton wrote re: Entity Frame Performance Gotcha
on 02-14-2013 12:23 AM

Doesn't make sense why that gotcha even exists, thanks for sharing.

Salman wrote re: Entity Frame Performance Gotcha
on 02-14-2013 9:08 AM

I think the solution should have something like client.Images.where(...)

Its good.

bookmaring service wrote re: Entity Frame Performance Gotcha
on 03-15-2013 6:26 AM

mpI97L Major thankies for the blog post. Will read on...

buy social bookmarks wrote re: Entity Frame Performance Gotcha
on 03-24-2013 8:49 AM

nAIC9Z Major thankies for the blog.Thanks Again. Great.

Social bookmarks wrote re: Entity Frame Performance Gotcha
on 03-24-2013 5:03 PM

77JF4z I appreciate you sharing this blog article.Really looking forward to read more. Cool.

buy social bookmarks wrote re: Entity Frame Performance Gotcha
on 04-04-2013 2:53 AM

fEFi4t Enjoyed every bit of your blog post.Thanks Again. Cool.

buy social bookmarks wrote re: Entity Frame Performance Gotcha
on 04-04-2013 4:58 AM

Yq49mB A big thank you for your article.Really looking forward to read more. Fantastic.

funny shirts wrote re: Entity Frame Performance Gotcha
on 04-06-2013 2:50 AM

Say, you got a nice article.Really thank you! Fantastic.

grizzly bears wrote re: Entity Frame Performance Gotcha
on 04-06-2013 4:08 AM

I value the post.Thanks Again. Cool.

buy social bookmarks wrote re: Entity Frame Performance Gotcha
on 04-13-2013 5:20 PM

qL6oyH Thank you ever so for you blog post. Cool.

social bookmarking service wrote re: Entity Frame Performance Gotcha
on 04-13-2013 11:27 PM

T2cl4y Thanks again for the blog article.Really thank you!

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)