Foreign keys cause more queries?

Question

Hi,

I have 2 objects - Order and Product. On the Order only productID is saved and when I view orders I want to see the product name. According to ScuttGu blog this is easily done by using a template field with Eval("Product.ProductName"). However, when reviewing the actual queries I see that for each order a separate query is made.

It doesn't sould right to me because for many rows and/or foreign keys many additional queries will be made. Doesn't it make the whole this too inefficient (i.e. why linq doesn't use a join)?

Thanks

Answer 1

This happens because at the point that Eval has run, the query already has, without said join.

When you're fetching the query you can use DataLoadOptions to include this using the .LoadWith() method:

var dlo = new DataLoadOptions();
dlo.LoadWith<Order>(o => o.Product);
var dc = new DataContext();
dc.LoadOptions = dlo;

var orders = from dc.Orders select o;

Answer 2

That is because your products are lazy loaded - that is they are loaded when needed.

You can DataLoadOptions to set your fetchingstrategy, and load the products with your order:

MyDataContext db = new MyDataContext();
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Order>(order => order.Product);
db.LoadOptions = options;
var orders = from c in db.Orders

If you don't like the pr. datacontext specification of loadoptions you do something like this (not testet):

MyDataContext db = new MyDataContext();
db.Orders
    .Select(o => new { Order = o, Products = o.Products})
    .ToList()
    .Select(x => x.Order)
    .ToList();

I have implemented something like this guys fethingstrategies, which works out nicely with my repositories and the specification pattern.