none
How to prevent loading of data of all tables using data context? RRS feed

  • Question

  • Hi,

    I have created a .CS file using SQL Metal. This contains the details of all the tables in the database. It also allows to create a datacontext based on the .CS file.

    For example:- My .CS file called "ResourceManagement.cs" contains the properties and methods for all the tables in the database. Now when I use this DataContext and use it to retrieve data, it gets me back data from all the tables. which means if I have 20 tables in my database - it would retrieve me data for all twenty tables.

    Is there a way I can avoid it?



    Friday, December 5, 2008 8:48 AM

Answers

  • The table properties on the DataContext are not collections of loaded items, they are queries. If you enumerate them they will execute a database query and retrieve objects, so they may appear as if they are collections loaded with data but they are not. If you cause them to be enumerated again they'll fire off another database query. You should use these tables as starting points for more specific queries. When you do you only retrieve the objects for that specific query, not the whole table. However, if you data bind to a table, you will in fact retrieve all objects from the entire table. For this reason, you should never data bind to a DataContext table unless you know for a fact that it is relatively small. You can, and should, data bind to a more specific query based on a DataContext table.

     

    Relationship properties on objects (for example the Orders property on a Customer object) are by default lazy loaded. Each is empty until you look it (enumerate it), in which case it executes a database query and loads itself. These are actual collections and only load themselves once.  So it may appear that all is loaded up front when you examine collections in the debugger or choose to bind a property, but you are only paying for the data load because you chose to look.

     

    Wednesday, December 10, 2008 3:09 PM
    Moderator

All replies

  • How do you know that all data are retrieved ? What is the code you are calling ? Have you tried SQL Profiler ? This is not the usual behavior so for now I would think that you mistakenly interpreted something as meaning that you loaded all data.

     

    Newing the datacontext doesn't do much. Data are retrieved as needed when querying them (you can even lazy load particular columns in a table). I wonder if you don't think that loading is being done beforehand while it is actually done as needed when your code enumerates a query...

     

    --

    Patrice

    Friday, December 5, 2008 1:43 PM
  • Hi Patrice,

     

    I am getting back data from one of my tables and binding it to the datagrid. Before binding it to the datagrid, when I check whats in the context, it displays all the tables related to the context and within it all the records.

     

    this is the code which I have written

     

    public static IEnumerable<Employee> RetrieveAllEmployees()

    {

    IEnumerable<Employee> employees;

    using (ResourceManagement context = new ResourceManagement())

    {

    employees = from emp in context.Employee

    select emp;

    employees = employees.ToList();

    }

    return employees;

    }

     

    After this when I check the context on my UI - I see records of other tables like Projects, EmployeeLevels etc. Now, when the database is small and the number of records are less it is fine; but there might be an issue if there are many records but again this could not be proved. We inserted 1,00,000 in a table and it was able to fetch it back in less than 1/2 a second!

     

     

     

     

    Monday, December 8, 2008 10:09 AM
  •  

    IMO, what you see is just that LINQ exposes related rows automatically from your parent/child rows... If you enumerate them (for example perhaps even using the debugger), LINQ will fetch the data. It doesn't mean all is loaded first. This is just that data are automatically loaded for you when ask for them.

     

    I would suggest launching SQL Server profiler against your db and check see what is sent to the server as you step into the code (and perhaps in the debugger or the immediate window). You'll see your main request and then you'll see that requests to load related data are just done when your code asks for those data...

     

    --

    Patrice

    Tuesday, December 9, 2008 6:56 PM
  • The table properties on the DataContext are not collections of loaded items, they are queries. If you enumerate them they will execute a database query and retrieve objects, so they may appear as if they are collections loaded with data but they are not. If you cause them to be enumerated again they'll fire off another database query. You should use these tables as starting points for more specific queries. When you do you only retrieve the objects for that specific query, not the whole table. However, if you data bind to a table, you will in fact retrieve all objects from the entire table. For this reason, you should never data bind to a DataContext table unless you know for a fact that it is relatively small. You can, and should, data bind to a more specific query based on a DataContext table.

     

    Relationship properties on objects (for example the Orders property on a Customer object) are by default lazy loaded. Each is empty until you look it (enumerate it), in which case it executes a database query and loads itself. These are actual collections and only load themselves once.  So it may appear that all is loaded up front when you examine collections in the debugger or choose to bind a property, but you are only paying for the data load because you chose to look.

     

    Wednesday, December 10, 2008 3:09 PM
    Moderator