none
Hiearchical Data and Linq RRS feed

  • Question

  • Ok I understand Linq to Sql has its downfalls but this seems like a big one unless im doing it all wrong. Ok lets say i have 2 Tables with a one to many relationship setup. now I set my data context to a wpf treeview when i load the little app the first level loads i get all of the first table using the following:

    "Select ... from table1"

    now when i expand that node I get a list of all the rows in table1 the problem comes in on how linq loads table2 instead of selecting everything in one DB call and then filtering the relationships linq seems to do a "select where" on each and every record in table1 well in my case im loading 900 rows and 800 of them may not have a child record in table2 but we still do the select. It seems faster to select table1 and if you request table2 it gets all records at one time then creates the relationships from that master list.

    Am i thinking of this wrong it just seems like making that many trips to the DB which for me in most cases will not be on the same server or even in the same state sometimes as the client.
    • Moved by Bruce.Zhou Friday, September 11, 2009 3:38 AM move to appropriate forum (From:Windows Presentation Foundation (WPF))
    Thursday, September 10, 2009 8:24 PM

Answers

  • Hi Chrissteven81,

     

    Welcome to LINQ to SQL forum!

     

    I can understand your current scenario, and it seems lots of database transfers are unnecessary.  However, due to the delay loading feature of LINQ to SQL, such behavior is reasonable.  All the data will be retrieved only if we try to access them.  Even the LINQ query is built, the actually SQL query will be executed when we want the data. 

     

    For LINQ to SQL databinding, the SQL commands are all built with parameters which will be filled by the certain foreign key column values.  Only when we press each row/entity of the master table, the certain SQL commands will be executed to retrieve the corresponding detail table data.   It is designed for many applications with large complicated data and the developers don’t want all the data be retrieved into the local memory at one time. 

     

    In your scenario, if you want to retrieve all the data at one time, I recommend you to use strongly typed DataSets, and bind the TreeView to the DataSet.  If you also want the LINQ styled query in your application, LINQ to DataSet is a good option.   Here are some references for binding TreeView to DataSet:

    http://joshsmithonwpf.wordpress.com/2007/05/05/binding-a-treeview-to-a-dataset/

    http://bea.stollnitz.com/blog/?p=21

     

     

    Hope you have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Chrissteven81 Friday, September 11, 2009 12:26 PM
    Friday, September 11, 2009 7:22 AM
    Moderator
  • Rather than going the route of typed datasets (which the LINQ to SQL and EF objects can be seen as replacements for), I would recommend taking a look at LINQ to SQL's LoadOptions, specifically LoadWith. With the Entity Framework, you would use the Includes method in the query. In both of these cases, the generated query will eagerly load the children rather than relying on the azy loading behavior you are complaining about.

    That being said, I would be hesitant to use the eager loading on a data set with 800-900 rows because it is highly unlikely that your users are going to access all 800 records and their children. Thus by lazy loading only the results that are accessed, you will see a performance benifit by being more chatty.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Sunday, September 13, 2009 10:44 PM
    Moderator

