none
Limiting a list of child entity fields with LoadWith<> preload RRS feed

  • Question

  •  

    I have two related tables, say customer and order, and they both have over 50 fields. In my LINQ query I just need a few of those fields in each table (name and account from Customer and dateInvoiced and totalAmount from Order), and I want to preload all data to reduce the number of SQL scripts. 

     

    What I used is to define anonymous type:

    Code Snippet

     

    DataLoadOptions lo = new DataLoadOptions();

    lo.LoadWith<Customer>(c => c.Orders);

    db.LoadOptions = lo

     

    var cust = from customer in db.Customers

    where (condition on customer)

    select new() {customer.Name, customer.Account, customer.Orders}

     

     

    This indeed generates just one SQL script and I have access to Orders list and it only loads two fields from customer in the SQL script, but it also loads all 50something fields from Orders. Seems like an overkill just to reach two fields in the child entity list. I know I can skip the preload and generate a new LINQ to SQL query in foreach on customers using another anonymous type, but that brings me back to having a new SELECT for each customer.

     

    Is there a way (without resorting to LINQ to Entities, I cant use non-RTM stuff, policy rule) to use anonymous type in drill down, somehow instruct LINQ to SQL to use anonymous type for customer, but also use anonymous type for child table with only these fields?

     

     

     

    Wednesday, May 7, 2008 12:47 PM

Answers

  •  

    You can always project the subset of Orders' properties using another anonymous type:

     

     

    Code Snippet

     

    DataLoadOptions lo = new DataLoadOptions();

    lo.LoadWith<Customer>(c => c.Orders);

    db.LoadOptions = lo;

     

    var query = from customer in db.Customers

                where (condition on customer)

      selec new

      {

         customer.Name,

         customer.Account,

         orderFields = from order in customer.Orders

                       select new

                       {

                          order.OrderField1,

                          order.OrderField2

                       }

                };

     

     

    Hope that helps,

     

    Maurycy

    Wednesday, May 7, 2008 9:45 PM

All replies

  •  

    You can always project the subset of Orders' properties using another anonymous type:

     

     

    Code Snippet

     

    DataLoadOptions lo = new DataLoadOptions();

    lo.LoadWith<Customer>(c => c.Orders);

    db.LoadOptions = lo;

     

    var query = from customer in db.Customers

                where (condition on customer)

      selec new

      {

         customer.Name,

         customer.Account,

         orderFields = from order in customer.Orders

                       select new

                       {

                          order.OrderField1,

                          order.OrderField2

                       }

                };

     

     

    Hope that helps,

     

    Maurycy

    Wednesday, May 7, 2008 9:45 PM
  •  

    That was exactly what I needed, I didn't know I can project in depth. Thanks.
    Thursday, May 8, 2008 2:12 PM