none
Linq2Sql batch loading RRS feed

  • Question

  • Looking for help with Linq2Sql and batch loading.. 

    Say I'm looking to get all Customers loaded as well as each Customer's Orders and the Customer's Addresses?

    So I have two 1-Many relationships.

    For best performance in my case I'd like it to do this:
    1 query for Customer
    1 query for ALL Orders of all customers selected (where customer id IN the previous list)
    and 1 last query for ALL Customer Addresses (where customer id IN the first list)

    When I try to do the DataLoadOptions and it loads Orders, I seem to get a separate query per Customer that would have Order   (ie where customerID = 7), but it would be more efficient if it would it just get all Order lines for all customers involved and shove that data into each customer's orders.

    I will be returning all this data for around 5 customers, so the slow loading isn't helping - it's hindering in this case :(

    So again, is there a way to tell the engine to load a single entity type with only 1 query (not 1 query per parent entity?) and also have Linq2Sql shove it in the appropriate Customer.Order object for me?

    Thursday, March 22, 2012 11:46 PM

All replies

  • Hi ryanb,

    Welcome!

    I think you can use SQL Server Profiler to watch the generated T-SQL by your LoadOptions Linq queries, I don't think they are running seperated queries.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, March 24, 2012 3:00 PM
    Moderator
  • Hi Alan. That is a great idea, I agree. Before posting I had gone through that and that's why I've posted. I've been working on getting an example together to show what I mean. I used AdventureWorks so it's a database people already have.

                AWDataContext aw = new AWDataContext();
                DataLoadOptions dataLoadOptions = new DataLoadOptions();
                dataLoadOptions.LoadWith<Customer>(c => c.CustomerAddresses);
                dataLoadOptions.LoadWith<Customer>(c => c.SalesOrderHeaders);
    
                aw.LoadOptions = dataLoadOptions;
                aw.Log = Console.Out;
    
                List<Customer> customerList = aw.Customers.Take(3).ToList();
    
                Console.WriteLine(customerList.Count);

    I query for 3 customers, with also wanting to load their addresses and order headers (this is a similar relationship to my own data's setup). What ends up happening is 1 query for customers and then 6 more queries because it does a separate one to get the CustomerAddresses and OrderHeaders per customer.

    Is there a way to make Linq2Sql make a total of only 3 queries and still auto populate the 3 customer objects with Addresses and OrderHeaders?

    1) query to get customers
    2) query to get all customeraddresses where customerID in the first list
    3) query to get all order headers where customerID in the first list

    Remember another point was to isolate the customer query by itself because there is an expensive column in the mix and I wouldn't want to repeat that thick column on several rows... so JOINs in this query are not an option.

    Or is this the case where a stored proc would be a better fit? If so, maybe I could be pointed to a resource that would show how it could be done and possibly still auto populate the customer objects' properties as well as their addresses and orders. An article I found is close to that but it seems like I'd need to set the CustomerAddress list and Order list for each customer. Any way around this grunt work would be helpful so I can avoid it in the future.

    Tuesday, March 27, 2012 5:36 PM
  • Hi ryanb2009,

    Thanks for your feedback.

    I can repro your scenario with .Take(3) method, If you move it or use "where" to filter, the "T-SQL" changes. You can try it.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 29, 2012 9:09 AM
    Moderator
  • Thanks! Trying it out..

    Ok, with this:

    List<Customer> customerList = aw.Customers.Where(c=> c.CustomerID <= 3).ToList();
    

    I do still get a query per CustomerOrderHeader :( Imagine the rare cases where there might be 100 customers returned.

    And the other iffy thing is that CustomerAddress now bloats the record count and I do have an issue with Customer having a big binary column in it that I don't want repeated on multiple rows for performance issues.

    If you have other ideas around this, I'm all ears for the future.

    Linq2Sql is a great tool for RAD, but so far it seems to fall short for anything but single (or low) record CRUD and single table select. I say this after spending 15 hours researching/troubleshooting it as compared to writing a stored procedure. I was looking for quickness plus flexibility when needed. I noticed you commented on my other thread and I think that's the route I'm going to need to go to in order to not inflate the return data.

    Thursday, March 29, 2012 1:49 PM