none
Immediate loading of tables with Data Load options RRS feed

  • Question

  • Hi all,

    I am using data load options so that I won't have to load too much data into memory. The two tables 'GenericEntity' and 'Company' are related through the foreign key GenericEntityId. My code looks like this:

     Public  List<GetEntityListResult> GetEntityList()
            {
                using (EntityDataContext EntityDC = new EntityDataContext(Settings.ConnectionString))
                {

                    EntityDC.DeferredLoadingEnabled = false;
                    DataLoadOptions loadOptions = new DataLoadOptions();
                    loadOptions.LoadWith<GenericEntity>(o => o.Company);
                    EntityDC.LoadOptions = loadOptions;
                    return EntityDC.GetEntityList().ToList();    // Calls a stored proc that Gets entities that are companies
               

                } //end using

    'GetEntityListResult' is a generated class when dragging the stored procedure to my dbml in the designer. This class is not related to 'GenericEnitity' or 'Company' tables since it is generated in the designer.

    Would I still get the benefit of low memory consumption when using data load options in my example above? Am I on the  right track?

    How come if I am doing immediate loading with data load options on tables that are irrelevant to my linq query or stored procedure, I never get a runtime or compile error?

    Please advise...

    Thursday, March 24, 2011 12:12 PM

Answers

  • Hi N.Afache;

    To your statement:

    You are saying that I am loading Sectors when I put EmpDC.Sectors in the code above, my assumption was that EmpDC should not contain Sectors since 'Sectors'  was not specified in the 'loadWith' options of the following  statements :

      loadOptions.LoadWith<AddressType>(o => o.Address);
      EmpDC.LoadOptions = loadOptions;
     
    Maybe I am confused about how Data load options work, but I thought that it prevents certain tables from being loaded in memory.

    This statement:

      loadOptions.LoadWith<AddressType>(o => o.Address);

    Tells the data context that any time it is requested to load row from the AddressType table to also load associated rows from the Address table using the relationship defined in the database between the two tables.

    So when you execute this statement:

        return (from c in EmpDC.Sectors
                where c.SecID == ID
                select c).Single();

    It is telling the data context to build a SQL query that instructs SQL server to return the first row of the Sectors table with the column name SecID that has the value ID in your program. Note it has nothing to do with any other table but the Sectors table. If you create a query that uses the AddressType table the data context will use the load option you created to not only return the AddressType table but also the associated rows from the Address table because you told it to do this when you set up the load options.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, March 30, 2011 3:25 AM

All replies

  • I think you can get what you want, can't you?

    What you mean is you can't get the right result but no runtime or compile error ?

    Tuesday, March 29, 2011 7:27 AM
  • yes I am getting the correct result, but the dataLoad options in my case does not make any sense, does it?
    Tuesday, March 29, 2011 4:10 PM
  • I am getting the correct result with no compile or runtime errors.
    Tuesday, March 29, 2011 7:04 PM
  • Hi N.Afache;

    To your question, "Would I still get the benefit of low memory consumption when using data load options in my example above? Am I on the right track?", What is returned from a stored procedure is totally controlled by that stored procedure and the Linq load option will have no effect on it. The load option only comes into account on queries that are compiled into SQL statements to be sent to the SQL server. So in this situation it has no effect.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Allan Merolla Wednesday, March 30, 2011 6:14 AM
    Tuesday, March 29, 2011 7:50 PM
  • Hi Fernandoo,

    I will not use load options with stored procs anymore.

    Another important issue came to my attention lately. Take a look at the following peice of code:

    public Sector Getsectors(int ID){
    using (EmployeeLookupsDataContext EmpDC = new EmployeeLookupsDataContext(Settings.ConnectionString))
               {
             
              EmpDC.DeferredLoadingEnabled = false;
              DataLoadOptions loadOptions = new DataLoadOptions();
              loadOptions.LoadWith<AddressType>(o => o.Address);
              EmpDC.LoadOptions = loadOptions;
              return (from c in EmpDC.Sectors
                     where c.SecID == ID
                     select c).Single();
               
              } //end using

    }//End method

     

    In the above code I am using a linq query and not a stored proc. I am loading 'Address' and 'AddressType' tables, but the linq query returns data from the Sector table. I do not get any error !!!! It seems that Load options has no effect here either. What do you think?

     

    Tuesday, March 29, 2011 8:48 PM
  • Hi N.Afache;

    You state the following, "I am loading 'Address' and 'AddressType' tables, but the linq query returns data from the Sector table.", but in fact you are loading the Sector table as can be seen from this part of your code EmpDC.Sectors in the below query. 

    return (from c in EmpDC.Sectors
          where c.SecID == ID
          select c).Single(); 
    


    In the following query I am assuming that AddressType is the one side of the one-to-many relationship.

    return (from c in EmpDC.AddressType
          where // The where clause most likely changed because table changed
          select c).Single(); 
    


    Now the returned type is a AddressType with a collection of Addresses. If my assumption was incorrect please post the schema's of the tables to be returned.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, March 29, 2011 9:21 PM
  • Hi Fernandoo,

    Sector table is a table within the dbml file that is not related to 'Address' or 'AddressType' at all.

    AddressType and Address relate to each other through AddressID.

    I was actually doing a test by calling a query that contains nothing pertaining to 'Address' or 'AddressType' because I wanted to see if it will return an error or not.

    return (from c in EmpDC.Sectors
          where c.SecID == ID
          select c).Single();

    You are saying that I am loading Sectors when I put EmpDC.Sectors in the code above, my assumption was that EmpDC should not contain Sectors since 'Sectors'  was not specified in the 'loadWith' options of the following  statements :
     loadOptions.LoadWith<AddressType>(o => o.Address);
      EmpDC.LoadOptions = loadOptions;
    Maybe I am confused about how Data load options work, but I thought that it prevents certain tables from being loaded in memory.


    Tuesday, March 29, 2011 11:08 PM
  • Hi N.Afache;

    To your statement:

    You are saying that I am loading Sectors when I put EmpDC.Sectors in the code above, my assumption was that EmpDC should not contain Sectors since 'Sectors'  was not specified in the 'loadWith' options of the following  statements :

      loadOptions.LoadWith<AddressType>(o => o.Address);
      EmpDC.LoadOptions = loadOptions;
     
    Maybe I am confused about how Data load options work, but I thought that it prevents certain tables from being loaded in memory.

    This statement:

      loadOptions.LoadWith<AddressType>(o => o.Address);

    Tells the data context that any time it is requested to load row from the AddressType table to also load associated rows from the Address table using the relationship defined in the database between the two tables.

    So when you execute this statement:

        return (from c in EmpDC.Sectors
                where c.SecID == ID
                select c).Single();

    It is telling the data context to build a SQL query that instructs SQL server to return the first row of the Sectors table with the column name SecID that has the value ID in your program. Note it has nothing to do with any other table but the Sectors table. If you create a query that uses the AddressType table the data context will use the load option you created to not only return the AddressType table but also the associated rows from the Address table because you told it to do this when you set up the load options.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, March 30, 2011 3:25 AM
  • You have been very informative Fernando Thank you. 

    You said:

     If you create a query that uses the AddressType table the data context will use the load option you created to not only return the AddressType table but also the associated rows from the Address table because you told it to do this when you set up the load options.

    I understand how it works now, and thanks to you. My question is will the above be beneficial as far as optimization? Does it make a differance when it comes to memory consumption?

     

     

    Wednesday, March 30, 2011 5:25 AM
  • Hi N.Afache;

     

    To your question:

    My question is will the above be beneficial as far as optimization? Does it make a difference when it comes to memory consumption?

    That all depends on the situation that it will be used in. For example if I query the database table AddressType and I only need to look at the Address table for for one or two items of AddressType then setting DeferredLoadingEnabled to false and defining load options is not a good idea because all this extra data from the Address table will also be download. On the other hand if you are going to need the data from both tables for most if not all of then it is a good idea. You need to also remember that downloading the extra data, the Address table in this example, is going to use up more bandwidth from the network which may not be what you want to do. You need to analyze each case and take the proper action.

     

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, March 30, 2011 2:34 PM
  • If I wanted data from both tables(Address and AddressType) then I could do an inner join in my linq query for Address and AddressType and not use load options at all. I don't see much differance  in such a case. Do you?
    Wednesday, March 30, 2011 3:39 PM
  • If you do not need to update the database with the downloaded data then no real difference. But if you will be needing to update one or both of the tables then you need to download them as entities objects of there table types so that the data context can track the entities so that they can be modified. Downloading them as a join table will create an anonymous type or if you create a specific type for the returned data which will not be part of the data context or be tracked by the data context.
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, March 30, 2011 5:03 PM
  • As a conclusion to our discussion:

    Data load options with immediate loading can be effective with linq queries and not stored procs. It is beneficial when the data downloaded through the linq query is to be further updated.

    I am trying as much as I can in my webapplication to make the (DataContext) dbml file as small as possible (with few tables). So by creating more than one dbml file  I will avoid creating a bulky object that consumes a lot of the application resources.

     I also aquire late and release early of the datacontext in use.

    Thank you Fernandoo. If you would like to comment on anything I would always appreciate your help and value your opinion.

     

    Wednesday, March 30, 2011 7:53 PM
  • To the statement:

    I am trying as much as I can in my webapplication to make the (DataContext) dbml file as small as possible (with few tables). So by creating more than one dbml file  I will avoid creating a bulky object that consumes a lot of the application resources.

    This is not the case. when you create a data context you are creating an object that will hold tables which are only created as you query the database. So if you have a mode with 100 tables defined and you create a data context you are NOT creating 100 tables you are creating an object capable of holding 100 tables. A table is created and filled as a query is executed and rows are returned.

    To your statement: 

    I also aquire late and release early of the datacontext in use.
     
    Microsoft has a phrase for this and that it uses in its documentation and that is, creating a DataContext for "A Unit of Work", and that is a good thing.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, March 31, 2011 3:45 AM
  • So no matter how big the dbml file is, that should not make me worry about performance, Correct?

    If an object is capable of holding more tables than another that does not make it any heavier?

    Thursday, March 31, 2011 8:37 AM
  • To your statement:

    So no matter how big the dbml file is, that should not make me worry about performance, Correct?

    Remember that a DBML file holds class definitions that are not yet instantiated, not yet an object, when the DataContext is instantiated. So no memory is being use except for the definition in the exe of the classes. No tracking of entities because the DataContext was just instantiated so nothing to keep track. Once you execute a query and bring rows of a table/s then objects get create and tracking starts but only for those rows returned from the query.

    To your statement:

    If an object is capable of holding more tables than another that does not make it any heavier?

    From Microsoft documentation:
    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.aspx

    The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance.

    In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, March 31, 2011 2:45 PM