Slow performance on LINQ and lists

Hi

I use VB.net to connect to a SQL database.

I have a order table, and a orderline table.

Dim recOrderHeaders As IList(Of ShopifyData.OrderHeader)

            recOrderHeaders = (From item In dbContext.OrderHeaders
                               Where (item.CustID = MyCustomer.CustID)
                               Order By item.OrderNo Descending
                               Select item).ToList

' Then I iterate through all the orders and find the order lines
for each item in recOrderHeaders
   for each oLine in recOrderheaders.Orderlines
   next
next

Open in new window


Iteration through order headers go quickly, but iteration through order lines takes a long time (on my case 60ms per line).

I thought all data was collected in the OrderHeader object from memory when using the ToList.

Typically each order has 1-5 lines.

How can I speed this up ? For example one customer has 90 orders and it takes 5 seconds to get all orders/orderlines.
David DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
This is one of the misconceptions in regards to LINQ to Entities.  Everything is lazy loaded.  In a nutshell lazy loading is the concept where an entity is not loaded from the database until a property referring to the entity is accessed.  In your case, you prevented the lazy loading on the OrderHeader by subjecting it to the ToList extension method.  This loaded the OrderHeaders, however, the OrderLines (an entirely different Entity) is still lazy loaded because you did not ask for it to be eagerly or explicitly loaded.  In order to eagerly load your OrderLines, you will need to use the Include method; e.g. -
Dim recOrderHeaders As IList(Of ShopifyData.OrderHeader)

recOrderHeaders = dbContext.OrderHeaders
    .Where(Function(header) header.CustID = MyCustomer.CustID)
    .OrderByDescending(Function(header) header.OrderNo)
    .Include(Function(header) header.Orderlines)
    .ToList()

' Then I iterate through all the orders and find the order lines
For Each item In recOrderHeaders
    For Each oLine In recOrderHeaders.Orderlines
    Next
Next

Open in new window

You can read more about lazy, eager and explicit loading here: Entity Framework Loading Related Entities

-saige-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David DAuthor Commented:
Perfect :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.