linq to sql loadwith vs associatewith

Question

what is the difference between loadwith and associatewith. From the articles i read it seems that loadwith is used to load addition data (eg all orders for the customers). While AssociateWith is used to filter data.

Is that a correct understanding? Also it will be nice if someone can explain this with an example based explanation.

Answer 1

Yes your understanding is correct; AssociateWith filters data before queries whereas LoadWith returns associated objects in the query. The reason for doing LoadWith is so you can return associated objects in 1 single query. Otherwise extra db calls will be made at the time when you iterate over your associated objects.

Try different examples yourself and take a look at the SQL generated through Profiler or some other logger. Remember that these options need to be set on your DataContext before you do any queries.

Have a look at the examples in the below links (MSDN). I've just copied the examples they use there.

LoadWith

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>(c => c.Orders);
db.LoadOptions = dlo;

var londonCustomers = from cust in db.Customers
                      where cust.City == "London"
                      select cust;

AssociateWith

DataLoadOptions dlo = new DataLoadOptions();
dlo.AssociateWith<Customer>(
    c => c.Orders.Where(p => p.ShippedDate != DateTime.Today));
db.LoadOptions = dlo;

var custOrderQuery = from cust in db.Customers
                     where cust.City == "London"
                     select cust;

Answer 2

LoadWith is used to perform an eager load of an association as opposed to the default lazy load.

Normally, associations are loaded the first time you reference them. That means if you select 100 Order instances, and then do something with each of their Details, you're actually performing 101 SELECT operations against the database. On the other hand, if the LoadOptions specify LoadWith<Order>(o => o.Details), then it's all done in a single SELECT with an added JOIN.

AssociateWith doesn't have any effect on when the association is loaded, just what is loaded. It adds a WHERE clause every time you load an association.

As you say, AssociateWith is used to automatically filter data. Typically you'd use this if you know that an association has a very large number of elements and you only need a specific subset of them. Again, it's mainly a performance optimization, just a different kind.