Linq question: loading an object graph

[0to60]

Let's say we have your basic Invoices and InvoiceItems table. If we load
this in with LINQ:

var query = from i in db.Invoices
select i;

When I then loop through my invoices, if I wanna access the lineItems for a
particular invoice, LINQ runs another query behind the scenes and goes and
gets them for me. So if I wanted to loop through my invoices and print out
the number of invoices thusly:

foreach(Invoice i in query)
WriteLine(i.someInfo, i.lineItems.count);

This means I have to go to the db each time through the loop. If I have
1000 invoices, I make that many round trips to the db, plus the original
query to get the invoices in the first place.

Similarly, if I used LINQ to just gimme the lineitems and then I wanted to
loop through them and access lineItem.Invoice, that means I have to go to
the db each time through the loop. In other words, any "aggregate" objects
don't get filled by the original LINQ query; LINQ waits until you access
them and goes and gets them in a "just in time" fashion.

Now, this is nice and convenient. It takes hardly any code to write this
stuff, and it gets what it needs when it needs to and you don't have to
concern yourself with it. But, I often work with graphs such as this that
have maybe 1000 parent objects, and each one might have 100 children (and
sometimes those children have children). If I have to loop through one of
my object graphs, it would be TONS of trips to the db and long wait times
for my users.

vIs there any way to tell LINQ to get more stuff initially?

 

[Peter Morris]

http://www.hookedonlinq.com/JoinOperator.ashx

 

[NvrBst]

Let's say we have your basic Invoices and InvoiceItems table.  If we load
this in with LINQ:

var query = from i in db.Invoices
                    select i;

When I then loop through my invoices, if I wanna access the lineItems for a
particular invoice, LINQ runs another query behind the scenes and goes and
gets them for me.  So if I wanted to loop through my invoices and print out
the number of invoices thusly:

foreach(Invoice i in query)
   WriteLine(i.someInfo, i.lineItems.count);

This means I have to go to the db each time through the loop.  If I have
1000 invoices, I make that many round trips to the db, plus the original
query to get the invoices in the first place.

Similarly, if I used LINQ to just gimme the lineitems and then I wanted to
loop through them and access lineItem.Invoice, that means I have to go to
the db each time through the loop.  In other words, any "aggregate" objects
don't get filled by the original LINQ query; LINQ waits until you access
them and goes and gets them in a "just in time" fashion.

Now, this is nice and convenient.  It takes hardly any code to write this
stuff, and it gets what it needs when it needs to and you don't have to
concern yourself with it.  But, I often work with graphs such as this that
have maybe 1000 parent objects, and each one might have 100 children (and
sometimes those children have children).  If I have to loop through one of
my object graphs, it would be TONS of trips to the db and long wait times
for my users.

 vIs there any way to tell LINQ to get more stuff initially?

I've only used LINQ for my object collections, that are inside my
code, but I think you might be wanting the "DataLoadOptions":

DataLoadOptions Class
http://msdn2.microsoft.com/en-us/library/system.data.linq.dataloadoptions.aspx


-----Some other links that might help you if this doesn't-----

How to: Control How Much Related Data Is Retrieved (LINQ to SQL)
http://msdn2.microsoft.com/en-us/library/bb882681.aspx

Deferred versus Immediate Loading (LINQ to SQL)
http://msdn2.microsoft.com/en-us/library/bb399393.aspx

Good Luck

 

[Frans Bouma [C# MVP]]

Let's say we have your basic Invoices and InvoiceItems table. If we
load this in with LINQ:

var query = from i in db.Invoices
select i;

When I then loop through my invoices, if I wanna access the lineItems
for a particular invoice, LINQ runs another query behind the scenes
and goes and gets them for me. So if I wanted to loop through my
invoices and print out the number of invoices thusly:

foreach(Invoice i in query)
WriteLine(i.someInfo, i.lineItems.count);

This means I have to go to the db each time through the loop. If I
have 1000 invoices, I make that many round trips to the db, plus the
original query to get the invoices in the first place.

Similarly, if I used LINQ to just gimme the lineitems and then I
wanted to loop through them and access lineItem.Invoice, that means I
have to go to the db each time through the loop. In other words, any
"aggregate" objects don't get filled by the original LINQ query; LINQ
waits until you access them and goes and gets them in a "just in
time" fashion.

Now, this is nice and convenient. It takes hardly any code to write
this stuff, and it gets what it needs when it needs to and you don't
have to concern yourself with it. But, I often work with graphs such
as this that have maybe 1000 parent objects, and each one might have
100 children (and sometimes those children have children). If I have
to loop through one of my object graphs, it would be TONS of trips to
the db and long wait times for my users.

vIs there any way to tell LINQ to get more stuff initially?

You can use loadoptions (DataLoadOptions object set to
ctx.LoadOptions), but take into account that linq to sql has a somewhat
bad way of fetching your graph. 1 1:n relation is supported. If you for
example want to fetch customer - order - orderlineitem, you're out of
luck and will see lazy loaded elements. This isn't a problem with linq
but with linq to sql.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------