none
Help with LINQ syntax for eager loading and object graph conditionally. RRS feed

  • Question

  • I'm trying to query conditionally across several tables in an SQL DB to return a limited object graph.  As an example let's assume three tables Customers, Invoices (child of customer), Details (child of Invoice) with appropriate relationships set up in the DB between them.  I'd like to write a LINQ query that returns a single Customer with a limited graph based on conditions:

    Customer.Name = "John"
    all Invoices where Invoice.Amount > 100.00
    all Details where Detail.Date = 01/01/2009

    I'd like to get all data in one trip to the DB.  I know I could write a pretty gnarly query expression using anonymous types to accomplish this but I'd really like to take advantage of the strong typing already defined by the LINQ to SQL generated classes.  I looked at mixing DataLoadOptions LoadWith and AssociateWith but I know there are some serious performance issues when using LoadWith when relationships are deeper than one level.

    It seems like this should be something that can be done efficiently but I'm fairly new to LINQ (our company has been stuck in .net 2.0 land forever) so I'm banging my head against it.  Any help you can provide will be greatly appreciated.

    Thanks
    Thursday, September 24, 2009 5:20 PM

Answers

All replies

  • After playing around with LINQ queries and the SQL profiler and reading through information from various internet sources it appears that what I want to do is actually not possible.  The information I've gathered indicates that eager loading works well to a single level of depth but as soon as you go to the second level (grandchidren) you end up with a minimum of two seperatly executed DB queries.  Here's one of the better posts I found on the subject.  

    http://www.lowendahl.net/showShout.aspx?id=190

    The experimental evidence I found in testing seems to confirm the authors assertions.  My initial thought (that I could accomplish this with anonymous types) appears to have also been in error.  Constructing a query that projects into an anonymous type for LINQ to SQL does, in fact, bring about eager loading (even without using DataLoadOptions LoadWith) but again it only works as a single DB trip to one level.  If you try projecting to anonymous types across three or more tables you again end up with at least two seperate queries being executed on the DB.

    I would absolutely love it if someone could show me that I'm wrong here and that there's some way of getting data across three seperate tables with a single trip to the DB but as of now it looks to me like the only way to accomplish this is to utilize a stored procedure, view or function on the SQL DB itself.  This really seems to limit the usefulness of LINQ to SQL.  Queries involving parent/child/grandchild data are far from uncommon.
    Friday, September 25, 2009 5:03 AM
  • Hi Dane,

    Do you want to load all the customer with name “John”, then each “John”’s Invoices whose Amount is greater than 100.00, and for each these Invoice, its Details whose Date is 01/01/2009 with one SQL command?  If so, I think it is feasible with the combination of DataLoadOptions.LoadWith and DataLoadOptions.AssociateWith as you have mentioned in your first post.   Do you encounter any performance issue on this method? 

    If the performance is really important for your application, using stored procedure or database views can be a good option.  Stored procedures and views are also supported well in LINQ to SQL. 
    http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
    http://msdn.microsoft.com/en-us/library/bb384396.aspx

     

    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.
    • Marked as answer by Yichun_Feng Friday, October 2, 2009 1:34 AM
    Thursday, October 1, 2009 7:53 AM
    Moderator
  • That is correct, a single command.  Using the DataLodOptions only works to eager load one level of depth in a single command.  If you try to use it to eager load grandchildren you get more than one query.  You can see this quite clearly by looking at the generated SQL statements.  I am aware that I can do this with a stored procedure, however, admin level access to a DB isn't always an option.

    Wednesday, October 14, 2009 5:45 PM