none
what is the best way to use lazy loading or table join is better? table splitting is efficient?

    Question

  • Hello,

    I have 4 tables  country- state-city and office. 

    those 4 are static DB tables and I load (cache) them into the memory as shared EF classes.

    then when I need to access for example county of the office, while I do lazy loading before I cache them into memory, I can simply acces like myOffice.city.state.country.country_name e.g.

    But actually since I do lazy loading for my offices, I increase my memory load in the cache. What I decided later is, I have so many properties of city, state and country, so I reduced in another class I called them city_small, state_small e.g.

    then I mapped them for lazy loading but I still use original large classes and I cache them separately. anyway, finally, that didnt work, while I learnt EF works only with 1 to 1 relationship on DB. I got the following error;

    "System.InvalidOperationException: The entity types 'City_Small' and 'City' cannot share table 'City' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them."

    my questions are; 

    1) Is there a work around on that? I read inheriting or table splitting. but splitting or inheriting probably causes double DB queries? not efficient?

    2) Would you recommend that dont do lazy loading but use join query with linq while I load FK tables anyway in the memory as well? will this be faster? (lazy loading i do only 1 time on app load until  IIS reset)

    3) using EF doesn't look very efficient because we cant handle to fetch columns we want to but we are forced to fetch entire table every time? am I wrong with that?

    thanks for your help.


    "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."

    Tuesday, March 06, 2012 12:59 PM

Answers

  • Hi emil_tr,

    Welcome to MSDN Forum.

    Based on the post, my understanding is, there are 4 tables in the database, but the properties of these tables are too many, and you don’t
    want to load all the properties into memory, so you create another two lightweight classes. Is it correct?

    Why you want to map the lightweight classes to the database? Let them as DTO classes is fine. This is related to your 3<sup>rd </sup>question. You can write Linq to Entities to fetch the columns you want, EF will not load entire table to the memory. You can create a demo and see the generated T-SQL by EF in SQL Profiler, it only load the properties you want. About your 1<sup>st </sup>question, as I have mentioned, I think only let the lightweight classes as DTO classes is fine, you needn’t map them to database. If the properties of the database table can be null, when you want to insert a record, just assign the values to the properties required. And using DTO class to store data in your project. About the 2<sup>nd</sup> question, lazy loading is a good choice, it lets you only hit the database and load entities when you need to use. No matter whether you use join or not, if only you don’t call ToList () or To… () method at the end of the Linq statement, the query statement is only a query variable, it will not hit the database at that time. It hits the database only at the time of you traverse the collection. For example,

    // This statement will not hit the database

    Var query = from c in context.City join from s in context.State on c.StateID equals s.ID select new {c.Name, s.Name};  

    //This statement will hit the database

    Var query = (from c in context.City join from s in context.State on c.StateID equals s.ID select new {c.Name, s.Name}).ToList();   

    Here are some links I think can help you, please refer to them.

    Lazy Loading,Eager Loading,Explicit Loading in Entity Framework 4

    Lazy Loading in Entity Framework 4

    ADO.NET Entity Framework and .NET 4 & SQL Profiler : Lazy Loading of Entities

    I hope these information can help you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 08, 2012 7:04 AM
    Moderator