locked
POCO lazy loading question ? RRS feed

  • Question

  • Hello,

    here is my scenario;

    - I am caching all data from my database. my Customers object has respectively following sub objects as region/country

          CustomerCache = (From item In myDBContext.Customers.AsNoTracking).ToList()

    - then when I try to access that shared (static) property in other class like below ,

    myCustomers = (From item In myCacheData.CustomerCache Where item.Customer_Name = Customer_Name And (item.Region.Country.Country_ID = country_ID)).ToList()

    this query takes long because I believe region,country load causing it. I am not sure if it is load or entity F. does something else, this is my question anyway because after cache is done, when I observe my customercache object, i can see for each customer, region, country objects are filled correctly. that means 2nd query doesnt reload, does it? I profiled, no sql query coming for region and counrty.

    If I change that query to be inner join like below, it is pretty fast. 

     

     Dim query = From item In myCacheData.CustomerCache
       Join r In myCacheData.RegionCache On r.Region_ID Equals item.Region_ID
       Join c In myCacheData.CountryCache On c.Country_ID Equals r.Country_ID
       Where c.Country_ID = country_ID
    

     

    Can you explain why the first one takes too long and what action does it happen behind the scenes?

     

    Thanks


    "Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."

    Wednesday, April 20, 2011 5:31 PM

Answers

  • Hello,

    the first one takes too long because it is known as N+1 problem. The scenario is that you are iterating every customer in your cache and accessing his Region and Region's country so for each customer EF first triggers separate query to get the region and then triggers another separate query to get Region's country. So if you have 1000 customers your first approach will trigger 2000 queries. That is incorrect usage of lazy loading.

    Your second example uses just single query to get all data. You can also use direct eager loading by calling:

    CustomerCache = (From item in myDBContext.Customers.Include("Region.Country").AsNoTracking()).ToList()

    Btw. caching proxied entities is not a good idea. Once you disposet the context each request to lazy loading will throw an exception. You should turn off proxy creation for entities which should be cached. To  turn off proxy creation use ContextOptions.ProxyCreationEnabled and set it to false.

    Best regards,
    Ladislav

    • Marked as answer by emil_tr Thursday, April 21, 2011 12:47 PM
    Wednesday, April 20, 2011 8:50 PM
  • Well, If you debug the code you are triggering lazy loading for each record you are accessing. The query is executed to database. EF doesn't fetch anything until you explicitly say that you want it which is either done by accessing the property = lazy loading or by using Include method = eager loading.

    If you have proxy creation enabled every loaded entity is proxied.

    Best regards,
    Ladislav

    • Marked as answer by emil_tr Thursday, April 21, 2011 12:47 PM
    Thursday, April 21, 2011 11:22 AM

All replies

  • Hello,

    the first one takes too long because it is known as N+1 problem. The scenario is that you are iterating every customer in your cache and accessing his Region and Region's country so for each customer EF first triggers separate query to get the region and then triggers another separate query to get Region's country. So if you have 1000 customers your first approach will trigger 2000 queries. That is incorrect usage of lazy loading.

    Your second example uses just single query to get all data. You can also use direct eager loading by calling:

    CustomerCache = (From item in myDBContext.Customers.Include("Region.Country").AsNoTracking()).ToList()

    Btw. caching proxied entities is not a good idea. Once you disposet the context each request to lazy loading will throw an exception. You should turn off proxy creation for entities which should be cached. To  turn off proxy creation use ContextOptions.ProxyCreationEnabled and set it to false.

    Best regards,
    Ladislav

    • Marked as answer by emil_tr Thursday, April 21, 2011 12:47 PM
    Wednesday, April 20, 2011 8:50 PM
  • Hi Ladislav,

    Is every POCO entity also called Procied entity? I cant just understand something. when that line 

     CustomerCache = (From item In myDBContext.Customers.AsNoTracking).ToList() is executed, when I debug I can see already for each customer which region and country they have. that means I fetched already data from DB, am I wrong? If I am wrong, how and why do I see the entries?

    "EF first triggers separate query to get the region and then triggers another separate query to get Region's country. "

    query to DB or Memory? If memory, shouldn't it be fast. I am asking because It is slow as if it sends query to DB but when I profile with SQL profiler. there isnt any query coming. That's why I am wondering, when are these sub objects are fetched from DB? 

    thanks.


    "Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."
    Thursday, April 21, 2011 8:35 AM
  • Well, If you debug the code you are triggering lazy loading for each record you are accessing. The query is executed to database. EF doesn't fetch anything until you explicitly say that you want it which is either done by accessing the property = lazy loading or by using Include method = eager loading.

    If you have proxy creation enabled every loaded entity is proxied.

    Best regards,
    Ladislav

    • Marked as answer by emil_tr Thursday, April 21, 2011 12:47 PM
    Thursday, April 21, 2011 11:22 AM