linq to sql LoadWith limiting fields returned

Question

Is there a way to use LoadWith but specify the fields that are returned?

For example, if I have two tables 1) Products 2) Categories

and do something like

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Products>(d => d.Categories);
db.LoadOptions = dlo;

MyDataContext db = new MyDataContext();
var result = from d in db.Products
             select d;

when i check the query in profiler i see that ALL the rows from the Categories table are being returned. All I really need is the "Name" field.

I know I can rewrite the query using joins but I need to return the result set as a "Product" data type which is why I am using LoadWith.

Answer 1

No that's not possible with LoadWith.

You could try with a nested query in the projection, though that will be slow: 1 query per parent (so 1 query for the related category per product loaded).

Answer 2

You can use a projection, but you need to deal with anynomus types after that

select new {Order = order, ProductName = order.Product.Name,
             CustomerName = order.Customer.Name,
             OrderType = order.OrderType.Name } // etc