All replies

  • Yes, LINQ-To-SQL is not that dynamic. Actually it's plain static. That's why I hope I won't ever have to use it.

    This a great article on the subject providing a solution implementing some extensions to LINQ.
    http://www.scip.be/index.php?Page=ArticlesNET18

    Bigsby, Lisboa, Portugal - O que for, quando for, é que será o que é... http://bigsby.eu
    Thursday, September 10, 2009 8:39 PM
  • well as the sole developer where i work it cuts my development time by at least 25% for the DAL but it just seems this is a big downfall that i did not realize until i started monitoring the system for perfomance
    Thursday, September 10, 2009 8:42 PM
  • Yes. We all have to do things we don't love. I never heard of a single new solution for cutting development time that hasn't performance issues. Take ASP.Net for exemple and all the trash comming back and forth...don't get me started. I've been always doing my own dynamic T-SQL construction and my own communication protocol and don't think I'll see anything as performant in the next few years. In fact I only implemented it once. There's your 80% to 90%.

    Bigsby, Lisboa, Portugal - O que for, quando for, é que será o que é... http://bigsby.eu
    Thursday, September 10, 2009 8:54 PM
  • While I aggre with the sacrafices must be made part of it. I would have to disagree with the Asp.net statement, Asp.net has a lot of tricks to drasticly minimize the overhead and reduce clutter to a minimum. the only problem i have with asp.net is some of the "features" of many of the controls provided are not fully browser independent as i believe browser indepented does not only mean it works on other browsers but will display the same on each.
    Thursday, September 10, 2009 9:01 PM
  • Hi Chrissteven81,

    The problem you are facing is more of a Linq to Sql problem, so you may get better answers in the Linq to Sql forums since there are many linq to sql experts. I am moving this thread  to Linq to Sql forum now.

    Best regards,
    Bruce Zhou
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 11, 2009 3:37 AM
  • Hi Chrissteven81,

     

    Welcome to LINQ to SQL forum!

     

    I can understand your current scenario, and it seems lots of database transfers are unnecessary.  However, due to the delay loading feature of LINQ to SQL, such behavior is reasonable.  All the data will be retrieved only if we try to access them.  Even the LINQ query is built, the actually SQL query will be executed when we want the data. 

     

    For LINQ to SQL databinding, the SQL commands are all built with parameters which will be filled by the certain foreign key column values.  Only when we press each row/entity of the master table, the certain SQL commands will be executed to retrieve the corresponding detail table data.   It is designed for many applications with large complicated data and the developers don’t want all the data be retrieved into the local memory at one time. 

     

    In your scenario, if you want to retrieve all the data at one time, I recommend you to use strongly typed DataSets, and bind the TreeView to the DataSet.  If you also want the LINQ styled query in your application, LINQ to DataSet is a good option.   Here are some references for binding TreeView to DataSet:

    http://joshsmithonwpf.wordpress.com/2007/05/05/binding-a-treeview-to-a-dataset/

    http://bea.stollnitz.com/blog/?p=21

     

     

    Hope you have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Chrissteven81 Friday, September 11, 2009 12:26 PM
    Friday, September 11, 2009 7:22 AM
    Moderator
  • Thanks I understand the reasoning behind it. Quick question when you set the datacontext.log property to log querys to your immediate window does that log the generated sql or does it log the executed sql query? In otherwords are all the querys im seeing actualy being executed? The only time this seems to be a problem is when I'm viewing the data in a treeview otherwise i almost never veiw all the data at once. does linq to datasets support INotifyCollectionChanged or would i have to extend it like i had to do in linq to sql for wpf?
    Friday, September 11, 2009 12:11 PM
  • Hi Chrissteven81,

     

    Yes, the T-SQL in the DataContext log is same as the one executed in the SQL Server side, except that LINQ to SQL actually calls "exec sp_executesql ‘T-SQL command’” to retrieve the child table data in your scenario.   For detail, you can use SQL Server Profiler to verify it. 

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 11, 2009 12:24 PM
    Moderator
  • Awsome thanks.
    Friday, September 11, 2009 12:26 PM
  • You are welcome.  J 

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 11, 2009 12:29 PM
    Moderator
  • Rather than going the route of typed datasets (which the LINQ to SQL and EF objects can be seen as replacements for), I would recommend taking a look at LINQ to SQL's LoadOptions, specifically LoadWith. With the Entity Framework, you would use the Includes method in the query. In both of these cases, the generated query will eagerly load the children rather than relying on the azy loading behavior you are complaining about.

    That being said, I would be hesitant to use the eager loading on a data set with 800-900 rows because it is highly unlikely that your users are going to access all 800 records and their children. Thus by lazy loading only the results that are accessed, you will see a performance benifit by being more chatty.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Sunday, September 13, 2009 10:44 PM
    Moderator
  • Hi Chrissteven81,

     

    Jim is right!  Using DataLoadOptions is another good option, all the children data will be retrieved immediately.  But to load all the data and their children at one time may bring huge performance cost.  Maybe paging is a good choice if we have lots of data to bind. 

     

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, September 14, 2009 7:06 AM
    Moderator
  • I figured out the root cause of why in my situation so many querys were made to the db. while this does not realy help the situation with the treeview it does however show me why things were getting loaded so much. each time you click a node in my treeview a report for that node is generated using .net reports connected to biz layer objects which are then using the linq data layer. The default node selected is the upper top level node because i assumed once the data is loaded all consecutive reports will not query the server. this means the entire Dataset was being loaded right in the begining. which is why the system would lag out. however im not sure why even after the first report loaded it would query the DB for every consectutive report there after. I decided to just move the report to a stored procedure in the DB and run in through linq to sql this allowed me to load my reports without tons of querys in one thread and run the UI on another so my UI is no longer dependent on weather the report has finished loading. This means i can allow the system to lazy load and have reports which require almost all the data in the database.

    Thanks for all your help.
    Monday, September 14, 2009 2:51 PM